Solved

Is there a way to get current weekID in a Generic Inquiry?

  • 5 December 2022
  • 8 replies
  • 404 views
C
Rank
Userlevel 4
Badge +1

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

icon

Best answer by Naveen Boga 5 December 2022, 12:52

View original

8 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

@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 B
C
Rank
Userlevel 4
Badge +1

@Naveen Boga  Thank you. I will try it.

C
Rank
Userlevel 3
Badge

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.

Excel VBA

=Year(DateAdd("d", -6 - Weekday(Date, 2), Date)) * 100 + Application.WorksheetFunction.Floor(DateDiff("d", CDate("1/1/" & Year(DateAdd("d", -6 - Weekday(Date, 2), Date))), DateAdd("d", -6 - Weekday(Date, 2), Date)) / 7) + 1

Acumatica

=Year(DateAdd(Today(),'d', -6 - DayOfWeek(Today()))) * 100 + Floor(DayOfYear(DateAdd(Today(),'d', -6 - DayOfWeek(Today()))) / 7) + 1

 

greglang
Rank
Userlevel 3
Badge

This is how I’ve done it in GI’s for This or Last weeks Timecards. 

 

 

=IIf([AllWeeks] = 'Last' , [LastWeekStart], iif([AllWeeks] = 'Current' ,[ThisWeekStart],'' ))

 

 

Greg Lang
C
Rank
Userlevel 4
Badge +1

@greglang Great!. Thanks for sharing

C
Rank
Userlevel 3
Badge

Here is a better way to calculate WeekID in Acumatica. The end of the year gives the wrong number using the first method.

=DateAdd(Year(DateAdd(Today(),'d',-6-DayOfWeek(Today()))) * 100 ) +
Floor(
  DateDiff('d',
    Round(
      DayOfWeek(CDate('1/1/'+DayOfYear(DateAdd(Today(),'d',-6-DayOfWeek(Today())))))/7,0
    ),
    DateAdd(Today(),'d',-6-DayOfWeek(Today()))
  ) / 7
)
+ 1

 

C
Rank
Userlevel 3
Badge

Corrected. Your mileage may vary.

=Year(DateAdd(Today(),'d',-6-DayOfWeek(Today()))) * 100 +
Floor( DateDiff('d',
  DateAdd(CDate('1/1/'+Year(DateAdd(Today(),'d',-6-DayOfWeek(Today())))),'d',7 *
    Round(DayOfWeek(CDate('1/1/'+Year(DateAdd(Today(),'d',-6-DayOfWeek(Today())))))/7,0)
  )
,
  DateAdd(Today(),'d',-6-DayOfWeek(Today()))
) / 7 )
+ 1

 

C
Rank
Userlevel 4
Badge +1

Hi @cmetzger. Thanks for sharing!

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