Skip to main content
Solved

Display in a GI Transactions Made On Overtime

B
Freshman II
Forum|alt.badge.img

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.

View original
Did this topic help you find an answer to your question?

3 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 591 replies
  • 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
1 person likes this
  • Chris Hackett
    Chris Hackett
B
Freshman II
Forum|alt.badge.img
  • bodiec
  • Author
  • Freshman II
  • 20 replies
  • 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
2 people like this
  • lauraj46
    lauraj46
  • Chris Hackett
    Chris Hackett
lauraj46
Captain II
Forum|alt.badge.img+8
  • lauraj46
  • Captain II
  • 591 replies
  • 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
1 person likes this
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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