Filter Dropdown according to Supplier & Category

[LLD]

I have a master table containing Supplier Code, Part Category & Part Code

I have a parent table ("Supplier Invoice") containing Supplier Code & Supplier Invoice Number

I have a child table ("Supplier Invoice Details") containing Supplier Invoice Number, Part Category & Part Code.

New parts are first added to the Parts master table.

While adding the line item details of an invoice to the child table, I would like to - 

1. Filter down the list of Part Categories to only those categories that a Supplier has parts for
2. Filter down the list of Part Codes to those matching both Supplier Code AND Part Category

What formulas do I add to the Valid_If field?

[Suvrutt_Gurjar · Accepted solution]

You may want to mention

1. Through which column the master table and the parent table (Supplier Invoices) are referenced to each other.

2. Through which column the child table Invoices details and parent table Supplier invoices are referenced to each other.

[LLD · Accepted solution]

Hello!

The Master table and Parent table are referenced via Supplier Code

The Parent & Child tables are reference via Supplier Invoice Number

Hope this helps

[Suvrutt_Gurjar · Accepted solution]

Thanks. Is it correct to assume that in the master table there will be many records for a single supplier code  with corresponding Part Category and Part Code?

Similarly, there will be many combinations of one supplier code and  a supplier invoice number because each supplier can have multiple invoices?

[LLD · Accepted solution]

yes you are right....

Supplier Code - Part Code combination is unique (Category is an additional tag, but can repeat across a supplier or suppliers)

Similarly, Supplier Code - Supplier Invoice Number combination is also unique.

Hope this makes sense.

[Suvrutt_Gurjar · Accepted solution]

You may want to mention

1. Through which column the master table and the parent table (Supplier Invoices) are referenced to each other.

2. Through which column the child table Invoices details and parent table Supplier invoices are referenced to each other.

[LLD · Accepted solution]

Hello!

The Master table and Parent table are referenced via Supplier Code

The Parent & Child tables are reference via Supplier Invoice Number

Hope this helps

[Suvrutt_Gurjar · Accepted solution]

Thanks. Is it correct to assume that in the master table there will be many records for a single supplier code  with corresponding Part Category and Part Code?

Similarly, there will be many combinations of one supplier code and  a supplier invoice number because each supplier can have multiple invoices?

[LLD · Accepted solution]

yes you are right....

Supplier Code - Part Code combination is unique (Category is an additional tag, but can repeat across a supplier or suppliers)

Similarly, Supplier Code - Supplier Invoice Number combination is also unique.

Hope this makes sense.

[Suvrutt_Gurjar · Accepted solution]

Please try a valid_if for [Part Category]  in child table "Supplier Invoice Details" something like

SELECT( Master Table[Part Category], [Supplier Code]=[_THISROW].[Supplier Invoice Number].[Supplier Code])

For [Part Code] it can be

SELECT( Master Table[Part Code], [Supplier Code]=[_THISROW].[Supplier Invoice Number].[Supplier Code])