Filtering on a form

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

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.

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

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

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

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

Hey @brad,

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

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?

Oh duh, sorry :man_facepalming:

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

1 Like