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

johnw · ‎10-02-2023

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.

anthony_chen · ‎10-02-2023

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

anthony_chen · ‎10-02-2023

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.

johnw · ‎10-03-2023

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)

Systematic Community

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