First off, link: https://coda.io/d/FC-Asset-Tracking_d_4LZMlCxvR/_surD4
Essentially I have an inventory system. I have one table with supply chain receiving. This means I receive multiple of the same items (ie; multiple shipments of the same type of batteries)
The second table is my ‘Total Inventory’. This displays the on-hand quantity of an item (how many batteries I have, given all the shipments I have received)
Essentially it is a sum of all my shipments, filtered by a certain item type. I just can’t get the filter to properly work. Trying to make this a button but if you have a different method, please let me know!!
Let me know if I can clarify anything! Eventually I’ll want to subtract shipments from total inventory, given the product shipped, but that is a separate and likely more advanced topic. Also my products have multiple items, and I want to serialize some of them. This is more complex than a simple e-commerce store with 1 or 2 one item products.
Any help on this? Simply trying to add only specific items with a filter, essentially. On the ‘Inventory and Receiving’ page
Hi @Jon_Kirchman,
It would be helpful if you could be more precise. Which table (ie. the table name), which section is it in, which columns (from which tables) are you wanting to sum.
If you can point directly to where the issue is, that would be great.
Cheers
EDIT: check edit below
Sure, I’ve brought all the tables to the Inventory and Receiving Page.
I’m trying to sum all the receiving rows in the ‘Receiving’ Table, using the ‘Quantity Received’ value. I’m displaying the sum at the ‘Units Received’ value on the ‘Inventory’ table. I’ll have multiple rows that need to be summed for a specific item (ie; I’ve received multiple battery shipments) that I’d like to add up to the total ‘Units Received’.
I’ve got it working using this formula in the ‘Inventory Units Received’ column: Receiving.Filter([Unit Received]=thisRow.[Full Description]).[Quantity Received].sum()
But, for some reason it only works when both the ‘Full Description’ and ‘Unit Received’ are text values, instead of Lookup values. So I’ve made a third table ‘Units’ and am trying to link both ‘Inventory’ and ‘Receiving’ to these items…but it’s not working because they are still lookup. (Initially I had ‘Inventory’ as the Text values with the ‘Inventory’ and ‘Units’ table combined, and ‘Receiving’ as the Lookup values)
Here are 2 photos, same exact everything, except in one photo both columns are using Lookup, and in the other photo they are using Text. You can see that the ‘Units Received’ in the ‘Inventory’ table changes.
Thank you for the response and for telling me to explain it better, haha. Hopefully this makes more sense!
EDIT: I got it working with 3 tables, now I’m trying to get it working with only the ‘Inventory’ and ‘Receiving’ Tables (‘Units’ table is redundant in my opinion.)
1 Like
Hi @Jon_Kirchman,
OK. Thanks for the extra info. Please see doc below.
I added a new column to the Inventory table and called it “Unit Name”, made it the “Display Column” and then deleted the “Full Description” Column.
The I changed the “Unit Received” column in the Receiving table to lookup the Inventory table.
Then changed the formula of [Inventory.Units Received] to - Receiving.Filter([Unit Received]=thisRow).[Quantity Received].Sum()
Is that what you are after?
(Note, I have set this up in my ‘Samples’ doc because it makes it easier to keep track of this kind of thing rather than having multiple copies of other people’s docs)
Let me know if I’ve got your requirements wrong.
Rohan
2 Likes
Thank you! Yes this is what I was after.
Interesting that the main formula change was in the Filter, to change Filter([Unit Received]=thisRow.Unit Name) to Filter([Unit Received]=thisRow). Why did getting rid of the Unit Name solve this? (Just trying to understand)
I greatly appreciate you taking the time to help me out here!
I think basically it is because each row in a table is a representation of an “object” (eg. “MainPCB-1F” is an object) that has properties (ie. the columns, like “unit type” and “purchase info”).
The [Unit Received] column of the [Receiving] table is a reference to a row in the Inventory table - ie a [Unit] - each of which is an object.
In the formula, if you write [thisRow.Unit Name], it is a reference to the [Unit Name] column which is a text value, whereas just writing [thisRow] is a reference to the object. So [Unit Received]=thisRow.Unit Name is comparing an object to a text value, and so they aren’t equal/the same.
Hopefully that makes sense, but someone from @Coda would probably explain it better.
1 Like
Thank you very much. That makes sense, I need to reference the object itself instead of a text value.
You’re welcome.
Another important one is when comparing something with the value of a select list - you need to use the Matches formula! Before I discovered that I was always having to use .ToText on both sides of the comparison (eg. Column.ToText = SelectList.ToText)
