How can I filter values from a ref table in the user form?

[JulioHM]

I have two tables:

• Product
• Category

The Product table has a ref column to Category, and each Product is allowed to be associated to one specific category.

Category has a Yes/No column called "available". I would like to disable some categories so that the user is no longer able to select them in the Product form view. I can't simply delete values from the Category table, since they are already in use by Product records that were registered in the past. That history needs to be available, but future products can no longer be assigned a category that is no longer marked as available.

How can I filter the Product form view to show only Category records that match a specific filter, let's say "[Category].[Available] = True" ?

That would be really helpful. 

Thanks a lot.

[WillowMobileSys · Accepted solution]

In the Category Ref column of your Product table. you would assign an expression in the Valid If property like this:

 FILTER("Category", [Available] = TRUE)

This should provide you the list of available categories in a dropdown list 

[JulioHM]

This works great! Thanks.

The restrictions mentioned by AleksiAlkio are valid points, though. In my case, my users can live with the fact that once a category is deleted, they will be forced to pick a new category for that product when it's updated.

Best regards!

[AleksiAlkio]

One thing to remember.. if it's possible that someone edit the record in the future and that category is not available any more, it doesn't allow you to save the edit. For that reason, you need to use something like..

OR(
FILTER("Category", [Available] = TRUE),
LIST([_THISROW_BEFORE].[Category])
)

[JulioHM]

I wasn't able to make this filter work for some reason. But I can manage with the restriction you mentioned. Thank you.

[WillowMobileSys]

Since you are using an [Available] flag, you may not need to worry about the extra portion of the filter. 

Adopt a policy of never deleting old category records unless absolutely certain it is not being used by any data in the app.  This might mean disabling the Delete action or at the very least insert very strict criteria to allow deletion - e.g. [Related....] column is empty.

Also, make sure you are not using the Category Name as the key.  If the name changes you will run it issues.  Instead use a dedicated key column with a random value and you will never run into a problem.