Writing data to UD Tables

[steveh (Steve Hare)]

Hi everyone.

I have been working on a proof of concept to import some log file data from our CNC machines. I have a process working using a combination of PowerShell script and SQL / BCP to import the data and it all works fine. The next stage is to make this accessible in EPICOR so I can match it to jobs etc.

The real simple solution appears to be a direct import into a UD table (using said BCP command), I have tried this on a test database and it appears to work, but it seems like this has “bad practice” written all over it

So really my question here is: Is it OK to import data directly into a UD Table such as UD01, UD02 etc.?

I know that I have other options, automated DMT, external data sources etc. but I like simple so I just want to see if this approach is OK first?

Thoughts / comments appreciated.

[Doug.C (Doug Crabtree)]

I asked a similar question here:

DMT or SQL for UD

I do not go into the SQL side to ever write things in Epicor. I have plenty of external views that I have written to read, but I use BOs and DMT to do my data injections. That being said, I have a large amount of data that I need to put into a UD table. There are no customizations for DD or MD through BPMs on this table. Would it still be a no-no to use SQL injection to a UD table? In playing around in a sandbox, it takes about 6 minutes to load 500k+ lines of data. I don’t think DMT will …

Again, any direct injection is a “do it at your own risk” proposition.

[timshuwy (Tim Shoemaker)]

Personally, I would look into using Rest calls to import your data into the UD Tables. there are built in business methods that will make sure that you did everything correct if you use the Rest service.

[dhewi (Daryl Hewison)]

I think just don’t go there. Arguably UD tables are the lowest risk, but direct SQL is just not OK for Epicor.

You could use PowerShell with REST and get the data in there, but I’ve moved over to storing this kind of data outside Epicor altogether and accessing it via External BAQ when needed whenever possible. It feels so much cleaner.

[Mark_Wonsil]

PowerShell has the Invoke-RestMethod cmdlet which would make it easy to use instead of BCP. When you get to 10.2.500, you can use Epicor Functions to do the hard work for you. Pass in a Json string and load the UD table for you.

And if you want to get really fancy, you could send the machine data into Azure (IoT) and fire off events to update the jobs in Epicor. The added benefit is now you can do some monitoring and analysis with the aggregate data.

[steveh (Steve Hare)]

Thanks for the replies. Some ideas to consider.

We are evaluating IoT at the moment, but think it’s some time off yet, but I am hopeful we will get there, things have slowed down a little in the current climate

As far as REST is concerned, I have been looking at this also, are there any good resources out there to help learn a little more?