VALID IF already present in another table

Renaud1 · 12-10-2021 04:14 AM

Renaud

Hello,
I want to set up serial number monitoring between my purchases and my sales
I have a BDD ENLEVEMENTS table, where I fill in the serial numbers of the products entering my stock in the column [PRODUIT NUMERO DE SERIE UE]
I would like not to be able to enter in my second table, which is called BDD AFFAIRES DETAIL, in the [PRODUIT NUMERO DE SERIE UE] column (same name as in the BDD ENLEVEMENTS table), only serial numbers already present in BDD ENLEVEMENTS. [PRODUIT NUMERO DE SERIE UE]
I tried with LOOKUP to no avail.
I’m working on this formula in Valid IF, but it doesn’t work:

ISBLANK(
FILTER(
“BDD ENLEVEMENTS”,
AND(
ISNOTBLANK([PRODUIT NUMERO DE SERIE UE]),
([PRODUIT NUMERO DE SERIE UE] = [_THISROW].[PRODUIT NUMERO DE SERIE UE]) )

LIST([_THISROW])
)
)

Can anyone help me

Thank you

Renaud

Aurelien

Bonjour Renaud

et bienvenue dans la communauté

Not sure to undertand this part:

My understanding is:
“being able to select, in BDD AFFAIRES DETAIL, only [PRODUIT NUMERO DE SERIE UE] that already are in BDD ENLEVEMENTS”

Do you have a 3rd table with all your “Produits” ?
Can you share a screenshot of your table structures BDD AFFAIRES DETAIL and BDD ENLEVEMENTS ?

Renaud1

Hello Aurelien,

Thank you for your time

I want to be able to fill in the table BDD AFFAIRES DETAIL in the column PRODUIT NUMERO DE SERIE UE, only serial numbers present in the table BDD DETAILS in the column PRODUIT NUMERO DE SERIE UE

In the case below, I should not be able to enter in BDD ENLEVEMENTS column PRODUIT NUMERO DE SERIE UE only the value “123456789” and have an error message for any other value

Capture BDD ENLEVEMENTS (PRODUIT NUMERO ENLEVEMENT is a key)

CAPTURE BDD AFFAIRES DETAIL (CODE AFFAIRE DETAIL is a key)

I hope it’s clearer like that

Aurelien

Hi @Renaud

I think there is a barrier language here.

In French, that would give:
Je souhaite pouvoir remplir le tableau BDD AFFAIRES DETAIL dans la colonne PRODUIT NUMERO DE SERIE UE, uniquement les numéros de série présents dans le tableau BDD DETAILS dans la colonne PRODUIT NUMERO DE SERIE UE
(==> did you duplicate the table name ?)

Dans le cas ci-dessous, je ne devrais pas pouvoir saisir dans la colonne BDD ENLEVEMENTS PRODUIT NUMERO DE SERIE UE uniquement la valeur « 123456789 » et avoir un message d’erreur pour toute autre valeur
(==> double-négation ? Veux-tu dire “ne pas pouvoir saisir autre chose que 123456789” ?)

==> My understanding: in DETAIL, you don’t want to select anything else than 123456789 because this is the value that has been entered in ENLEVEMENTS.
Is it correct ?

Renaud1

==> My understanding: in DETAIL, you don’t want to select anything else than 123456789 because this is the value that has been entered in ENLEVEMENTS.
Is it correct ?

Exactly !

Aurelien

OK

Please try, in the Valid_If expression of the column [PRODUIT NUMERO DE SERIE UE] from the table DETAIL:

ENLEVEMENTS[PRODUIT NUMERO DE SERIE UE]

Renaud1

Hello Aurelien,

I tried your solution, but it didn’t work.

I found this formula and it works now

AND(
ISNOTBLANK([_THIS]),
IN([_THIS],BDD ENLEVEMENTS[PRODUIT NUMERO DE SERIE UE]))

Now, I can enter in
BDD AFFAIRES DETAIL. [PRODUIT NUMERO DE SERIE UE]
only serial numbers present in
BDD ENLEVEMENTS[PRODUIT NUMERO DE SERIE UE])

Thank you for studying my problem

Renaud

Aurelien

Great you make it.
I was not aware you wanted it to make the field mandatory as well
The require option would have done the job as well for this part:
ISNOTBLANK([_THIS])