How to filter linked data [SOLVED]

[Angie_Belle]

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.

Ideas?

[Andrew_Johnson1]

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

[Angie_Belle]

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?

[Angie_Belle]

Anyone?? I’d love help on this!

[Angie_Belle]

@Elias_Gomez_Sainz I would love your feedback!

[Elias_Gomez_Sai]

Angie_Belle:

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?

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?

[Angie_Belle]

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.

[Elias_Gomez_Sai]

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.

[Angie_Belle]

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.

[Elias_Gomez_Sai]

Elias_Gomez_Sainz:

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?

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

I told you, you can’t.

Angie_Belle:

I’d like to be able to quickly see how many items are still available without switching to another table

And you said this.

[Angie_Belle]

Angie_Belle:

how many

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