ODATA request to fetch latest UD105 rows

We have defined an extension on UD105 with 27 custom columns. One of them is called LastModified_c which is a long (64 bit int). Another is a Guid_c which is a unique GUID.

I am also setting
Key1 = Guid_C
Key2 - LastModified_c

and then I was trying to find the latest rows with the following code:

    const refreshJobList = (async (fields) => {
        const columns = fields.join(',')
        let nowish = Number(Tools.timestamp()) - (60 * 1000) //TODO figure out best window
        let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `Key2 gt '${nowish}' `, $select: `${columns}` })
        //let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `LastModified_c gt 5 ` })
        //let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `LastModified_c gt ${nowish} ` })
        return reply
    })

function timestamp(obj) {
    return (Date.now()).valueOf().toString()
}

/**
 * EPICOR invoke ODATA Get command - used NPM O.js (odata) library
 * @param {string} serviceBO - Busines Object that provides service
 * @param {string} args - the ODATA command and any arguments
 * @param {json} query - ODATA query object (e.g. {$filter: `UserName eq 'foobar'`})
 * @return {string} The next job number
 * @public
 */
const get = (async (serviceBO, args, query) => {
    let reply = {}
    const url = SERVERURL + serviceBO
    const options = window.STORE.getState().identity.options
    try {
        reply = await o(url, options).get(args).query(query)
    } catch (error) {
        console.log(`Error Code: ${error.status}`)
        console.log(`Error URL: ${error.url}`)
        const json = await error.json()
        const body  = JSON.stringify(json, ' ', 4)
        console.log(`Error body: ${body}`)
        return error
    }
    return reply
})

The first version sorta works. Key2 is a x(50) string. but it someone knows how to properly compare the incoming timestamp and do a fast compare.

The issue is that I want to keep updating the LastModified using a rowMod

const updateJob = (async (jobData) => {
    let row = { ...jobData, RowMod: "U", Company: "TRI" }
    let reply = await Comm.post(MoveURL, 'UD105s', row)
    return reply
})

and when I change Key2, I get a new record, not an update of the old record. That is sad :frowning: because I really want the fast search that a index key gives in a data base, and not search all the records by LastModified_c and see if that is .gt. a value.

