cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

nil can't be coerced into Float when trying to add 3 searches together

johnw
Executive Chef I
Executive Chef I

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

1 ACCEPTED SOLUTION

gary1
Executive Chef III
Executive Chef III

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:

  • You have 3 companies, and I'm assuming each company has an ID. Put the IDs in an array.
  • Make a repeat helper list with [array].size (resolves to 3)
  • Use the repeat helper list in a repeat action to loop 3 times
  • Create your action to run your searches (which I assume requires a company ID)
  • Parameterize the company ID in the search action by using [array][loop_index], which will return the ID in the array that corresponds to the index of the current loop
  • Add the cost from the response to a list 
  • Outside of the loop, pluck the costs from the list and sum them

Hope this helps 

 

View solution in original post

18 REPLIES 18

gary1
Executive Chef III
Executive Chef III

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:

gary1_9-1700158617705.png

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.

gary1_0-1700157785005.png

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:

gary1_5-1700158212942.png

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.

gary1_6-1700158263623.png

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".)

gary1_10-1700158736288.png

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.

gary1_11-1700158751957.png

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.

gary1_7-1700158371271.png

You need to use the loop's index with the original array to access the array's individual items, like this:

gary1_8-1700158494201.png

For this example, the Repeat Action will loop three times, and the above action will output the following:

  • Loop 1 (Index 0) = 1
  • Loop 2 (Index 1) = 5
  • Loop 3 (Index 2) = 10

As a more advanced example, if you have an array of objects in Step 3 like this:

gary1_12-1700158855204.png

You can access them in the loop by using bracket notation to access the key name ("number"), like this:

gary1_13-1700158894221.png

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.

moxiego
Deputy Chef II
Deputy Chef II

Thank you, this is super helpful.

johnw
Executive Chef I
Executive Chef I

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

 

gary1
Executive Chef III
Executive Chef III

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.

johnw
Executive Chef I
Executive Chef I

I tried the ().to_f but still not working

  • {
    • 2  variables: {
      • 3    schema: [{"name":"Total_Cost_Var","type":"integer","optional":true,"label":"Total_Cost_Var","details":{"real_name":"Total_Cost_Var"},"control_type":"integer"}],
      • 4    data: {
        • 5      Total_Cost_Var: ((0.0.present? ?0.0.to_f : 0) +(0.0.present? ?0.0.to_f : 0) +(676.0.present? ?676.0.to_f : 0) + (0.0.present? ?0.0.to_f : 0) +(11.6366.present? ?11.6366.to_f : 0) + (.present? ?.to_f : 0) +(.present? ?.to_f : 0) +(.present? ?.to_f : 0) + (.present? ?.to_f : 0) +(.present? ?.to_f : 0) + (.present? ?.to_f : 0) +(.present? ?.to_f : 0) +(.present? ?.to_f : 0) + (.present? ?.to_f : 0) +(.present? ?.to_f : 0)).to_f
            6}
        7}
    8}

------------------------

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