topic Re: Unique List of records from an Epicor BAQ in Workato Pros Discussion Board

Unique List of records from an Epicor BAQ

johnw — Tue, 01 Oct 2024 20:47:07 GMT

I have an Epicor BAQ with a list of Order Transactions that I use within a recipe that's working perfectly.

in a different recipe I want to see a list of Unique Order numbers on this report and didn't want to create a new BAQ

my thought was to execute the BAQ but only retrieve the 3 fields I need which will have lots of duplicates since every order can have multiple transactions.

was hoping there was some way to only show unique records when I execute the BAQ based on the 3 visible fields but don't see anything obvious... is there some combination of pluck and unique that works on a whole record as opposed to just one field in the record?

John

Re: Unique List of records from an Epicor BAQ

gary1 — Tue, 01 Oct 2024 21:45:26 GMT

This is definitely doable but it depends on what your end goal is.

If you just want a list of unique IDs, then pluck and uniq will work, like this:

orders = [{"id": 1}, {"id": 1}, {"id": 3}, {"id": 3}] orders.pluck("id").uniq ## returns [1, 3]

This assumes you're determining uniqueness based on the ID only.

Re: Unique List of records from an Epicor BAQ

johnw — Wed, 02 Oct 2024 13:22:37 GMT

now this is probably a dumb question... not sure where I can add this formula... was thinking I could add it into the loop step but all it allows me to do is add the input list, I can't change to a formula field and do the pluck unique there so then was thinking I could create a list Batch and do it in that step but its not obvious

here s my recipe where Step 3 runs the BAQ and gives me lest say 20 records but only 3 unique records

I do the if to make sure its not empty and then loop through all the records... so it seems pretty straight forward just not sure how t insert this pluck unique before the loop step

Re: Unique List of records from an Epicor BAQ

gary1 — Wed, 02 Oct 2024 16:09:11 GMT

Are you trying to loop through only the unique records?

What you're trying to accomplish is something Workato makes very difficult to do. I'm happy to explain how to do it, but I want to make sure I understand what you're trying to accomplish before I spend the time.

Re: Unique List of records from an Epicor BAQ

johnw — Wed, 02 Oct 2024 16:10:43 GMT

yes... I just want to loop through the unique records

where the record would be 2 fields Company and Sales Order number

Re: Unique List of records from an Epicor BAQ

gary1 — Wed, 02 Oct 2024 16:33:48 GMT

Got it. Here's the easiest way I can think of doing this:

Run your query to get results
In a logger, do results.uniq. This will return an array of unique objects. This assumes the objects in your array are 100% identical. If even one key/value is different,
Add a JSON parser and copy/paste the output of the logger as the Sample Document (this has to be text and not a data pill)
In the JSON parser action, add the logger data pill as the Document
The JSON parser will output a list, so you can use it as your input to your loop

Give this a shot and let me know if you get stuck.