topic Compare/Count a field in a datapil to other records in the same datapil without looping? in Workato Pros Discussion Board

Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Mon, 29 Jul 2024 14:47:42 GMT

I have an Epicor BAQ report I use in Workato that gives me a list of all SKUs in an order and their shipping information

99.9% of the time all the SKUs in an order will be on the same pricebook but on rare occasions 2 SKUs might on pricebook X and 1 SKU on pricebook Y so each record in the BAQ report will have a pricebook field

is there a way in workato to count the number of different prciebooks or do i have to loop through each record to count them?

thanks

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

gary1 — Mon, 29 Jul 2024 15:25:41 GMT

I don't know how your data is structured, but you can use a combination of pluck and uniq to accomplish what you need.

Here's how they work:

list = [ {"book": "a"}, {"book": "a"}, {"book": "b"} ] books = list.pluck("book") ## outputs ["a","a","b"] uniqbooks = list.pluck("book").uniq ## outputs ["a","b"] uniqbooks.size ## outputs 2, the number of items in the array

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Mon, 29 Jul 2024 16:54:48 GMT

this is cool... haven't played with Lists much in here.

So I created a List and loaded it with the field from the datapil which runs the BAQ report.

Do I need to create the list or is there a way to do these formulas directly from the data Pil; saving that one step?

this works perfectly

se list formulas directly from the BAQ step or if I have to convert to a list first

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

gary1 — Mon, 29 Jul 2024 17:06:23 GMT

You can do it directly from the output of the Epicor action.

Also, just a heads up that will hopefully save you some time...

When you use .pluck you need to refer to a data pill by its "API key", which is the schema key and NOT it's readable label. This usually trips people up when using methods on connector data pills with really long labels, or when the label and the key are completely different.

To see the API key, just hover over the data pill. If you were to pluck the pill below, you would use .pluck("field_1") and not .pluck("blah blah blah...")

I'm mentioning this because you might have to pluck "Price list 2 of price book ID 18 char c" and I would be very surprised if that label was also the API key.

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Mon, 29 Jul 2024 17:50:30 GMT

thanks, I was trying to use the output instead of the value which is why I couldn't get it to work 🙂

pretty cool

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Fri, 16 Aug 2024 23:48:16 GMT

this is working great but now I'm curios if there is a way to see if there are any blanks

if I do this and test [Value Step3}.pluck('PriceLstParts_SfPriceBookEntryId18Char_c')

I get a list of tehse fields such as

"A" "B" "C"

but sometimes its "A" "B" "" "C"

where one is blank I just want tos ee if Blanks are greater than 0

played around with scan and blank etc can't seem to get it to work

[Value Step3}.pluck('PriceLstParts_SfPriceBookEntryId18Char_c').blank?

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

gary1 — Sat, 17 Aug 2024 03:17:17 GMT

It's not published in Workato, but "count" is a standard Ruby method that you can use.

@meghan-legaspi Could someone add "count" to the formula helper and also to the Ruby docs?

https://docs.workato.com/developing-connectors/sdk/sdk-reference/ruby_methods.html

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

meghan-legaspi — Wed, 21 Aug 2024 20:08:14 GMT

Hey @gary1 – great thought! I can pass this along, though I also recommend submitting that feedback through the portal. Our team loves to keep in touch with the folks that make these requests to let them know when it's done! 🙂

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

gary1 — Wed, 21 Aug 2024 22:59:36 GMT

Is the portal the one where users have to vote on which requests get prioritized?

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

meghan-legaspi — Fri, 23 Aug 2024 15:19:06 GMT

Hey Gary,

You can submit feedback via AssistIQ when you are logged into your Workato account (see screenshot below). This should go straight to our support team – user voting shouldn't affect that.

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Wed, 25 Sep 2024 11:05:18 GMT

how would pluck be used to see if there were any blank/empty fields?

was thinking something like this:

["value"].pluck('PriceLstParts_SfPriceBookEntryId18Char_c').count("") > 0

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

gary1 — Wed, 25 Sep 2024 23:54:51 GMT

When data is missing, it should be missing in a few different ways:

The key is missing
The key is present and the value is ""
The key is present and value is null

If the key is always present and a blank value is "", then this will work!

Re: Compare/Count a field in a datapil to other records in the same datapil without looping?

johnw — Thu, 26 Sep 2024 13:50:25 GMT

got twisted around and needed to add a null option so this fixed my issue:

["value"].pluck('PriceLstParts_SfPriceBookEntryId18Char_c').count(null) > 0