Need some help with transactions in my app

[JohnHar1234]

In my app i need to record both positive and negative transactions ( we are a no from profit that take old item cleans then up and gives them to people in need, so we need to records getting the items and giving them away ) 

SUM(SELECT(Transactions[Change in stock], ([Row ID] = Transactions[Inventory ID]),FALSE))

I'm getting the following error: Cannot compare Text with List in ([Row ID] = Transactions[Inventory ID])

I'm trying to get all the transactions + and - and add them up to give us a number

[BHUVANESH]

Use In Condition to compare the list
SUM(SELECT(Transactions[Change in stock], IN([Row ID] ,Transactions[Inventory ID])))

[mikeas · Accepted solution]

Hi John,

Great use of AppSheet, and it looks like your tables are well designed. Because you have references setup between your tables, there's an automatically generated column that helps you to find all Transactions related to an Inventory item: the Related Transactions virtual column of your Inventory table. This column gives you the list of all transactions as a list of refs. One neat thing you can do with a list of refs is a list dereference, which allows you to get a particular column from the referenced table. Since you'd like to get the "Change in stock" values from the Transactions table, you can do: [Related Transactions][Change in stock]. You can then sum all of these together to get the total stock available for the item. So, the full expression is:

SUM([Related Transactions][Change in stock])

Hope this helps!

P.S. The error you were seeing is because "Transactions[Inventory ID]" returns the list of all Inventory ID values in the Transactions table, which you were then comparing to the single Row ID value. This is why the error indicates that you are comparing Text (the type of the Row ID) column with a List. What you probably wanted to do was ask "does the current row from my inventory match the Inventory ID from the transactions table", which would look like: [_THISROW].[Row ID] = [Inventory ID]. The _THISROW qualifier means "the current row in the Inventory table that I'm computing this expression for". However, because [Inventory ID] is a Ref column, this is the same definition as the REF_ROWS function used for the "Related Transactions" virtual column!

[JohnHar1234]

Following on from that, is there a way to get an item name from the item table via a link through the inventory table to show on the transactions page? 

[mikeas]

Definitely possible. You can chain dereferences together, so from the Transactions table you can follow the Inventory ID ref to the Inventory table, then follow the Item ID ref to the Item table, then finally get the Name column:

[Inventory ID].[Item ID].[Name]