0

VLOOKUP formula between 2 tables

Hello,

I'm looking for a vlookup formula to get information stored in another table.

- Table 1 with EAN Code (for example 5400101123456) and Product name (for example spaghetti).

- Table 2 with EAN Code (same as Table 1 - 5400101123456) and a formula field.

Which formula can i put in the formula field (into table 2) to get "spaghetti" (from table 1) as result?

Thanks in advance for your help and have a nice day,

David W.

13 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    This works...

     

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

    • David_WILLOX
    • 4 yrs ago
    • Reported - view

    Thanks for your help Sean!

    Can you please clarify for the 'EAN Code': from Table 1 or from Table 2

    Also, if from a different table, how can i right the correct syntax?

    In my case:

    Table 1 is Fournisseur

    Table 2 is SensoPro

    EAN code table 1 is IDSAP

    EAN code table 2 is IDFOURN

    The result needed is IDGESTPRO in table 1

    Thanks for your help and have a nice day,

    David W.

    • Sean
    • 4 yrs ago
    • Reported - view

    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

    • Senso
    • 4 yrs ago
    • Reported - view

    Thanks Sean, the second formula works.

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

    David W.

    • LoResah
    • 3 yrs ago
    • Reported - view

    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

    • Fred
    • 3 yrs ago
    • Reported - view

    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?

    • LoResah
    • 3 yrs ago
    • Reported - view

    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 ?

    • LoResah
    • 3 yrs ago
    • Reported - view

    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.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

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

    • LoResah
    • 3 yrs ago
    • Reported - view

    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.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    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.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    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)

    • LoResah
    • 3 yrs ago
    • Reported - view

    Thank you!

Content aside

  • 3 yrs agoLast active
  • 13Replies
  • 1805Views