Filtering on a form

[brad]

Hi community, I have been going around in circles trying to get a really simple filter to work.
I have Suppliers Table, Orders Table, Order Detail Table, Products Table.
I have a form to add products to the order detail. However the products I want displayed available for the Order Detail should only be the products from the Supplier that was selected when creating the Order Table record. I cannot get this to work. Any tips or dark arts I need to know about?
I have the following connections:

1. Products Table: 1 Product connects to 1 Supplier
2. Orders Table: 1 Order connects to 1 Supplier
3. Order Detail Table: 1 Order Detail connects to 1 Product
4. Order Detail Table: 1 Order Detail connects to 1 Supplier
5. Order Detail Table: 1 Order Detail connects to 1 Order

I have tried filtering on the Product filed in the Add Order Detail form. The filter option I have tried is: Supplier - is connected with current record

Thanks in advance. Cheers

[tim.young]

Hey @brad,

Sorry to hear this is giving you trouble. I will test this scenario to see if this is unintended behavior but in the meantime, check out this post from @moe regarding SHOW vs FILTER, he describes the differences very well.

"show" vs "filter" in the form builder

The general difference is the filtering on filters tab is based on values in the connected (parent/remote) table, while the show is based on filtering data based on either the form or filter by related records. You can see very detailed docs here with the 3 different types of filtering: But I’ll try and clarify here as well. Filter Records: In your Add Activity table if you wanted to only show Projects that have a Status (field) set to “Active” you would do that in the Filters tab. Since …

Have you tried using the SHOW function in the form’s field settings?

[brad]

Hi @tim.young I have made a video to explain my challenge with the Supplier & filtering. Hopefully you can find the time to watch it & give me some feedback. Here is the link https://www.loom.com/share/c40efa51b1494b6ea68df6d47f6a0c7d

Basically I have a supplier field populated in the Order record, when we are adding products to the Order Detail we have to select the supplier again. This is not ideal as the user can make errors here & end up combining products from multiple suppliers on 1 order. I would like the value of the Supplier from the Order record to automatically populate the Supplier field in the Add Order Detail form. Then I can filter the product choice by that Supplier value.

Cheers Brad

[brad]

Hi @tim.young, I use the Show option to filter the products by the selected supplier in the add order detail form. It works great, but as I said in my earlier post I don’t want the users to have to select the supplier again. The supplier value is already set when they choose the Supplier then the Pantry List to create the Order record. Cheers

[tim.young]

Hey @brad,

You can use Record Rules within the form to set the connected supplier on form submit.

[brad]

Hi @tim.young, thanks I have used them for Dates & logged in user Venue fields. But I am wanting to filter the products by the supplier & adding a product form needs the supplier populated before the submission. Am I missing something?

[tim.young]

Oh duh, sorry

I don’t think it’s possible right now but I will keep thinking about it.