Creating BAQ with total AP Invoice per Sales Order

Kinetic ERP
1

I was trying to create a BAQ and link talbles. i have APInvHed, APInvDtl, OrderHed, OrderDtl but there was OrderNum to link orderhed to apinvdtl.

2

Buy To Order is what your looking for? Then you’ll need to use the PORel to get the link from Order-2-PO then you can go PO-2-APInv

1 Like
3

image
image1507×1080 67.8 KB

Seems I can’t see an OrderNum and OrderLine in APIInvDtl

4

It will be in the OrderRel table, the PO/LN/Rel will be available there, you can then link directly to APInvDtl.

1 Like
5

You can’t you need to use the OrderRel table to link to the PORel table to APInvDtl

6

Thanks Randy and Jennifer Will try that

7

seems there is no link to PORel to OrderRel

image
image1203×989 35.1 KB

8

image
image494×444 6.05 KB

Why I can’t find the OrderDtl.TotalShipped in OrderDtl table?

9

You can manually make the link between the two tables yourself (not all tables autolink together in BAQ editor).

1 Like
10

Because it’s an external field, those fields do not exist in the database and need to be manually created. For example, on this particular one, you’d want to make a subquery summing the shipped qtys on the shipdtl table for this order.

1 Like
11

Thank you all for the help. I’ve figured it out. much appreciated