Update rows in table

Epicor ERP 10
,
1

Hi

In a similar easy way to update many rows in a table in SQL using an UPDATE stored procedure, how can i do this in Epicor BAQ? I have created a BAQ that is updatable with the fields & criteria and i can edit each row individually with a double click but when you have over 900 rows, thats not efficient.

All of the rows are to be cleared if the value = a default value so its not a complicated need. Is it possible?

Thanks

2

Two ideas.

  1. Use DMT. It is made for exactly this. Though it does cost extra, I think it has been well worth it.
  2. Make sure your UBAQ is working 100% for single record updates. Then make a dashboard out of it. Deploy the dash, then get into it. Once there, you may be able to do some kind of paste/update using the grid. This will take some trial and error.

Barring those options… make a BAQ that filters out the exact records you want to update and only those records. Then use a BPM to cycle though each record in the BAQ and apply an update. You should be able to accomplish this with BPM widgets, and minimal (if any) custom code.
Good luck!

3

so… (:safe_harbor: Safe Harbor…We are doing stuff with DMT and UpdateEXT in a future release… and we did find something! :safe_harbor:
During some changes that we are doing in DMT, we are changing it to be more efficient, and instead of adding/updating/deleting one record at a time, we are going to be sending larger blocks of data (also know as "page size)…
What we found was that, while we are going to make this be a flexible setting, a page size of 200-300 maximum was the most efficient, and we saw a huge improvement as compared to doing it one record at a time. We also put a high end limit on page size dut to exactly what you have found.
Also, Updatable BAQs use UpdateEXT, so, same rule above would apply.

So, you might try changing how many records that you push to an update at a time (if possible in your code) to see if it more efficient by only updating 200 instead of 900.
:safe_harbor:

2 Likes
4

You can put the update processing on the post processing of GetList and then any rows read are processed.

5

This was especially painful for Cloud users. Plus, once the client goes away, there are no DLLs for DMT to use so I can see why this was a priority.

6

but the other reason for doing this was totally for performance. We found that for importing PARTS with DMT, that we saw a huge advantage using 200 record page size vs using the DLL. we can run the tests side-by-side, and we are seeing improvements we cannot pass up. More to come with Announcements at Insights 2024!
:safe_harbor:
Safe Harbor!
:safe_harbor:

7

Maybe some news on these somewhat related Ideas allowing DMT to run for Cloud users without installing a local client? :thinking:

8

I only have a small amount of experience with DMT and from what i recall, it wasnt the fasted process to update / insert data which is probably why i havent explored more…

I guess one easy way is to create an updatable dashboard, export out as excel, make an update and paste back but my inner self shouts back at me “whhyyyy” :slight_smile:

Its a one off job so i’ll begrudgingly do that. I like to think or hope that there is a better more fluid, less clicky way of doing things.

Thanks for all of your responses