How to filter linked data [SOLVED]


I buy pallets, which each have several items on them, so I have a table for each LOT (pallet), and a table of items. I have a linked column in the LOTS table, which pulls the names of each ITEM in that lot. I would like to have another column that pulls only those ITEMS which has not been marked SOLD.



Add in a Lookup field in the LOTS table for ITEM STATUS
Where ITEM STATUS is the field for stating whether the product is sold or unsold.

Create a view called “UNSOLD ITEMS” in your LOT table which has a filter i.e “ITEM STATUS = UNSOLD”.
This view will consist of only those items that are marked as unsold


Here is what it produces when I do the lookup field, just a list in a new column. I want the ITEMS column to only pull UNSOLD items. Any ideas?


Anyone?? I’d love help on this!


@Elias_Gomez_Sainz I would love your feedback!


You can’t do that. You have to see that information from the Items table.

Let me understand what you do: you buy Lots with Items, and then you sell the Items independantly? And you want to see which of them are still available, isn’t it? Why do you want to see that in the Lots table?


When I’m looking at the LOTS table, it is usually to get an idea of how profitable the lot as a whole is at that point, or how far we are from breaking even or hitting our income goals. When I see that a lot hasn’t lived up to expectations, I’d like to be able to quickly see how many items are still available without switching to another table, and then to another view, then coming back and hoping I remember the lot number during all of that.


You could do this:

  1. Create a Checkbox field in the Items table, called Sold or something like that. Mark it when you sell an Item.
  2. Create a Formula field like IF(Sold,0,1), called Pending for instance.
  3. Create a Rollup field in the Lots table, called Unsold. Select the Items table, Pending field, use SUM as the function.

You cold also do that:

  1. Create a View in the Items table
  2. Filter out the Items that has been Sold.
  3. Group By the Lot.

You now have in screen the Lots with pending Items to sell, but you also have the information about the Items.

Tip: you can use Shift + Space to view a ‘pop up’ with the linked Items in the Lots table.


I tried this, and I’m not clear on where I’m going wrong, but this just adds up the # of unsold items, without listing them for me.


I told you, you can’t.

And you said this.


Ahh…OK, I see why that was confusing. Thank you for the solution, which did answer what I [didn’t realize I] asked!


No prob! Maybe other advanced users like @W_Vann_Hall or @Jeremy_Oglesby have any tricky suggestion for you :sweat_smile:


Building off of @Elias_Gomez_Sainz’s solution:
Replace the formula in the “Pending” field with IF(Sold,BLANK(),{Item Name})
Replace the formula in the Rollup field with ARRAYCOMPACT

What this does is produce a column on your Items table with the names of items that haven’t yet been sold. The Lots table then rolls up these names, and discards the BLANK() entries (which correspond to sold items).

The formatting might be a little bit wonky (I’m not sure if it’ll space things out correctly), but it should get you what you need.


This worked GREAT!

Thank you very much. :slight_smile:

closed #15

This topic was automatically closed after 4 hours. New replies are no longer allowed.