Help With FILTER On Load IDO Collection

What is the error in my syntax here? It is saying there is incorrect syntax near 'ORDER'. Do I absolutely need an ORDERBY? I add it and it gives me an error saying incorrect syntax near 'jobt' yet there is no 'jobt' in the entire script.

IDO("SLJobTrans")
PROPERTIES("Job, Suffix, OperNum, EmpNum, TransNum, RecordDate, Wc, StartTime, EndTime, QtyComplete, QtyMoved, QtyScrapped, AHrs")
FILTER(SUBSTITUTE("TransNum = {0}", V(vTransNum)))
SET(R(rsTrans) = RESULT)

Find more posts tagged with

Accepted answers

All comments

kazuyuki mochizuki

Hi Oliver,

weird, I tried it on my side and no error.. I didn't supply order by so I think its not required..

Is the IDO Extended?

Regards,

Kaz

OliverEngdahl

I don't believe so, but putting quotes around {0} appears to have worked. Perhaps we have an invalid value in the field and casting it to a string prevents it. Worth noting we're also on 9.0.

OliverEngdahl

Additionally, how do you do math and then set the result to a variable? I am pulling in Start Time and End Time off of Job Transactions, both in seconds, and setting them to variables. I want to do the math below and then append the values together to return 4:22:05 AM. I just can't get it to not pass in as strings.

vStartTime (in seconds) = 15725

vHour = vStartTime / 3600 which becomes

vHour = 15725 / 3600

and returns 4.368

Round vHour down to 4 to get the number of hours.

If it's less than 12, set vMeridiem to AM, if it's greater than or equal to 12, set vMeridiem to PM.

If vMeridiem is PM, subtract 12 from vHour.

vMinute = vStartTime - (vHour*3600) which becomes

vMinute = (15725 - (4*3600)) / 60

vMinute = 1325 / 60

and returns 22.0833

Round vMinute down to get the number of minutes.

vSecond = vStartTime - (vHour * 3600) - (vMinute * 60) which becomes

vSecond = 15725 - (4 * 3600) - (22 * 60)

and returns 5 seconds.

kazuyuki mochizuki

maybe cast will work?

Result

I didnt complete the computations. Can this be solved by datediff or dateadd? sample code below..

SETVARVALUES(vFinalDueDate = cast(CAST(DATEADD(day, - 1, CURDATETIME()) AS DATE) as string) , vFilter = SUBSTITUTE("DATEDIFF(Day, DueDate, '{0}') <= 2 AND StatCode IN ('OPEN', 'PENDING')", CURDATETIME()))

OliverEngdahl

Hi, Kaz.

I don't believe DATEDIFF or DATEADD would work here since it's returning a value in seconds and it's whenever the transaction was made, not the current time. Are you legitimately able to cast variables? It doesn't seem that I am because my code is the same as yours I believe.

SETVARVALUES(
vStartTimeHour = FLOOR(CAST(V(vStartTime) AS NUMBER) / 3600),
vEndTimeHour = FLOOR(CAST(V(vEndTime) AS NUMBER) / 3600)
)

kazuyuki mochizuki

Hello,

Can you try removing the FLOOR as a test if it will work? I think the floor is our only diff?

or maybe can you try to cast it again to a sting?

I just modified the code below by hand so I might get syntax error..

SETVARVALUES(vStartTimeHour = cast( FLOOR(CAST(V(vStartTime) AS NUMBER) / 3600) as string)

or something like

SETVARVALUES(vStartTimeHour = substitute( "{0}", FLOOR(CAST(V(vStartTime) AS NUMBER) / 3600) )

Regards,

Kaz

OliverEngdahl

I am not entirely sure what was any different, but you were spot on. I have some additional logic added for formatting but here's the basics. Thank you!

SETVARVALUES(
vStartTimeHour = FLOOR(CAST(V(vStartTime) AS NUMBER) / 3600),
vEndTimeHour = FLOOR(CAST(V(vEndTime) AS NUMBER) / 3600),
vStartTimeMeridiem = 'AM',
vEndTimeMeridiem = 'AM'
)

SETVARVALUES(
vStartTimeMinute = FLOOR((CAST(V(vStartTime) AS NUMBER) - (CAST(V(vStartTimeHour) AS NUMBER) * 3600)) / 60),
vEndTimeMinute = FLOOR((CAST(V(vEndTime) AS NUMBER) - (CAST(V(vEndTimeHour) AS NUMBER) * 3600)) / 60)
)

SETVARVALUES(
vStartTimeSecond = CAST(V(vStartTime) AS NUMBER) - (CAST(V(vStartTimeHour) AS NUMBER) * 3600) - (CAST(V(vStartTimeMinute) AS NUMBER) * 60),
vEndTimeSecond = CAST(V(vEndTime) AS NUMBER) - (CAST(V(vEndTimeHour) AS NUMBER) * 3600) - (CAST(V(vEndTimeMinute) AS NUMBER) * 60)
)

Copyright © 2025 Infor. All rights reserved.