Calculate 1 line value using 2 or more other lines <p>I have the following query.</p> <p>How this works my client sells kits. Each kit can contains 3 to 5 lines. </p> <pre><code>LINTYP = 6 - Kit Item LINTYP = 7 - Components </code></pre> <p>The sequence should always be 6-7-7 where 6 represents the start of an new kit. There will always be 1 line that would contain an gross price. In the sample below there is 2 kits each containing 2 lines with an line status of 7</p> <p>So what needs to happen is I need to calculate the missing GROSPRI using LINTYP 6 and deducting all my LINTYP 7 lines</p> <p>eq. </p> <pre><code>Grosprice on line 1 (Product 550412) = R1 795 Grosprice on line 3 (Product 501301) = R 185 Grosprice on line 2 (Product 650412) should be R 1795-185 = R1 610 ---------------------------------------------------------------- | Invoice No| Product| Line No| Line Type| Gross Price ---------------------------------------------------------------- |SI141000008| 550412| 1000| 6| 1795.00 ---------------------------------------------------------------- |SI141000008| 650412| 2000| 7| 0.00 | This needs to be R1610 ---------------------------------------------------------------- |SI141000008| 501301| 3000| 7| 185.00 --------------------------------------------------------------- |SI141000008| 550413| 4000| 6| 1855.00 -------------------------------------------------------------- |SI141000008| 650413| 5000| 7| 0.00 | This needs to be R1670 --------------------------------------------------------------- |SI141000008| 501301| 6000| 7| 185.00 ---------------------------------------------------------------- </code></pre>