Time Interval / Duration BAQ E9.05

Epicor ERP 9
,
1

I’ve created a BAQ / Dashboard to show items being placed in the QA Rack . Parts clocked into an Operation created before any QA related OPR . These are active transaction to be shown and refreshed every 5 minutes . I am trying to show the duration on the parts sitting on the Rack . Epicor support suggested to use DateDiff , but that function is not applicable in E9 . I’ve tried the below with no success . If anyone could point me in the right direction would be much appreciated

  1. String(Time, “HH:MM:SS”) - LaborDtl.DspClockInTime
  2. Add-interval(String(Time, “HH:MM:SS”) , 20, ‘minutes’) - [Calculated field]
  • String(Time, “HH:MM:SS”) = DataType Character
  • LaborDtl.DspClockInTime = DataType Character

The whole idea here is to show the Time Duration of the parts sitting on the rack in minutes . System current time - Clocked in Time

Create the Row Rule to show example: Parts been sitting on the rack for 20 minutes = Warning . Parts been sitting on the rack for 30 minutes = Error

Thanks for any help . Appreciate it.

UPDATE:
I’ve tried creating 3 calculated formula as below and analyse systax OK but when test i gotten error

image

image

image
Analyse Syntax OK
image
Final Result
image

Having sore eyes with this simple function . Any thoughts ?

2

Hi Suresh,

I looked at my database and did a very basic calc on SysTime (PartTran.SysTime - 1) in a query and it worked. I am seeing time fields in Epicor stored as integer. My calculated field worked setup either as integer or decimal.
I was curious how the time data stored as int was actually a time on the clock. Looking into seconds calculation, per below, that looks to be what it is.
IDK if this helps at all, but hopefully it steers you in the right direction. We are on E10.

image
image.png969×870 185 KB

image
image.png631×974 33 KB

Nancy

3

@Nancy_Hoyt you are correct. Epicor will store time as seconds a lot. I think they do this because they will use these columns sometimes as foreign key fields with transaction level tracking. We use the following SQL statement to convert it to an actual time and store in it a nvarchar field in data warehousing.

CONVERT(TIME(0), DATEADD(SECOND, B1.CreateTime, 0))

That is definitely a fun one to figure out when you see a column labeled time with a big integer.

2 Likes
4

Here is a collection of E9 BAQ code that I collected from Epicor KBs.

BAQ Code Library.p (15.6 KB)

2 Likes
5

Thank you Nancy for taking time and effort to do some case study . As you can see at the below screenshot . I can actually get the System Time in HH:MM and also the Clock in Time from MES in HH:MM . I would like to know in E9 , the interval calculation between both time in minutes . Epicor support is not being very helpful at this point of time

image
image.png1043×181 12.2 KB

@gpayne
Thank you for the list but i am not sure why doesnt INTERVAL works with E9 , we are running on Progress OpenEdge 10.2B

image
image.png709×188 3.08 KB

6

I looked and I had never used interval in any routines. This was much simpler than you would think. The calc below gives you a decimal result, but you only care about checking over X, so it will work.

(Time / 3600) - LaborDtl.ClockinTime
1 Like
7

@Nancy_Hoyt
Thank you

@gpayne
Managed to get it done . Thank you for the right direction

image
image.png917×189 9 KB

1 Like
8

@Mi3kel @gpayne @Nancy_Hoyt,
i do not know if this will help to simplify what you want or not, and i have noticed that you are not taking into consideration the date change, and i think this is important when working on the LaborDtl table, so i created this SQL equation to calculate the difference in minutes time for each transaction between ClockInDateTime and CurrentDateTime, this one equation will take into consideration the different in days as well as time, and can be altered to any other time unit if you want and to any other date you want i.e. replace GETDATE() by any other date

DATEDIFF(day, LaborDtl.ClockInDate, GETDATE())*24*60+ DATEDIFF(minute, LaborDtl.DspClockInTime, FORMAT(getdate(), 'HH:mm:00'))

HTH

2 Likes