Every row of the UD table needs to have a unique combination of the key fields (key1, key2, etc…).
So at a minimum (even with DMT), you need to specify at least 1 key field. Since it looks like PartNum is the field that the New and old status’s will be saved for, Make Key1 hold your PartNum.
If it is possible that the record for PartNum can change again in the future, you’d need to UPDATE that existing record, not INSERT a new one. If you want to keep the existing records, and add new ones, you’ll need a second key (use Key2). Set it to the DATETIME to make it unique.
In this second scenarion you could have records like:
Okay thank you for that tidbit about the keys needing to be unique. I will tinker a bit more with some SQL statements and then post back with either a solution or more questions.
Since you’re still early in the learning curve, I’d strongly second Mark’s advice about not messing around with SQL. Things can go sideways REAL FAST.
To get a better handle on how UD table records are controlled, add a menu item for the UD02 form, and experiment with entering values in the form.
Also, the fact that you have UD Fields in your UD table would complicate any method to add or update records via some non-Epicor process (like direct SQL, or calls via PHP, ASP, etc…). Because the UD fileds are stroed in a separate table than the UD table itself. Using the Epicor functions (like DMT, REST, or data views in the UI) will treat those two tables as one, managing all the references between them.
@Aaron_Moreng - you might get asked to take that down. I posted “don’t do it, but if you do, here’s how” before. And was asked by the moderators to not do that.
Yeah that’s the really problem, we have custom UDs on the UD table. Since this is early development I could just switch use the built in fields even though they are generically named which we loathe but oh well.
You can rename the Labels for those built-in Columns. Doesn’t make reading the refences to them much easier. But it does help in BAQ’s, and provides a bit of documentation.
I have the following use cases:
We check a box on part entry inside Epicor. I want to record that change in a UD log table triggered from a BPM on part.update BO. The BPM C# code is going to reach out with a POST request with all the necessary data to a PHP endpoint I made.
Since starting this discussion I found the Epicor REST service for UD02.
Since we have full control of the endpoint we can do all the validation and manipulation to that data we want a whole lot easier than messing around in the sandbox the BPM provides, then do a REST call back to the Epicor server with the data to insert into UD02.
I do understand to be cautious with SQL. Aren’t UD fields are the exception? Base Epicor knows nothing about them and thus has no data integrity checks for them. If someone is updating base Epicor fields or inserting into base Epicor tables, then that’s obviously pretty bad, but custom fields… much lower risk. But never would I never do that anyway…
The PHP endpoint currently will log a field on part entry’s change into a table.
This log table will eventually be queried by another PHP web-application.
We found the Epicor change log is pain to query against and isolate just the fields you need so we’re making our own table to track this very specific checkbox.
Hi,
I have a question please.
If we manage primary key by BPM, what should we put in Key1 (in the DMT Template) ?. I just posted a post against this, I was wondering if you had any answers ?
I increment Key1 in a BPM, in GetaNewUD10 more exactly, but when I upload my file to DMT, I don’t know what to put in key1 to trigger my BPM.
Here is my topic if you want answer me there since this one i old or maybe solved.
