Skip to main content
Answer

Display in a GI Transactions Made On Overtime

  • March 28, 2025
  • 3 replies
  • 57 views
B
Semi-Pro II
Forum|alt.badge.img+1

I would like to be able to keep track of quantities that were produced during Overtime Hours.

My initial thought was to have a GI for move transaction history with a checkbox that would check if it was made during these overtime hours. This has proven to be quite the task and I just can’t get the correct transactions checked. 

 

I then thought I could create a Work Calendar and pull it in as a Data Source to compare with the times that the transactions occurred at. For this I had to create a GI that pulled the time information from the work calendar which made it display as a formula when it was used as a data source. However, I get an error when viewing the inquiry that these items “can’t be bound”  (whatever that means). 

 

My new thought would be to create a business event that would trigger when a move transaction happened during Overtime Hours and it would automatically update an Attribute within the produced item displaying OT.  This way I could filter by the attribute.  However this would also require the system recognizing time enough to create conditions in the GI that this business event would pull from.

 

Overtime Hours:

Mon-Thurs 1:30 AM - 4:00 AM

Friday 1:30 AM-11:59 PM 

Sat-Sun 12:00 AM - 11:59 PM

 

Here is what I had that would hopefully check the box if it was on “this specific day” and between these two calendar times. This gets the unbound error.

 

=IIf(((DayOfWeek([AMBatch.CreatedDateTime])=1) AND ([AMBatch.CreatedDateTime]>= [OTCalendar.CSCalendar_Formula56fd3ca1b78246d6abefd6d1b84c8c1a] AND [AMBatch.CreatedDateTime]<= [OTCalendar.CSCalendar_Formulac58c8a1ef3914d7a9aa068240afbf499])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=2) AND ([AMBatch.CreatedDateTime]>= [OTCalendar.CSCalendar_Formula641bd672826d418ebeaed20ad7a230e9] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formula92019c63a8aa49c4aa8c049644c065ec])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=3) AND ([AMBatch.CreatedDateTime]>=[OTCalendar.CSCalendar_Formula69b7df004ca6463fa7216994c739cf83] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formulaaa320e5236374f83b2409380e52fafb2])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=4) AND ([AMBatch.CreatedDateTime]>=[OTCalendar.CSCalendar_Formula173c76126aea4b60945d469329e87e67] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formula07ab10862c26463799d9ff45fd939933])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=5) AND ([AMBatch.CreatedDateTime]>=[OTCalendar.CSCalendar_Formulac9c7c290a0914e01b4c01b3ee5ee569a] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formula2252b4429b614a0ca344ec1e3a09a40e])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=6) AND ([AMBatch.CreatedDateTime]>=[OTCalendar.CSCalendar_Formula116899d0dd8c495faf42fab38df01f3a] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formulaf57d0b3ba50f46dc8d6fab1c0e285a55])) OR ((DayOfWeek([AMBatch.CreatedDateTime])=7) AND ([AMBatch.CreatedDateTime]>=[OTCalendar.CSCalendar_Formula5efd7ea7c96d4a68b75c83c74fe729bd] AND [AMBatch.CreatedDateTime]<=[OTCalendar.CSCalendar_Formula91ade4b6fb984e179245f16ee5fc0169])), TRUE, FALSE )

 

 

 

Since this cannot be bound, and I cannot see if it works, I substituted each calendar time with an actual time instead.

 

=IIf(((DayOfWeek([AMBatch.CreatedDateTime])=1) AND ([AMBatch.CreatedDateTime]>= '12:00:00 AM' AND [AMBatch.CreatedDateTime]<= '11:59:59 PM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=2) AND ([AMBatch.CreatedDateTime]>= '1:30:00 AM' AND [AMBatch.CreatedDateTime]<= '4:00:00 AM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=3) AND ([AMBatch.CreatedDateTime]>= '1:30:00 AM' AND [AMBatch.CreatedDateTime]<= '4:00:00 AM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=4) AND ([AMBatch.CreatedDateTime]>= '1:30:00 AM' AND [AMBatch.CreatedDateTime]<= '4:00:00 AM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=5) AND ([AMBatch.CreatedDateTime]>='1:30:00 AM' AND [AMBatch.CreatedDateTime]<= '4:00:00 AM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=6) AND ([AMBatch.CreatedDateTime]>='1:30:00 AM' AND [AMBatch.CreatedDateTime]<='11:59:59 PM')) OR ((DayOfWeek([AMBatch.CreatedDateTime])=7) AND ([AMBatch.CreatedDateTime]>='12:00:00 AM' AND [AMBatch.CreatedDateTime]<='11:59:59 PM')), TRUE, FALSE )

 

However the system does not recognize these times at all, and will not show any transactions that occurred within this expression.

 

 

 

I’m really grasping at straws here, I feel like I should be close, I just wish there was an easier way to display time so that I can add filter conditions for it.

 

Does anyone have any thoughts or Ideas on this matter? Has anyone needed to differentiate between straight time production and overtime production? I would be surprised if this is the first time this has come up.

 

Thanks to all who have helped me get this far, especially ​@bwhite49  

Best answer by bodiec

Thank you very much ​@lauraj46  I believe this did work. Thanks for the idea on using a shift field, however we do not have labor transactions set up at this time. would I be able to set up shifts without people actually clocking in and out on acumatica?

 

This ended up being the final formula that worked for me.

=IIf((DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=2 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=3 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=4 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=5 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=6 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=7 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719),TRUE, FALSE)

 

 

I am very happy that this worked out. Thank you all.

B

3 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • March 28, 2025

Hi ​@bodiec ,

The [CreatedDateTime] field contains the date and the time.  To look at the time only, you can use the functions Hour and Minute.  Be aware that date/time fields are stored internally in UTC time, so you might need to do an adjustment for that first.

For example to subtract 5 hours from UTC time to convert to Eastern:

DateAdd([AMBatch.CreatedDateTime],’h’,-5)

This formula subtracts 5 hours and then looks for a transaction on Monday between 1:30am and 4:00am:

=IIf(DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-5))=2 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-5))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-5)))>=90 and 
Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-5))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-5)))<=240, TRUE, FALSE)
 

If your moves are being captured by means of a quantity on a Labor transaction, have you considered making use of the Shift field?  It’s available to enter on the labor and clock entry screens.

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*.
Chris Hackett
B
Semi-Pro II
Forum|alt.badge.img+1
  • bodiec
  • Author
  • Semi-Pro II
  • Answer
  • March 28, 2025

Thank you very much ​@lauraj46  I believe this did work. Thanks for the idea on using a shift field, however we do not have labor transactions set up at this time. would I be able to set up shifts without people actually clocking in and out on acumatica?

 

This ended up being the final formula that worked for me.

=IIf((DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=2 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=3 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=4 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=5 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=240) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=6 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=90 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719) OR (DayOfWeek(DateAdd([AMBatch.CreatedDateTime],’h’,-6))=7 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))>=1 and Hour(DateAdd([AMBatch.CreatedDateTime],’h’,-6))*60 + Minute(DateAdd([AMBatch.CreatedDateTime],’h’,-6))<=719),TRUE, FALSE)

 

 

I am very happy that this worked out. Thank you all.

lauraj46
Chris Hackett
lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • March 28, 2025

Hi ​@bodiec ,

Glad that the formula worked out.  The shift field is available on the Labor transactions screen, with or without clock entry.

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*.
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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