By using this website, you agree to our Terms of Use (click here)
I am working on an AR Aging inquiry, that calls from the ARInvoice table. However, there is one payment from the ARPayment table that I need brought over in order for my inquiry to tie out. If I try to call the ARPayment table to get this one payment, every other payment comes along with it. How can I specify my tables' relationship so that I only bring over the one payment that is affecting my inquiry?
Part of your join should include the reference number if it doesn't change. Or the customer/location combo if they're unique for this inquiry.
The join would be something like:
Ref Nbr <--> EQUALS <--> 001234
DocType <--> EQUALS <--> Payment
If you have to isolate multiple values (say 3 invoices, or multiple customers) do it in the conditions tab and use parenthesis and the "OR" option to group them together. If you're comfortable sharing more details about the account you wish to isolate, we can get you a more precise answer.
The account's reference number is 22158. So when joining the tables, I should put Ref Nbr as the parent field and 22158 as the child field?
If the Parent is ARPayment then yes. You are asking the system to look at a VARIABLE (RefNbr) in the ARPayment table and then you're saying it must equal a VALUE (22158) that does not care about about the table. So whichever side has ARPayment gets =[RefNbr] and the other side gets ='22158'. Which translates in english to:
Only join when the RefNbr in ARPayment is EQUAL to 22158. This will result in a single row being pulled. If you get more than one row, your issue is that RefNbr is part of a Key Pair, the other half being the DocType. You may need to assign DocType if you get multiple rows.
This worked perfectly! Thank you so much!
