I think i have a join issue but i can’t quite track it down.
Example 1: 154221 has two invoices (312230, 315411), but they are both duplicated with each others information.
example 2. 154204 is all blank, however, that information is there on the invoice tab in the sales order.
i have swapped the joins of each relation i have but cannot figure out why i’m getting dupes AND blanks. some relation swaps will drop the blanked out orders.
Best answer by lauraj46
Hi
You are getting crossed records when there is more than one invoice related to a sales order because the discount details are specific to the invoice. Instead of joining SOOrder with ARInvoiceDiscountDetail, you should join ARInvoice with ARInvoiceDiscountDetail on the docType and refNbr fields.
Hope this helps!
Laura
+8Hi
You are getting crossed records when there is more than one invoice related to a sales order because the discount details are specific to the invoice. Instead of joining SOOrder with ARInvoiceDiscountDetail, you should join ARInvoice with ARInvoiceDiscountDetail on the docType and refNbr fields.
Hope this helps!
Laura
Hi
You are getting crossed records when there is more than one invoice related to a sales order because the discount details are specific to the invoice. Instead of joining SOOrder with ARInvoiceDiscountDetail, you should join ARInvoice with ARInvoiceDiscountDetail on the docType and refNbr fields.
Hope this helps!
Laura
How did you know to relate these tables? When i was looking at the DAC schema i didn’t see ARInvoice joining to ARInvoiceDiscountDetail. I used what you have in your screen grab and it worked well.
As far as my blank info; it was because those invoices were manually entered. Since this was done, they are not associated to any sales orders….now those disapeared with the dupe fix. I’ll find them again!
+8Hi
You can use the DAC Schema browser to learn about the relationships between the different tables. Click on any table hyperlink in the Generic Inquiry editor and it will open. There you can see all of the outgoing references fro the ARInvoiceDiscountDetail table. I happen to know that ARInvoice is a subclass of ARRegister, but you can also find this information in the DAC Schema browser.
The join to SOOrder is also legitimate if you wanted to show all of the discounts that have been applied to the order. This would be appropriate if you weren’t also including ARInvoice in your query.
Hope this helps!
Laura
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
