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

  • 18 November 2020
  • 14 replies
  • 2997 views
lauraj46
Rank
Userlevel 7
Badge +5

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?

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

14 replies

M
Rank

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!

lauraj46
Rank
Userlevel 7
Badge +5

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
M
Rank

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.  

lauraj46
Rank
Userlevel 7
Badge +5

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

 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
M
Rank

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

C
Rank
Userlevel 2
Badge +1

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?
 

 

lauraj46
Rank
Userlevel 7
Badge +5

 

 

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
C
Rank
Userlevel 2
Badge +1

It did! Thank you so much, @lauraj46 

J
Rank

Hi @lauraj46 

How can I set the specific No. of Days in the condition of Generic Inquiry?

Thank you

lauraj46
Rank
Userlevel 7
Badge +5

Hi @jamesaldo ,

You can use the DateDiff and  DateAdd functions in the condition to calculate or adjust the difference in days.  Can you explain a little bit more about what you are trying to accomplish? 

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
J
Rank

Hi @lauraj46 ,

I’m struggling to set the specific no. of days in the condition, I always get the incorrect results.

Sample: Days are only 13 days starting from the date created.
 

 

lauraj46
Rank
Userlevel 7
Badge +5

@jamesaldo 

If I’m understanding correctly, you would like to calculate the number of days since the record was created?

In the Results Grid you can use a formula such as this to calculate the difference in days:

 

This condition will return Cases that were created in the last 365 days:

 

Another technique I sometimes find useful is to create a parameter for use in the condition.  You can allow users to change the default, or make it not visible if you don’t want that option. 

In the Default Value you can use Date-Relative values such as @Today, @WeekStart, @MonthStart, @QuarterStart, @YearStart.  There is also the ability to add or subtract a number of days such as 365 in the example below. 

Once you have defined the parameter, you can use that in your condition like so:

I hope that helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
J
Rank

Hi Ms. @lauraj46 ,

Thank you so much for the help, :grin::blush::blush:

My Generic Inquiry is working now and today I can show the records with any required days. 

I used this formula that you’ve provided.
 

 

dsimmerly
Rank
Userlevel 4
Badge

FYI this thread was very helpful for me today !!!

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