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
})
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.
UD is being used as queue: yes.
Iot module sounds interesting. But we do DOD work. Air-gapped assembly in SCIF. So we use ODATA and PWA type web applications. So no iot module for us with this project.