create an supplier requirement filter

[arnaudv]

Hello everybody,
I create a new app called "supplier requirement" . My data is based to a spreadsheet. I would like to know to add en filter to prevent the user from chossing a supplier twice as first priority.

example: for the same part: supplier 1 "priority" and supplier 2 "secondary" but it's not possible to choose the priority option for supplier 2 because it's already been chosen, so it's impossible to have two priority suppliers for the same part.

It was possible to creathe the filter ?

Thank

[WillowMobileSys]

This can certainly be done but HOW depends on how you are listing your suppliers.

Suppliers listed as Enum Values

If your suppliers are listed as Enum values, then you will need to implement a Validation check in the Valid If property of [supplier 2] that verifies that the chosen value was NOT already used in [supplier 1].  For example,

[supplier 2] <> [supplier 1]

Then in the "Invalid value error' property, you would include a message of "Supplier already selected as Priority supplier". 

Suppliers from a Suppliers Table

If your suppliers are being selected from a Suppliers table then I assume your [supplier 1] and [supplier 2] columns are defined as Ref type. In the [supplier 2] Valid If property you would use an expression like this:

Select(Suppliers[supplier ID], [supplier ID] <> [_thisrow].[supplier 1])

Since you are allowing ONLY valid values, no messaging is needed in this case.

I hope this helps! 

[arnaudv]

Hello @WillowMobileSys,
Thank you for your feedback. i'll be testing this as soon as possible.

[arnaudv]

Hi @WillowMobileSys ,

I'm testing your solution, but it doesn't work.

J'ai essayé l'appformula suivante mais j'ai encore un message d'erreur:

IF(AND([Statut Fournisseur] = "Principal", COUNT(SELECT([Code article][Statut Fournisseur], [Code article] = [_THISROW].[Code article],[Statut Fournisseur] = "Principal")) > 1))

It's my table (supplier condition)

And i have 2 tables, one call "Parts", in french "Articles" and one "Suppliers" in french "Fournisseurs". I have a ref between the Parts table and "Supplier Condition"

why it doesn't work ?

I'm going crazy because I've been trying since this morning 😵 😡

[WillowMobileSys]

---

@arnaudv wrote:

IF(AND([Statut Fournisseur] = "Principal", COUNT(SELECT([Code article][Statut Fournisseur], [Code article] = [_THISROW].[Code article],[Statut Fournisseur] = "Principal")) > 1))

---

First, I thought by your original post, you are trying to filter the dropdown list choices?  In your screen shot it appears that you have placed this expression in the App Formula - which is not the correct place to affect dropdown lists.  If you are trying to filter the choices, then you want to place the expression in the Valid IF property of that column.

Is this the full expression?  If it is, it's not at all close to being correct.

An IF() requires 3 parts - Condition, if condition True expression and if condition False expression.  It appears you only have the Condition part.

Please describe in words what you are trying to accomplish with this expression.

[arnaudv]

@WillowMobileSys ;

Okay, here we go!
There are 3 tables
- the "articles" table
- the "suppliers" table
- the "supplier conditions" table

There are two relationships in the "conditions-suppliers" table
- article code in "valid if" / data valitidy -> article code from article table
- suppliers in "valid if" / data valitidy -> suppliers from the suppliers column

Supplier conditions are used to assign a supplier to an article.
For the same article, I must have only one "main" status for a supplier. If I want to buy the same article from another supplier, he must have the secondary status.

I thought my formula did this: it checks the number of times the same item is present, when it finds 2 identical items: it checks the name of the supplier, it must have a main supplier, if there are two suppliers with a main status there's a message for the user.

Finally, when choosing a supplier for an item, the user will be able to choose the primary status if it's a primary supplier, but a message will be sent to inform the user that the same item exists with another supplier, so the secondary status must be chosen.

Sorry for the long explanation and I hope I've made myself clearer

[WillowMobileSys]

---

@arnaudv wrote:

if there are two suppliers with a main status there's a message for the user.

---

So I understand that you are trying to prevent/detect having 2 "Principal" suppliers for an article in the "supplier condition" rows.  But this statement above sounds like you are reacting to a situation where there are 2 "Principal" suppliers already listed in the table for the same article.

How can this happen?  How are the "supplier conditions" rows inserted?

Shouldn't the app prevent the possibility of 2 "Principal" suppliers in the first place?

OR...is it that the "Principal" status can change, automatically, over time?

[arnaudv]

@WillowMobileSys ,

"the supplier conditions" lines are inserted by the user.
There is a need to buy a new part.
1- the parts is created in the "parts table"
2- the user creates a new line in the supplier conditions table, selects the newly created parts, chooses the supplier from the list and all the supplier's technical information (country, currency)...

but indeed, these are the two conditions that should apply:

Shouldn't the application first prevent the possibility of 2 "main" suppliers?

OR... can the "Principal" status change, automatically, over time?
Automatically no, because it requires user action to change the status.

[arnaudv · Accepted solution]

Hello @WillowMobileSys ,
By searching, I finally found a formula that works:
"IF(AND(OR([Statut Fournisseur] = "Principal",[Statut Fournisseur] = "Secondaire"),COUNT(FILTER("CONDITIONS_ART_FRS",AND( [Code article] = [_THISROW].[Code article],OR([Statut Fournisseur] = "Principal",[Statut Fournisseur] = "Secondaire") ))) >= 2),FALSE,TRUE)"
Have a nice day

[WillowMobileSys]

I somehow missed your response. It wasn't intentional...sorry for that. I'm glad you found a solution.

[arnaudv]

@WillowMobileSys , No problem !