Solved

Customer first order number and date

  • 26 October 2022
  • 9 replies
  • 232 views

Userlevel 5
Badge +1

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!

 

Relations

 

Results Grid

 

icon

Best answer by lauraj46 26 October 2022, 22:11

View original

9 replies

Userlevel 2
Badge

Thank you.  I tried to link them correctly, but it doesn’t give a result.

 

Userlevel 5
Badge +1

@MComan62 - yes, the ARTran table only includes data for sales orders that have shipped and been invoiced so you’ll need to substitute the SOOrder table (twice) instead of the ARTran table(s). That will get you all order data (including orders in Awaiting Payment status).

Userlevel 2
Badge

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.

Userlevel 5
Badge +1

Thank you, @lauraj46! That worked great!

Userlevel 7
Badge +7

Hi @mikeho ,

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.

Userlevel 5
Badge +1

Thank you, @lauraj46! It seems to be working correctly for me based on your suggestions.

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?

Userlevel 7
Badge +7

Hi @mikeho ,

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!

 

Userlevel 5
Badge +1

Thank you, @lauraj46! I think I understand based on your example. Would grouping by BAccount.AcctCD fix the multiple lines issue? 

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:

  • Customers who placed their first and only order within that date range
  • Customers who have placed multiple orders and one of those orders is within that date range

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?

Userlevel 7
Badge +7

Hi @mikeho ,

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!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved