Solved

How to set the date parameter to filter by two dates so that when one date is not available filter by other date?

  • 8 June 2023
  • 3 replies
  • 191 views

Userlevel 4
Badge

There is a custom report created by me. It contains two columns as Invoiced Date and Document date. I want to filter the report by using these date fields. First priority goes to Invoiced date. If there is no Invoiced date filtration should be happened with the Document Date. And again If there is both Invoiced date and document dates filtration should be occurred by the invoiced date. Please check the below images.

Start Date, End Date parameters 

 

1st attempt by me
1st attempt result
2nd attempt method

I tried it in several ways in the Filters section of the report designer. But it did not work. In the first method, filtration occurred, but the priority was on the Document date although there was an invoiced date. In the 2nd method, an error message popped up as no Invoice date field found.

So can you please give me guidance to fulfill this requirement? Thank you. 

icon

Best answer by darylbowman 8 June 2023, 12:32

View original

3 replies

Badge +11

Try this, using the actual names and conventions in the report designer 

 

(InvoicedDate >= StartDate or

(InvoicedDate is null and

DocDate >= StartDate)) and

(InvoicedDate <= EndDate or

(InvoicedDate is null and

DocDate <= EndDate))

Userlevel 4
Badge

Thank you so much @darylbowman. It worked nicely. 

Badge +11

You're welcome!

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