Fill table by query v Update table by query

Kinetic ERP
1

What are the differences between Fill table by query v Update table by query and are there scenarios where either should be used over the other.

I am using Update table by query but I see some users suggesting Fill table by query should be used.

2

Fill Table by Query should is used to insert new rows into a table. Update Table by Query will allow you to change values in existing rows of a table.

image
image781×309 14.9 KB

3

Thanks @danvoss - that’s how I interpret the 2 different widgets but a user on the forum put together a reaonably length document on why, even when updating a table, Fill table by query should always be used.

I just wanted to see how other users understood the functionality in case I was missing something.

4

It is my understanding, based on some of the error messages I get when using it, is that Fill By is a LINQ query. Like, a fairly straightforward var yourThing = thisQuery, except it has the built in ability to cast results to non-generic objects.

Update also looks like it runs a LINQ query, but then loops through an existing dataset to set values.

I personally just write the queries myself in C# code blocks. I should, probably, use the widgets but I also don’t 100% trust them because sometimes they don’t work and the documentation doesn’t explain why. On top of that, I find the BAQ-esque UI tedious. Too many tabs and clicks. YMMV.

5

@jtownsend - can you share an example code block.

I stopped using code where widegts are available in the belief that it would make the upgrade process more straightforward.

Maybe I should revert back?

I’m in the middle of transferring a number of rather chunky service connect workflows into BPMs then I plan to upgrade from 10.2.200.10 to 10.3.

6

Widgets can upgrade more easily. And the definitely should be considered the first choice. However, it’s also a judgement call. Personally, I’ll use widgets unless it’s going to take like…six of them to implement what I want. At that point, I find them less readable. Legibility is paramount to maintainability, especially since I spend 10x more time looking at stuff I’ve done previously than I do debugging upgrade issues.

Here’s a query that takes a part# and returns the latest revision of it. Despite its simplicity, I don’t really see a good way to implement the sorting methods in the BPM widgets. It is something you could do in a full BAQ, where you can add order by and top clauses.

string lastRev = PartRev.Where(pr => pr.PartNum == PartNum &&
                                     pr.AltMethod == "")
                        .OrderByDescending(pr => pr.EffectiveDate)
                        .ThenByDescending(pr => pr.RevisionNum)
                        .Select(pr => pr.RevisionNum)
                        .FirstOrDefault()
                        .ToString();
7

Thank you.

I agree completely.

I’m slack at code commenting and then find myself taking quite a while to figure out what I am actually trying to achieve within the code snippet - as you suggest this takes longer than the actual debug more often than not.

The XML file handling I’m working on at the moment does not lend itself to widgets.

8

100%. There are no widgets for (de)serializing XML data. Meanwhile, the dotnet library has whole libraries for that built-in.

1 Like
9

True, but you could encapsulate that code in an Epicor Function and call those functions in a Function Widget any place related to that XML document instead of adding that code to multiple Directives or Functions.

10

I’ve haven’t delved into Functions yet - perhaps this is a more elegant solution than my mutli BPM approach.

11

Definitely use functions when the alternative is using the same code in 2+ places. Even when you’d be putting code, or any kind of non-trivial logic, in a single BPM. I’ve moved all my code to function libraries. My BPM’s are stubs with if/then blocks followed by function call widgets.

1 Like