Question

Filter by last invoice date

  • 15 November 2023
  • 6 replies
  • 144 views
M
Rank

Hi 

Apologies if already answers here but haven't been able to find 

attempting to create filters etc in our Customer GI for Lapsed customers. For example we already have several tabs with filters for sales person etc but wanting a quick look ability for the sales team on the road with a tab that only includes for instance customers with no invoices / sales orders in last 90 days. 

I have a report that does this but hoping to achieve in the GI so it is quick and easy to see. I have a semi reasonable understanding of how to customise a GI but not sure what tables / fields etc I might need to add. 

Thanks   

 

6 replies

Laura02
Rank
Badge +18

Hello,

I think you could create a GI for customers with no invoices in a given time period:

  • Use tables Customer left joined to ARInvoice
  • Add parameters for begin/end invoice date. 
  • Add Condition for AR documents types to include/exclude (do you want to see Credit Memos?  Write-Off? Refund?)
  • Sort/Group by Customer
  • Display highest (max) Invoice date; using Aggregate column on Results tab.
  • Then filter/condition on whether Max Invoice Date Is Empty.

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
M
Rank

Thanks heaps @Laura02 

 

Couldn't quite do it that way but managed the dates issue with a saved filter in the actual GI see below set to older than 90 days 

 

While I was setting this up thought it would be awesome to also add the last “Activity” set to activity type so can be easily seen when last “Sales Call note” was made for that customer. Is that an easy add with another table / relation 

Cheers

 

 

Laura02
Rank
Badge +18

Hi Matt,

I think you can join Customer to CRActivity to obtain the last Activity.

CRActivity table has several dates: Start, End, Completed, Created, Last Updated.

Use Max([date field]) as you did with invoice date above.

Display CrActivity.Type to see the type of the last activity.

To limit the activity date to Sales Call Notes, add Condition(s) to your GI to include only Sales Call Notes, or to list the types of activity that you’d like to include/exclude.

 

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
M
Rank

Hey @Laura02 thanks again. Have been battling away at this and worked out a few things but 2 issues I cant get past 

Worked out that we couldn't relate CRCustomer to Activity so have done through CRBaccount relating to Activity instead 

All data now seems to be present that i need but 2 problems 

  1. GI is super super slow and can see it causing issues with mobile sales team, any tips on cutting down the generation time? 
  2. Cannot get the activity date column to only show the last date a “sales call note” was taken
    1. have added conditions in the GI and tried all variations of And/or basically if i select the condition just “sales call notes” the GI will drop off all customers that don’t have a note in that period (slack rep :)) 
    2. also tried to add condition of “Is empty” no matter what I try, if the account is active it will put the date in the activity date column of ANY activity that has happened
    3. Ideally if there no “sales call note” type at all then that column would be blank   
M
Rank

Think I can resolve the delay issues if I can set the table data for activity to only import the “type” that i want. I Can’t seem to resolve this however despite what variation of “conditions” I try. If I select the “type” I want then it only shows the customers that have one of those “types” attached. Can I get the tables to import independently, ie 

  • All Customers (regardless of activities) 
  • Only activities of the one “type” 

sure it is possible using the brackets etc but can’t seem to nail it 

thanks 

Chris Hackett
Rank
Userlevel 7
Badge

Hi @MattH were you able to find a solution? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.

Reply


Terms & ConditionsCookie settings
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