Method Community

Hi All,

I am new to Method and trying to use the report designer to create a customer list that shows the customers last invoice date and amount. I can create a customer list and display their invoice history , but I am trying to determine a way to just show the last invoice. I can't do this by date filters because that wouldn't guarantee that I am seeing the last transaction - some of our customers order every other week, others just once or twice a year.

We want to use this report to determine when to follow up with existing customers for reorders. So once I have a working report, we could then filter it to show all the customers whose last order was 3 months ago, etc.

Any help or wacky ideas are much appreciated.

Hello MindyMcCasland and welcome to the Method Forums,

I believe there would be a couple of ways to do this, I will do my best to give you some of these possibilities.  Would I be correct to assume that you are technically looking to filter out the last invoice created for each Customer?  If this is the case, I can think of two fields from the Invoice table you could use as a filter, TxnDate and RecordID.  TxnDate, is the 'Date' field on the Invoice screen, this field will automatically take the date the Invoice was created/saved on, however you do have the ability to change this date manually.  The RecordID field exists in the Invoice table (as well as virtually all tables), each time you create a new record (like a new invoice), when you save it you get a RecordID.  This RecordID is assigned in sequential order, so if you look for the highest RecordID value for each Customer, you would get the last one created.  If you aren't familiar with the RecordID field, you can do a quick Import of your Invoice table and see what I mean.  Go to Customize->IntegrationTools, then choose the Import/Export Link at the top of this screen.  Follow the 5 steps on the next screen, making sure you choose to "export table", select the Invoice table, then export it as a CSV file, which can be opened in Excel (make sure to include the "RecordID" at the least in your export).  For more info on Importing/Exporting, check out this link.

You could always use the RefNumber (ie Invoice #), however this would depend again if you have it set to increase sequentially, this might not work if you ever use unique RefNumbers or manually import some with a # out of the original pattern.

Let me know if this helps, or post if you have any follow-up questions.

-Ben

Hi aother suggestion is to use the Report Designer filter which is at the report or group levels. Look at the Properties tab on the right hand side. 

You would want to use at the invoice group level. You can use a Max(txnDate) function. It seems like that should work.  Ben's suggestion using Invoice recordID would also get it.