VLOOKUP formula between 2 tables

This works...

(select Table1 where 'EAN Code' = 'EAN Code').'Product Name'

Hi David, The following should work and, according to your description, would go into the Formula field in SensoPro...

(select Fournisseur where IDSAP = IDFOURN).IDGESTPRO

if not, try this...

let myEAN = this.IDFOURN;

(select Fournisseur where IDSAP = myEAN).IDGESTPRO

Thanks Sean, the second formula works.

I only had to write let myEAN := this.IDFOURN...

David W.

Hi, I need the same formula but the original field is a multiple choice field and the formula doesn't work. In the multiple choice field I have names of employes ('Employé') and in the 2nd table I have 2 columns their name ('Prénom Nom') and their emails (Email). 

I tried : 

let Name := 'Employé';
(select Collaborateurs where 'Prénom Nom' like Name).Email

But it doesn't work. If I replace 'Employé' with the litteral expression of the name like "Jean DUPONT" it works. What is the problem ?

Many thanks

you can try changing the first line to:

let Name := chosen(Employé); <--since it is a multiple choice field (or even a choice field) the chosen command returns the text of the field.

the problem is this is a multiple choice field so if someone selects two choices then the code breaks again because you don't have an employee with two names separated by a comma. you can see this if you create a new formula field and put

chosen(Employé)

and you can see what happens when you make your multiple choice selection.

Do you need the email of employees selected in one field?

Hi,

Thanks but this is not working.

First of all, even when I have only 1 value in the first table 'Employé', the formula is not displaying a result. It's a dynamic multiple choice field actually.

And indeed the multiple selection would I have been my second question.

chosen() is not working for dynamic multiple choice.

Can you help ?

To be more precise : 

1. With a multiple choice source (not dynamic) : 

let Name := text(Employé);
(select Collaborateurs where 'Prénom Nom' = Name).Email

This formula works but it doesn't work if there is more than 1 choice in the Employé filed. As I need to convert with text(), the chosen() doesn't work because chose(text()) is not valid and text(chosen()) is not solving the problem.

2. With a dynamic multiple choice source : 

let Name := text(Employé);
(select Collaborateurs where 'Prénom Nom' = Name).Email

Nothing work : let Name := 'Employé' or let Name := text(Employé) or chosen(). Nothing is working.

How is your dynamic multiple choice field filled? I mean, what is the formula in "Dynamic value"?

Table 1 is 'Collaborateurs' and the formula in Dynamic Value is : 

(select Collaborateurs where Poste like "Acheteur") order by 'Prénom Nom'

And in Dynamic Value Name I have : 'Prénom Nom'

I read that chosen() doesn't work for dynamic multiple choice.

Indeed, "chosen()" does not work for dynamic multiple choice (DMC) fields. If you think about it, it does somewhat make sense: a DMC field is "populated" by submitting a function that returns an array of records, usually all or some records from a table. Selecting elements of the DMC field then actually makes a selection among those records. The "numbers()" function returns an array containing the numeric values of the Ids of the selected records. To use the "chosen()" function on the DMC field, it would then be necessary to find a way to specify which field of the selected records would provide the values to be returned. One could argue that the value computed by the function submitted as the "Dynamic value name" could be used, but it is not so.

Since "chosen()" is not available, one must use the Ids of the records returned by "numbers()" to explicitly select the needed values from the table providing the dynamic values.

In the case of your database, the formula below should return a comma-separated string with the values of the "Email" field from the selected "Collaborateurs".

 
let zSelected := "," + text(numbers('Prénom Nom')) + ",";

concat((select Collaborateurs where contains(zSelected, "," + Id + ",")).Email)

 
The first line builds a string where the values of the Ids of the selected records are surrounded by commas. The second line gets the values of the "Email" field from the selected records, and concats them into a string. The "surrounded by commas" business is needed since the Ids are folded into a string, and one must avoid that looking for, by example "7", would return false positive for "17", "27", "70", etc. It would not be needed if Ninox would provide a function to check if an array contains a given value, without converting everything to strings.

Mmmmm... Seems I did not read your description correctly. The formula shoud actually be:

let zSelected := "," + text(numbers('Employé')) + ",";

concat((select Collaborateurs where contains(zSelected, "," + Id + ",")).Email)

Thank you!