You have to add the Forwarder column in the reference between this table and the Liners-table (with a lower number than the Liners-column)

Hallo,

I forgot to mention that the Forwarder is a Domain with elements  NL, BE, UK

Liners is a variant from a table 

Hey ​@Jheng ,

You could try to add a new link-table with a new primary key, then a column with the key of the Liner and a column with the country.
Then you can add the liner field as a lookup field.

Hope it helps.

Kind regards,

Dani

Hi Dani,
 

Sorry I'm not quite sure what you mean about the link-table.

I will try to explain my current table structure. I have a domain name Forwarder with elements  NL, BE, UK. This Forwarder field or column is under Order table . note that forwarder is not another table.

From order table there is a Liner field. The value for this Liner is pulled from a separate Company table, which contains a Country (Forwarder) column to indicate which forwarder each company belongs to.

I'm using a Liner variant to show only the relevant Liners, but I want this to be dynamic — meaning, the list of available Liners should update based on the selected Forwarder value in the Order.

The company table has the country (forwarder)  column.

How can I make the Liner field dynamically filter based on the selected Forwarder?

Now I have Order table with forwarder field  and liner field. 

What should the structure of the link table look like?
Will it automatically update the Liner field based on the selected Forwarder?

Thank you in advance

Hello Jheng,

If I understand correctly you should be able to include the Forwarder column in the reference to the Company table, then use that as a look-up. I think you are already partly there, but I can’t see how you have set up your reference columns based on the screenshot that you've provided. I'll try to explain what I think it should look like :) 

Basically you will have to create a reference like this:

• Source table: Company
• Target table: Order
• Reference columns: 
  • Source table’s column country mapped on the target table's column forwarder
  • Source table’s column liner mapped on the target table's column liner

When you do this, the possible Liners that you can choose from in the Order table will be based on the selected Forwarder in the Order table, by returning only those Liners from Company that are connected to the selected Forwarder. 

Hope this is understandable and that it helps.

Kind regards,

Renée 

Hi Renee,

Thank you for the effort on helping out.

You're right I'm working on this 2 table

• Source table: Company
• Target table: Order

But I'm not sure if I'm doing this right, looks like wrong on my end. It doesn't fulfil to dynamically update the Liners based on the selected Forwarder.

Here I screenshot the reference (details) from Source table: company

What you have instructed, I should create below

• Reference columns: 
  • Source table’s column country mapped on the target table's column forwarder
  • Source table’s column liner mapped on the target table's column liner

Under Company table column there are company_id, and dbsource and the rest

Reference (details) is setup as below

In the target table: Order (wp-order_header)

I apply the look up to company_id for the liners - there is a variant that's why i added the table variant

As for the dbsource column from the Order table I added the dbsource lookup. But this part looks wrong on the dropdown

The result of the dropdown becomes like this

Instead of the original 

Now I'm not sure which part I'm missing or what I might have set up incorrectly.

Thank you

Your problem is that you only have one reference column in your reference. Like I said, you need one reference with two reference columns. Like this:

This reference will allow the order to determine which country (forwarder) can be selected based on the provided company_id. 

Hi Reene,

I have these references from Company table

the red one I deleted, this is the one I created earlier which is not showing a correct value on the forwarder dropdown.

I have the reference column of:

So now i have 2 reference under company_liner_id, where im not sure if this correct 

then I select the new one i created Country(forwarder) = company_line_id 

But still this doesn't show correct value under liners