Creating a GI or report parameter to filter a datetime field by date AND time

  • 18 November 2020
  • 8 replies
  • 382 views

Userlevel 1
Badge

Recently we had the requirement to create a Generic Inquiry and Report with parameters to filter on a date range using both date AND time to account for multiple labor shifts. 

I could not identify a schema field that included a time component, so I created two additional string parameters for the time components.  I then parsed the time parameter and added the corresponding number of minutes to adjust the start and end dates in the GI/Report condition.  I also had to make an adjustment for UTC, which works differently between the GI and the Report.  

GI search screen:

Condition on the GI:

It seems a bit hacky, but it does do the trick.  Has anyone else encountered this requirement and possibly found an easier method to provide the functionality?

 


8 replies

I am struggling with the created on date field myself, can you send me your whole condition as the right side is cut out, i believe it is exactly what I need to do!

Userlevel 1
Badge

@Mike Baio 

There may be a better way, but this approach worked for our situation:

=DateAdd([BegDate] , 'n' , IIf([BegTime]=null, 0, IIf(len([BegTime])=5, (CInt(left([BegTime], 2))+5)*60 + CInt(Right([BegTime], 2)), IIf(len([BegTime])=4, (CInt(left([BegTime], 1))+5)*60 + CInt(Right([BegTime], 2)), 0))))

Thank you, here is what I’m really trying to do, basically generate a report that shows everything that was created in a certain area for a day in time.  So for example, in Payables, I would like to see a report off the register file that shows all the transactions that have a createDateTime date of a particular parameter.  I think it has to do with this date time thing why its not working.  

Userlevel 1
Badge

@Mike Baio

I created the extra “time” parameters because the schema for a date/time field does not allow you to specify a time.  With four parameters (start date, start time, end date, end time) the user can specify both a start and an end time.  The formula converts the time into seconds and adds that number of seconds to the date parameters to create the formula for the condition.  

This is how I configured the parameters:

 

i believe i got it to work, thank you, your formula let me see this from another perspective!

Hi @lauraj46 , I’m trying to use a condition off of the PMProject.CreatedDateTime field but I don’t need the time as part of the condition, just the date. I just need to filter the list of projects for ones created on or after 1/1/2021. I’ve tried the below a few different ways but I continue to get an “ Incorrect syntax near '>' ” error. Do you have any suggestions?
 

 

Userlevel 1
Badge

 

 

Hi @cshaheen26 , 

Your formula on the right hand side of the condition should not include the > sign, instead specify the ‘Greater Than or Equal To’ option only in the condition.

You can also use the date relative parameters that Acumatica provides such as @YearStart.  This will allow your GI to continue working next year without having to go back and change the date.  

In order to implement this, I would typically create a parameter on the Parameters tab, and then reference it in the condition, see screenshots below:

 

Hope this helps!

Laura

It did! Thank you so much, @lauraj46 

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 — 2020  Acumatica, Inc. All rights reserved