Is there a way to get current weekID in a Generic Inquiry?
Hi All,
I want to add a condition to a generic inquiry to filter timecards created on last week. To do that I think that I would need to compare EPTimeCard.WeekID with current WeekId. Is there a way to populate Value field of GI with current WeekID ?
Thanks
Page 1 / 1
@charithalakshan49 I don’t think we can get the WeekID value in the Generic Inquiry but can get the StartWeek OR EndWeek like below.
@Naveen Boga Thank you. I will try it.
This formula will generate a WeekID. It uses DateAdd() and DayOfWeek() to get last week’s Monday, then uses that Monday to calculate the week number.
The formula cannot be used as a default value in a GI parameter, however it CAN be used as a GI condition or the default value in a report parameter.
Brandon, for some reason I can’t send Screenshots in a DM so here is what I was trying to send to you. I think the part your missing is a table with week Start. I use the CustomWeek table. EPCustomWeek and link it on WeekID
I created 2 hidden Parameters and one that is a combo box for the 3 choices I'm giving them (This week, Last week or All Timecards)
Then on the Conditions tab I tell it match the EPCustomWeek.StartDate to
the Parameter for @WeekStart if they choose the combo value of Current (This Weeks Timecards). or @WeekStart-1 if they choose the combo value Last (LastWeekStart) or if they choose All (All Timecards) it will not filter the weeks at all.
Hope that helps,
-Greg
@cmetzger I get error ‘Conversion failed when converting the varchar value '1/1/' to data type int.’ when I used your corrected formula in a GI results grid data field. Do you experience something different?
@john55 The GI results grid needs Cstr() around the Year() to explicitly be string + string instead of string + number.
But before you try that… I was having a hard time maintaining and adapting the formula.
I recently revisited the logic behind Excel’s ISOWEEKNUM(date), and refactored my Excel VBA to resemble Acumatica’s built-in formulas:
Public Function DayOfYear(ByVal theDate As Date) As Long DayOfYear = DateDiff("d", "1/1/" & Year(theDate), theDate) + 1 End Function
Public Function DayOfWeek(ByVal theDate As Date) As Long DayOfWeek = Weekday(theDate, 2) 'Mon is 1, Sun is 7 End Function
Public Function ThuOfWeek(ByVal theDate As Date) As Date ThuOfWeek= 4 - Weekday(theDate, 2) + theDate End Function
Public Function ISOWEEKNUM_UDF(ByVal theDate As Date) As Long ISOWEEKNUM_UDF = Year(ThuOfWeek(theDate)) * 100 + Int((DayOfYear(ThuOfWeek(theDate)) + 6) / 7) End Function
The Acumatica GI Results Grid tab allowed number + date:
If you want the previous week, use 4 - 7 instead of just 4.
I haven’t tested the formula in Acumatica Report Designer, but I suspect that reports and GI tied to scheduled email reports will both require the “proper” date addition with DateAdd().
My test cases were new year’s between 2008-2009 and 2023-2024 versus Excel ISOWEEKNUM.