09-29-2023 09:36 AM - edited 09-29-2023 09:38 AM
is there an easier way to do this?
I have 3 companies with 5 warehouses each
so I do 3 searches t find all the costs in each warehouses and then just add the data pills together and this worked fine.
but then I started seeing the Nil can't be coerced error so some warehouses will be blank.
do I need to loop through each search and do a if .present to add them together? or is there a trick I'm missing like in SFDC where there is a way to treat NULL as 0
also, is there a way to do a loop through 3 searches? or do I need to create a list variable which I'm trying to do but can't seem to combine the 3 searches into a single list
sorry if these are basic questions very new
Solved! Go to Solution.
09-29-2023 03:25 PM
There are a lot of ways to do this, but it depends on how your data is formatted. If you can make an array of all of the costs, you can use [array].compact.sum, where compact will eliminate the nil values. Otherwise, if you want to add them up individually, you will need to handle nil values with .present? like you mentioned. Something like:
([wh1].present? ? [wh1] : 0) + ([wh2].present? ? [wh2] : 0) ... and so on
For your second question, you can loop through three searches, but first you need an input to determine how many times to loop. Here's the general idea:
Hope this helps
11-16-2023 10:24 AM
No problem, let's review the Repeat Action and the Create Repeat Helper
Here's a screenshot of an example recipe I'm going to reference:
When you use the Repeat Action (Step 5), you must provide a list object as input. The length or size of the list informs the Repeat Action how many times it needs to repeat. If you provide a list with 100 items, it will repeat 100 times and process each item.
Sometimes you don't have a list, but you only have data that could be a list. For example, maybe you have an array containing [1, 5, 10] in Step 3:
Unfortunately, you can't use this data or formulas as input for Repeat Action, so you need to use the Repeat Helper to prep the Repeat Action.
When you use the Lists > Create Repeat Helper (Step 4), this action takes a number as input and returns very simple list that is basically intended to be used as input for the Repeat Action, so the Repeat Action knows how many times to repeat. Since you have an array with three items, you can use [1, 5, 10].size to get "3" as a result and use it as input for the Repeat Helper.
Doing this will create a list with 3 items to use as input for the Repeat Action. (The value of List Size in the below is "3".)
Use this output datapill as the input for the Repeat Action and you'll be able to iterate through your array and access its individual items.
Within the Repeat Action, you have access to the Index of the current repeat/loop. The index starts at 0 and increments +1 with every repeat.
You need to use the loop's index with the original array to access the array's individual items, like this:
For this example, the Repeat Action will loop three times, and the above action will output the following:
As a more advanced example, if you have an array of objects in Step 3 like this:
You can access them in the loop by using bracket notation to access the key name ("number"), like this:
This action would output the same as the original example because we're accessing the value of "number".
If you didn't use bracket notation to access "number", it would output the entire object, (e.g., {"number": 1})
You can use bracket notation to navigate more complex objects too.
I hope this helps! Let me know if you have other questions.
11-17-2023 11:23 AM
Thank you, this is super helpful.
10-02-2023 05:25 AM
weird my last response didn't show up here...
as I said above I did get it working by manually creating a Variable list (super cludgy) and looped through that with this formula:
=_('data.workato_variable.21e6694a.AverageCostVar').to_f + _('data.foreach.2d63c183.Cost').to_f
but when I tried your suggestion and did the .present formula within a single variable which is exactly what I nee dto do it seems to be returning a string and not the final number:
(#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgLaborCost')}.present? ?#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgLaborCost')}.to_f : 0) +(#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgBurdenCost')}.present? ?#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgBurdenCost')}.to_f : 0) +(#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgMaterialCost')}.present? ?#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgMaterialCost')}.to_f : 0) + (#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgSubContCost')}.present? ?#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgSubContCost')}.to_f : 0) +(#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgMtlBurCost')}.present? ?#{_('data.epicor_kinetic_connector_638485_1652373679.c3606d3d.value.first.AvgMtlBurCost')}.to_f : 0)
hope this text makes sense I wonder if my screen grabs didn't work before
10-02-2023 09:16 AM
Your formula looks good, but the previous actions producing the cost values might have their output fields configured incorrectly.
Output data pills default to strings. So if you're using an HTTP connector to get the data, configure your cost output field to be an integer/float.
If that doesn't work, then you should be able to wrap the entire formula in parentheses and .to_f its output.
10-02-2023 09:37 AM
I tried the ().to_f but still not working
------------------------
as far as changing the output of the search, I see where I add what fields I want to get but no where to set the output type