I want to join ARInvoice to FSBillHistory. Sometimes I get two returns for one ARInvoice RefNbr, depending on if there are two appointments billed to the one invoice.
There must be a way to use relations to grab only one of the two (or possibly more than two) returns.
I have tried using First([X]) = [X] , Max([X]) = ([X]) (can’t because of aggregate). There must be a way to relate the join to provide only one return per match. Can anyone help??
-David
+4
Hi
Have you tried using Grouping? you can group by ARInvoice.RefNbr
+4
Hi
Otherwise what you’ll need to do is join a second time the FSBillHistory table (giving it a new alias) and probably do a join to the original FSBillHistory table where the CreatedDateTime is < or > than the other CreatedDateTime.
Here is a recent post where we accomplished something similar but relating to Sales Orders and Shipments with the need being to show the first shipment on the order:
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
