Has anyone developed a method for sorting field groups for the purpose of comparisons between them? I am pulling data from Epicor and would like to compare to data from IDC as a validation, but the tasks I am using don’t comprehend a match unless both field groups are in the EXACT same order.
I am watching this with interest. This whole thing that we can’t query the data in a field group is tough… Don’t you think the data in a field group is somewhere in the database though 
If you’re OnPrem then you can certainly access the AstriaHost database and within there is a ton of the datapoints, but this isn’t accessible for SaaS, as far as I am aware. Your question made me think that it may be possible for an OnPrem solution to create an External BAQ to the AstriaHost database to which calculations and sorting could be achieved. This new BAQ could then be added as a datalink source to populate a new field group. Not exactly a clean workaround, but a potential workaround nonetheless.
1 Like
Not clean at all. The workarounds that are needed to achieve some basic data functionality (comparing, sorting, adding a field/value, etc.) seem out of touch with most of the other Epicor software.
I stumbled across this task, which I’ve never used, but it could potentially add some of the functionality we’re after. It’s possible that the key field would be automatically sorted on, but without testing i am unsure.
But that’s only in one field group right? I thought you said you wanted to take action on two different field groups? I guess you could append to field group 2 all the rows from field group 1 and then do that task that you just posted…
That or run that task on each of the field groups before comparing. With that being said, I can create a BAQ to calculate and sort the data pulled form Epicor for one field group, but this task may prove useful to sorting a field group brought in from IDC. I haven’t tested this theory, yet, though.
1 Like
Will that task re-sort it? Or just do a grouping function? I thought your main objective was to get them sorted in the same order?
Unfortunately, I haven’t had a chance to test it yet so I am not sure. It being a key value may indeed sort it on that by default, but I just don’t know, yet. I’ll update once I get a chance to test this out.
The new Merge Task Lines task does not sort the contents of the field group.
Added the sorting idea for field groups:
https://epicor-ecm-docstar.ideas.aha.io/ideas/ECM-I-1162
I feel like we could just ask that the MergeTask, which is pretty much a join task, take into account left outer, inner, and right outer, with the ability to merge based on fields… much like a join statement.
It would be great if there were SQL capabilities as tasks! It would be even better if there was a way to have it create a temporary table where you could then apply those SQL like capabilities and then have it return the data.
The Merge Lines Task seems to have its place, but it is definitely not for sorting.
1 Like
But aren’t you sorting so that the merge task works?
No, sorry for the confusion. I was simply trying to see if the Merge Lines Task would sort by default on the key field. It doesn’t.
The Merge Lines Task does merge lines together where you designate the key field and those values match, though. From my perspective there isn’t a great number of applications for this task, but I would like to know if others have found it to be useful. The one application I do see it being helpful with is in aggregating line miscellaneous charges before applying those to the GL Analysis in ECM.
1 Like
I haven’t found a method to sort the field groups for comparison, but by using the Verify Field Group task I was able to achieve the results I was looking for. I stole the idea from the 3-way matching that the IDC workflow does as it was evident that the order of the line items coming in from IDC did not have to match the order of the line items when reading them from the receipt in Epicor.
I’ll leave this open for a bit, but this seems to be the solution as of this date. Hopefully they will include a task to sort items so that a simple Compare Verify can be used instead of this workaround which requires a datalink to function.
2 Likes