Filter a ref list or slice

Steve_Chambers · 05-25-2021 11:38 PM

Hi All,
I have a tricky one here i was hoping someone could lend a hand

I have a 3 tables hierarchy

Order (parent)
Products (child)
Components (grandchild)

I have some other tables that are related but not owned by the tables above
Expenses
Inventory In
Inventory Out

When the user creates an order and adds a product they also create a component for their product, the component has an associated expense. The user then chooses whether or not to assign that component to the Inventory

So each order has two types of expenses
Cash Expenses
Inventory Expenses

I am trying to sum the total Expenses per order, to do this i have created some slices
Cash Expenses (slice of expenses table)
Inventory Expenses (slice of inventory out table)

However there are circumstances where if i simply sum to the two slices together some entries may be double counted due to the way the inventory cost calculations must occur.

So my question is what should my filter formula look like and where does my filter formula go to filter out Inventory Charges where there is a duplicate Component Key already found within that Order in the Expenses table? (I am wanting to show and sum the Inventory Expense only if there is no Corresponding Cash Expense already found)

I have tried using the below filter expression to compare the two lists in a few places, but not having any success, should i be working on getting this into a slice condition for the Inventory Expenses, or creating a filtered REF_ROWS VC column in the orders table?
(ISNOTBLANK(FILTER(“Inventory Out”, [Component Id] = [Component Id]) - LIST([Component Id]))))

Any ideas or guidance?

Steve_Chambers

I think i’m almost there, using Steve’s advice here

Typically, the REF_ROWS() virtual columns are added by AppSheet automatically. Modifying the column’s App formula expression may cause AppSheet to create another such column, so I don’t recommend modifying the existing virtual column. Instead, consider adding a new virtual column with an App formula expression like this:
SELECT(
  [ref_rows-column][row-key],
  ([_THISROW].[AffiliateCode] = [AffiliateCode])
)
replacing ref_rows-column with the name of the column with the REF_ROWS() expression, and row-key with the name of the key column of the table to which the REF_ROWS() expression refers.

I created a VC LIST REF in the Order Table type using

SELECT([Related Inventory Charges][InventoryOut Id],
(ISNOTBLANK(FILTER(“Inventory Out”, [Component Id] = [Component Id]) - LIST([Component Id]))))

where [Related Inventory Charges] is itself a REF_ROWS column in the same table using the formula:
REF_ROWS(“Inventory Expenses”, “Order Id”)

Which is filtering out the opposite entries that i want, my filter skills are letting me down

Steve_Chambers

no i take it back, I’m nowhere close to solving this

just to clarify,i have 2 REF_ROWs in my orders table

and i’m trying to create a third REF_ROW in my orders table that lists all the [Related Sliced Expenses] minus the [Related Inventory Charges] that contain the same Component key that is found in the [Related Sliced Expenses] list

Both the tables contain the Component key, both tables are obviously referencing the Orders table but Expenses and Inventory Out are not referencing each other directly

Might be easy for someone with superhuman filtering skills but that person is not me… not yet

Steve

I can’t make sense of this. Please explain the logic you need using plain language only; do not use any AppSheet jargon.

Steve_Chambers

hi Steve, Just trying to filter a table slice to remove entries that appear in a different table

Steve_Chambers

Hey Steve I ended up changing the data structure, so please disregard, thanks for reading anyways, appreciated