Creating a GI or report parameter to filter a datetime field by date AND time
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?
Page 1 / 1
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:
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!
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?
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
Hi @lauraj46
How can I set the specific No. of Days in the condition of Generic Inquiry?
Thank you
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
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.
@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
Hi Ms. @lauraj46 ,
Thank you so much for the help,
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.
FYI this thread was very helpful for me today !!!
Hi! I’m trying to calculate a month start and month end date field in the results grid a GI. I have the =DateAdd(Today(),’d’,-1) formula working but, I’d like to use a formula that works like the @MonthStart and @MonthEnd function does so it handles months that are 30 days and 31 days long. Does anyone happen to know of one?
Hi @Bree612 ,
To find the last day of the previous month, you can use the DatePart function to find the day of the month and then subtract that number of days:
=DateAdd(Today(), 'd', -1*Day(Today()))
To find the last of the current month you can use this formula which calculates the first day of the current month, adds one month to get the first day of the next month, then subtracts 1 day to get the last day of the current month.
