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

  • 18 November 2020
  • 5 replies
  • 177 views

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?

 


5 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!

@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.  

@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!

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