topic Re: Sum up costs from multiple Companies and warehouses in Epcior in Workato Pros Discussion Board

Sum up costs from multiple Companies and warehouses in Epcior

johnw — Tue, 03 Oct 2023 13:01:35 GMT

Our version of Epcior has 3 Companies (ACA1, AUS2 and PICO)

I have a function where I want to pull the QtyOnHand for any SKU that is called by this function

KN2SF parts QtyOnHand Function SKUXYZ

the function will do 3 searches such as this:

and it can output nil or multiple warehouses with qtyonhand and NonNettable Fields

what's the best way to take all these numbers from 3 different search steps and add them together.

I've been trying to create different types of variable sand lists and not having any luck.

I did hard code a list and added each step individually and this worked as long as there was only NIL or 1 warehouse in each company else it would return 0

thanks

John

Re: Sum up costs from multiple Companies and warehouses in Epcior

johnw — Tue, 03 Oct 2023 13:07:28 GMT

@anthony_chen responses in other thread:

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.

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

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: Sum up costs from multiple Companies and warehouses in Epcior

johnw — Tue, 03 Oct 2023 13:11:09 GMT

Thanks @anthony_chen I am checking with IT to find out our license limitations... we may be ok since we only have 2500 or so SKUs being moved from Epcor to SFDC and then only scheduled one to check all of them will be for cost and that will be weekly. BUT its good to know where those limits are and be prepared.

as far as SQL Transformation is concerned, I found it here and we don't have it BUT it says its only for CSV is this what you are referring to or is there something else that could work for my situation.