I’m trying to create a GI that will return a list of customer account numbers along with the date of the first order completed and that corresponding order number. I’ve gotten close, but the GI is returning some incorrect results.
Here’s what I have so far - any suggestions for how to get the information I’m looking for is greatly appreciated!
Best answer by lauraj46
View original
+7
Hi
You are close, but your approach will select the minimum SOOrderNbr by customer independently of whether or not it is the first transaction date.
A trick to select the earliest record along with all of the corresponding details is to join the detail table twice in the GI. The idea is to find the first transaction record by looking for a record where the second left join returns nothing (i.e. there are no earlier transactions).
A bit hard to put into words, but I have attached an example GI. Since the comparison is just by TranDate, you might find that the GI returns multiple lines for customers with multiple sales orders or multiple lines on a single sales order.
Hope this helps!
Thank you,
I also need to be able to set a date range to show all of the “first time orders” within that range. I’ve used your example and set up these parameters and conditions:
I set the above and grouped by BAccount.AcctCD, however I’m finding that the results include both:
Is there something else I can do to allow the date range to be set and to truly return ONLY the customers who have placed their first order within that range?
+7
Hi
Yes, there should be no problem with grouping and selecting the min or max sales order by AcctCD.
In your screenshot I don’t see the criteria INTran.TranDate is Empty. This is the bit that makes sure you are looking for the first transaction specifically.
I attached a revised XML with these changes.
One way to test would be to leave the date parameters blank and filter the date column using the column headers in the results table. I tested the attached and it seems to be working properly in my demo data.
Hope this helps!
Thank you,
One last question - we may want the GI to be based on the document date of the first sales order instead of the TranDate. Any suggestions on how I might go about tweaking this to return those results?
+7
Hi
In that case I would probably just use the SOOrder table (twice) instead of ARTran. Replace the ARTran.TranType filters with whatever filters you need to make sure that you are not including orders on hold, etc.
Thank you,
That’s a great way to do it. Is there a way to find the first Sales Order for a customer? I used your GI and it didn’t include SO’s awaiting payment. I am thinking its a whole separate report because it depends on SO.
Thank you. I tried to link them correctly, but it doesn’t give a result.
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
