topic Re: nil can't be coerced into Float when trying to add 3 searches together in Workato Pros Discussion Board

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

johnw — Fri, 29 Sep 2023 16:38:07 GMT

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

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

johnw — Fri, 29 Sep 2023 22:09:54 GMT

I got it working but its NOT pretty so have 3 search actions 1 for each company pulling 5 Cost fields each.

Then I created I List variable and created 15 Update List variable actions to add those 15 fields to the list.

then I looped through the list variable checking for nil and adding to my original variable to ensure it was a float.

this is working but like I said not pretty I was hoping to pass the searches datapil directly into a variable list which i suspect can be done but I tried everything I could think of.

tonight, this will run in sandbox and I'm curious how long it will take to crunch 2500 or so parts

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

gary1 — Fri, 29 Sep 2023 22:25:03 GMT

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

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

gary1 — Fri, 29 Sep 2023 22:26:52 GMT

Glad you made some progress. If you can provide some examples of how the response is formatted, I'm sure there are more elegant and efficient ways to do this.

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

johnw — Mon, 02 Oct 2023 12:25:54 GMT

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

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

gary1 — Mon, 02 Oct 2023 16:16:43 GMT

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.

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

johnw — Mon, 02 Oct 2023 16:37:14 GMT

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

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

gary1 — Mon, 02 Oct 2023 17:13:52 GMT

The formatting is a little hard to follow. Are you able to send screenshots?

To update the field type, you could edit the schema or just click the edit pencil and change the type using the dropdown.

Back to the formula, here are a couple examples:

Example 1: Three string variables with null values:

Formula to add:

Formula output:

Example 2: Three string variables with actual values:

Same formula as above is used.

Output of the variables (strings):

Formula output:

Be careful with spacing in the Ruby formula. If you don't have a space after the second question mark, it will error. Third row below is missing a space:

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

johnw — Mon, 02 Oct 2023 17:41:28 GMT

feel like I am soo close... here is my simple query to pull the fields for 1 company

and here is the creation of the variable and using thsoe fields:

but when I test it it seems to be putting in the right costs but within a string of the formulas as shown here

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

gary1 — Mon, 02 Oct 2023 18:03:05 GMT

You are very close 🙂

You just need to click the "Formula" toggle on the upper right of the field. It's currently set to text mode.

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

johnw — Mon, 02 Oct 2023 18:30:20 GMT

ok I now have topped a "I feel stupid" moment

thank you for all your help

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

gary1 — Mon, 02 Oct 2023 18:36:16 GMT

Haha, it's all good. The important thing is you got there!

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

johnw — Mon, 02 Oct 2023 20:02:02 GMT

now to that second part of making a list. When I create a search to pull the QtyOnHand field and the NonNettable Qty from a warehouse

I get multiple records due to different warehouse codes

so how do I get these qtys into an List to than use in a loop and sum up? I thought the formula you just showed me was going to work till I realized there could be more than 1 warehouse code per company.

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

anthony_chen — Tue, 03 Oct 2023 01:18:29 GMT

What are you doing to query? One problem is that if you have 2500 items and you iterate through each one you are using 2500 tasks. I dont know about you but we get 250,000 tasks/year for a recipe. So you will run through your tasks very quickly.

If you are using the sql connect list or you have a connection that allows sql (e.g. snowflake, postgres, etc) you can filter out the nulls in the query and then do a sum in the query.

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

anthony_chen — Tue, 03 Oct 2023 01:52:41 GMT

I did a bit more research. Unfortunately the sql collection I mentioned in my reply wont do sum. It only will let you filter out null values.

Normally in sql you can do something like this:
SELECT SUM(quantity) AS TotalQuantity FROM your_table_name WHERE quantity IS NOT NULL;

It looks like you need the sql transformations app to do summation. You dont get the app by default

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

johnw — Tue, 03 Oct 2023 13:03:30 GMT

thanks everyone for all the help... since the main issue on this one was solved, I have spun off this secondary question to its own post found here: Sum up costs from multiple Companies and warehouse... - Systematic Community - 5287 (workato.com)

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

moxiego — Thu, 16 Nov 2023 17:48:42 GMT

Gary,

Can you explain/give an example of step #3

"Use the repeat helper list in a repeat action to loop 3 times"

I have a repeat helper list, how do I iterate through it. or update the sequence?

Thanks

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

gary1 — Thu, 16 Nov 2023 18:24:20 GMT

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:

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:

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.

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

moxiego — Fri, 17 Nov 2023 19:23:19 GMT

Thank you, this is super helpful.