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