Systematic Community

johnw · ‎09-29-2023

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

gary1 · ‎09-29-2023

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

gary1 · ‎10-02-2023

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:

johnw · ‎10-02-2023

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

gary1 · ‎10-02-2023

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.

johnw · ‎10-02-2023

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

thank you for all your help

gary1 · ‎10-02-2023

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

Systematic Community

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