Generic Inquiry to pull most recent record

Hey there!  I know you said you can’t do any grouping, but I pulled the Ref Nbr., Customer ID, and Last Modification Date/Time fields from AR Tran into a GI and grouped on Customer ID.  I then created a test Invoice and it looks like it does pull the most recent record.  AR Tran is going to pull all detail lines in, but they won’t be able to edit them after releasing any way.  Let me know if this gets you closer.  What other considerations are there when displaying the data in the GI?

Hey there!  I know you said you can’t do any grouping, but I pulled the Ref Nbr., Customer ID, and Last Modification Date/Time fields from AR Tran into a GI and grouped on Customer ID.  I then created a test Invoice and it looks like it does pull the most recent record.  AR Tran is going to pull all detail lines in, but they won’t be able to edit them after releasing any way.  Let me know if this gets you closer.  What other considerations are there when displaying the data in the GI?

 

I have found it isn’t consistent; the issue is they want the dollar value of the line and a formula they have associated with that line. That is why grouping isn’t an ideal solution. It gets them “most” of the way there but there as it is fixed data, but there are a couple fields that need to be accurate as decisions are made from that.

 Hi ​@shooper10 ,

 

I’m struggling to find the post from I think ​@Gabriel Michaud, but he had a unique solution to a similar scenario.  In your case, pull in a 2nd copy of the ARInvoice table, and name it something like ARInvoiceLAST.  On your Joins, do the following:

 

 And on your Conditions, do this:
 

What you’re basically saying is “find me the invoice for each Customer that is not less than anything,” which is kind of a backwards way of saying the “last” of something.

From there, you can Join in your ARTran table to ARInvoice, and build out as normal.

Hope that helps!

Oh this may have some promise, I will try this out. In my testing, I was getting close to this didn’t come at it in this particular way.

How to get the last sales price and qty using GI?

I’ve used it several times 🙂

There we go, bookmarked now!  Thanks ​@darylbowman !

 Hi ​@shooper10 ,

 

I’m struggling to find the post from I think ​@Gabriel Michaud, but he had a unique solution to a similar scenario.  In your case, pull in a 2nd copy of the ARInvoice table, and name it something like ARInvoiceLAST.  On your Joins, do the following:

 

 And on your Conditions, do this:
 

What you’re basically saying is “find me the invoice for each Customer that is not less than anything,” which is kind of a backwards way of saying the “last” of something.

From there, you can Join in your ARTran table to ARInvoice, and build out as normal.

Hope that helps!

Hello!

I’m following this post to attempt to get the Last “ReleasedDateTime”. This has worked to narrow down my list AMAZINGLY. However, it still is showing more than a “single line item” Ideally we’d like to see “hey this is the last date they hit this warehouse” not “here’s every record of them hitting warehouses since we started”

Hey ​@kdolgner ,

I think your solution here is actually simpler than the solution proposed in this post.

I would Inner Join your POReceipt and POReceiptLine tables.  Then, Group By your Warehouse (SiteID I think?) and InventoryID.  In your Results, add your Warehouse, Inventory Item, and Release Date.  If you look to the right in the Results, you should see a column called “Aggregate Function,” set that to MAX.  

That should then return the highest available Receipt Date for each particular Item within each Warehouse.

Hope that helps!