C
chainman
Business works has linked two tables in the order entry module. The first
table is the invoice header. The second is the detail line item. Ideally,
they would be connected in a one-to-many relationship. However, the
following represents a simplified version of what the tables look like.
Invoice header:
custid. invoiceno itemlink lines status
A 1 200 1 O
B 2 201 3 O
A 3 204 2 C
detail line item:
lineid nextrecord productid quantity
200 0 x 2
201 202 x 3
202 203 Y 1
203 0 Z 9
204 205 x 5
205 0 Y 2
I have changed the actual field name for simplification. In the
illustration the status O is used for Open and C is used for closed. If a
join is made between inoviceheader.itemlink and lineid I can query the first
line item of each invoice but not the subsequent line items. I am guessing
that I need to modify the sql query with an if ...then statement but I must
admit that I do not have the experience to make it work.
Any help will be appreciated. Thanks.
table is the invoice header. The second is the detail line item. Ideally,
they would be connected in a one-to-many relationship. However, the
following represents a simplified version of what the tables look like.
Invoice header:
custid. invoiceno itemlink lines status
A 1 200 1 O
B 2 201 3 O
A 3 204 2 C
detail line item:
lineid nextrecord productid quantity
200 0 x 2
201 202 x 3
202 203 Y 1
203 0 Z 9
204 205 x 5
205 0 Y 2
I have changed the actual field name for simplification. In the
illustration the status O is used for Open and C is used for closed. If a
join is made between inoviceheader.itemlink and lineid I can query the first
line item of each invoice but not the subsequent line items. I am guessing
that I need to modify the sql query with an if ...then statement but I must
admit that I do not have the experience to make it work.
Any help will be appreciated. Thanks.