And even if I tried to do do that (see the last commented line

    //let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `LastModified_c gt ${nowish} ` })

this will not work. Epicor Odata converts the gt 13783093438 which is a long and thinks it is supposed to be edm.Int32 and throws and ODATA error that the long value is a syntax error for int32s.

Does this make sense? or do I need to provide more info?

I’m not sure I follow what you are trying to do, but
FYI you can’t change the keys (Key1-Key5) are the primary key of the record and cannot be changed. Once a UDXX record is created the Keys are what they are.

Don’t use Key2 as the modified time and you’ll be set. Just rebind to a different field to get around key-changing limitations.

1 Like

But if I don’t use one of the Key2 … Key5 indexes. And instead put the timestamp in a UD105 exension column (UD105.LastModified_c) which is a 64 bit long, then I believe you get no DB accellerration for looking for last modified.

In PostGres one would create a secondary key, add indexing to to, and then it would do the SQL search based on the index, not on searching every row for the matching columns.

Is there not a way to do this with UD tables?

Key1-Key5 are defined as primary keys. PostgreSQL would act the same. I believe others have added their own index to one of the other fields if on prem.

And how does one add one’s one index to one of the UD Field Maintenance columns (in our case LastModified_c).

Also note my issue above. It seems that the EPICOR ODATA Rest interface allows one to stick a 64 bit value into the LastModified_c only if it is sent as a string “34309343093344534”, but then one cannot do a .gt. compare using a filter since that changes it to a 32 and I can a type mismatch!

Follow this thread

Thanks for the pointer. So it sounds like I using SQL server to create a non-clustered index on the column I want, and then E10 will automatically pick up on this when it is doing a filtered ODATA search. Is that the correct gist of this?

That is my assumption, yes.

Well, even when I do that, I still get this error about using 64 bit values as a ODATA filter:

GET https://pwsepicorapp.draper.com/ERP10.2Test/api/v1/Ice.BO.UD105Svc/UD105s?%24filter=LastModified_c+gt+1568050992825+ 400
CommDepartment.js:76 Error Code: 400
CommDepartment.js:77 Error URL: https://pwsepicorapp.draper.com/ERP10.2Test/api/v1/Ice.BO.UD105Svc/UD105s?%24filter=LastModified_c+gt+1568050992825+
CommDepartment.js:80 Error body: {
    "HttpStatus": 400,
    "ReasonPhrase": "REST API Exception",
    "ErrorMessage": "Unrecognized 'Edm.Int32' literal '1568050992825' at '18' in 'LastModified_c gt 1568050992825 '.",
    "ErrorType": "Microsoft.Data.OData.ODataException"
}

Here is how I am phrasing the issue now:

Description
We have a UD105 table with an extension column called LastModified_c which is a LONG (uINT64).

We have heard that we can use SQL server to create a clustered index on that column to speed searches (and the EPicor10) will automatically find that SQL has an index on that column.

First Question: Is that correct?

Second Question:

I have no trouble POSTing with ODATA Rest calls to set the value. But I am having an issue with doing a filtered get based on that column.

As you can see, it is converting the long to a int32, and then not able to compare it to the LONG value in LastModified_c

GET https://pwsepicorapp.draper.com/ERP10.2Test/api/v1/Ice.BO.UD105Svc/UD105s?%24filter=LastModified_c+gt+1568050992825+ 400
CommDepartment.js:76 Error Code: 400
CommDepartment.js:77 Error URL: https://pwsepicorapp.draper.com/ERP10.2Test/api/v1/Ice.BO.UD105Svc/UD105s?%24filter=LastModified_c+gt+1568050992825+
CommDepartment.js:80 Error body: {
    "HttpStatus": 400,
    "ReasonPhrase": "REST API Exception",
    "ErrorMessage": "Unrecognized 'Edm.Int32' literal '1568050992825' at '18' in 'LastModified_c gt 1568050992825 '.",
    "ErrorType": "Microsoft.Data.OData.ODataException"
}

I have tried a number of different things, (quoting the ‘${nowish}’ but that does not work.

Searching with a small int (5) works. As does on Key2 (which has the same value as LastModified).

/*
 * Epicor API call for getting recent Job Headers based on user set filter, select, etc.
 * @param {string[]} fields - the fields that you actually want to see
 * @return {json} Response to API call from EPICOR
 * @public
 */
const refreshJobList = (async (fields) => {
    const columns = fields.join(',')
    let nowish = Number(Tools.timestamp()) - (60 * 60 * 1000) //TODO figure out best window, currently 1 hour
    //let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `Key2 gt '${nowish}' `, $select: `${columns}` })
    //let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `LastModified_c gt 5 ` })  -- this works since 5 is a UINT32
    let reply = await Comm.get(MoveURL, 'UD105s', { $filter: `LastModified_c gt ${nowish} ` }) // this fails since nowish is a UNIT64 long, just like LastModified_c
    return reply
})

Never tried it in a URL but…

Why don’t you use a normal date time variable? Instead of this timestamp?
Then you can make DateTime oData aware calls.

1 Like

Resolution of clock. Since ODATA turns this into a text timestamp, you lose a lot.

We are not using WCF, but an ODATA REST interface.

ok so let’s start over from where I usually start.

What’s the end goal. (not what are you trying to technically do, but what are you trying to functionally do and why)
There are many ways to skin a cat. UBAQ being my favorite.

3 Likes

How many jobs per millisecond are you creating?

1 Like

They are not true “jobs” but rather pseudo-jobs (rows) in a UD table. They correspond to equipment moves. Sometime they might come a milliseconds apart, sometimes minutes apart.

Gotcha. So you’re using the UD table as a queue and want to preserve order received?

I’m going to queue @edge here. Have you considered using the new IoT feature? It uses Azure and it will collect all that data for you and then you can generate REST actions back to your Epicor system. It would allow you to do other actions/monitoring as well. It’s pretty slick IMHO.