SQL Insert into UD Table view

How do you do a straight SQL insert into a UD table where you only want to add fields to custom fields?

I need to create a log of a particular checkbox being checked, we don’t want to use the change log for this for … reasons. Anyway, we’re going to use UD02. I’ve created custom fields in Extended Table Maintenance on UD02.

Here’s the SQL I tried:

DECLARE @Company   AS VARCHAR(8) = 'SD'
              , @Plant     AS VARCHAR(8) = 'MfgSys'
              , @UserID    AS VARCHAR(75) = 'smason'
              , @PartNum   AS VARCHAR(50) = 'test123'
              , @NewStatus AS BIT = 1
              , @OldStatus AS BIT = 0;

        INSERT INTO dbo.UD02
            (Company_c, Plant_c, UserID_c, PartNum_c, NewStatus_c, OldStatus_c)

        VALUES
            (@Company, @Plant, @UserID, @PartNum, @NewStatus, @OldStatus)

I get the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint “FK_UD02_UD_UD02”. The conflict occurred in database “E10Train”, table “Ice.UD02”, column ‘SysRowID’.

I know it’s because the parent UD02 table record hasn’t been created and I’m trying to insert into the UD02_UD table without it first having a parent. I think the child needs the SysRowID of the parent.

Does anyone have thoughts on how can I create a record in the parent table and then use that SysRowID in the child table in one nicely packaged series of SQL statements?

Use DMT

edit

And you must specify the Key fields and values.

Maybe you’re really trying to do an Update of existing UD records

1 Like

For one time inserts DTM that would be an option. This insert statement is going to be on an endpoint I want to call programmatically from the web. It’s going to be new records, not updating existing records.

I’m also open to calling a REST service for inserting into a UD table but don’t know which service it might be.

What version of Epicor are you on? UBAQ or function may be your friend.

1 Like

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:

Key1     Key2                 UserID  OldStat  NewStat
ABC-123  2020-10-01Z13:04:34  smason    0         1
XYZ-123  2020-11-13Z09:15:34  smason    0         1
ABC-123  2020-11-13Z11:05:12  smason    1         0

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.

I would advise against a direct SQL. Try and use some of the Epicor Tools.

1 Like

Just looking at that makes me a nervous nellie. :grimacing:

2 Likes

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.

Whoops, ok, I’ll remove

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.

image

Use a UBAQ for that, that’s your best bet

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… :wink: :wink:

Are you just logging the change? There is a change log feature already vs a UD table.

What does your PHP endpoint do?

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.

https://server/epicor10/api/help/v2/odata/Ice.BO.UD##Svc/index

where XX is the table ID. Search in the api/help in the Business Object section for the UD table of interest.

https://server/epicor10/api/help/v2/

Don’t forget there’s OData and Custom methods…

Use Case is ??

When Change on Part Table happens.
Log change

What is the PHP endpoint doing? Reading the UD02 table?

Sorry I’m just confused on the back and forth.

You could still use the ChangeLog BPM to update the UD table. That would be within Epicor. No need for the External process to create the log in UD02.

Then your external PHP could call REST to get the data it needs.