Skip to main content

Lookup two fields simultaneously: how to turn a formula result into a field

  • July 15, 2018
  • 9 replies
  • 103 views
G
  • Gonz
  • Participating Frequently

I am trying to do a look up based on two fields. Here is the situation:

  • my first field is called Severity and it is the primary field of a “severity definition” table, which contains 5 records
  • my second field is called Probability and it is the primary field of a “probability definition” table, which also contains 5 records
  • I have created a junction table, called “Risk acceptability” which is built with the two above-mentioned fields; the primary field is built as a formula (Severity&"-"&Probability). On that table is the field “Acceptability” which is the one I will try to lookup. See below

    -Finally, my FMEA table will allow for any new record to pick a Severity and a Probability: I would like to automatically have the “Acceptability” appear. So I have created the same formula in that table (Severity&"-"&Probability) to lookup to the table “Risk acceptability”. Problem is: I cannot create a Lookup based on the result of the formula.

So my question is: how can I use my formula result for a lookup ?
Or is there any other way ?
Thanks for your advice
G

    9 replies

    E
    Forum|alt.badge.img+17

    I think you have to create 2 Lookups first and then create the third field for the Formula.

    G
    • Gonz
    • Author
    • Participating Frequently
    • July 15, 2018

    I think you have to create 2 Lookups first and then create the third field for the Formula.


    Dear Elias, I am not sure I understand how you would do that…
    G

    E
    Forum|alt.badge.img+17

    I was thinking on something like this:

    But you could do it directly checking the Linked Records names instead of the Lookups.

    Anyway, you won’t get an Accectability from the Risk Acep. table, you will get it from the Formula in FMEA table.

    G
    • Gonz
    • Author
    • Participating Frequently
    • July 16, 2018

    I was thinking on something like this:

    But you could do it directly checking the Linked Records names instead of the Lookups.

    Anyway, you won’t get an Accectability from the Risk Acep. table, you will get it from the Formula in FMEA table.


    Ok, got it.
    So you need to hard code the acceptability criteria in a formula… Tough: it means a long and nasty formula.But I get it
    So there is no way to use the result of a formula to lookup in a table ?
    G

    E
    Forum|alt.badge.img+17

    Ok, got it.
    So you need to hard code the acceptability criteria in a formula… Tough: it means a long and nasty formula.But I get it
    So there is no way to use the result of a formula to lookup in a table ?
    G


    I don’t know what you mean with that.

    You can create a Linked Record field to Risk Acceptability and choose ONE record (Critical-Frequent for instance), and then Lookup the Acceptability field to the FMEA table. But if you want to choose the Severity in one Linked Record field and Probability in another field, you have to make a formula that checks those 2 records fields.

    G

    Agreeing with what @Elias_Gomez_Sainz said here, I would also recommend thinking of this problem as a 2x2 matrix. (If you have a pro account, you can actually visualize this in the Matrix Block - pictured below).

    Essentially, you want everything in the Upper Right corner to have an “Acceptable” tag, everything along the center diagonal (top left to bottom right) to have the "ALARP tag, and everything in the Bottom Left to have the “Unacceptable” tag, right?

    You can accomplish this with the following:

    SEVERITY VALUE:
    IF(Severity=“Negligible”,1,IF(Severity=“Minor”,2,IF(Severity=“Serious”,3,IF(Severity=“Major”,4,IF(Severity=“Critical”,5,0)))))

    PROBABILITY VALUE:
    IF(Probability=“Highly Infrequent”,1,IF(Probability=“Infrequent”,2,IF(Probability=“Probable”,3,IF(Probability=“Frequent”,4,IF(Probability=“Highly Frequent”,5,0)))))

    TOTAL VALUE:
    SUM({Severity Value},{Probability Value})

    ACCEPTABILITY:
    IF({Total Value}<=3,“Acceptable”,IF({Total Value}<=8,“ALARP”,IF({Total Value}<=10,“Unacceptable”,BLANK())))

    To save a field, you could eliminate the TOTAL VALUE and do the sumation in your ACCEPTABILITY formula, but I thought breaking this out might make it easier to visualize.

    A
    E
    G
    • Gonz
    • Author
    • Participating Frequently
    • July 18, 2018

    Agreeing with what @Elias_Gomez_Sainz said here, I would also recommend thinking of this problem as a 2x2 matrix. (If you have a pro account, you can actually visualize this in the Matrix Block - pictured below).

    Essentially, you want everything in the Upper Right corner to have an “Acceptable” tag, everything along the center diagonal (top left to bottom right) to have the "ALARP tag, and everything in the Bottom Left to have the “Unacceptable” tag, right?

    You can accomplish this with the following:

    SEVERITY VALUE:
    IF(Severity=“Negligible”,1,IF(Severity=“Minor”,2,IF(Severity=“Serious”,3,IF(Severity=“Major”,4,IF(Severity=“Critical”,5,0)))))

    PROBABILITY VALUE:
    IF(Probability=“Highly Infrequent”,1,IF(Probability=“Infrequent”,2,IF(Probability=“Probable”,3,IF(Probability=“Frequent”,4,IF(Probability=“Highly Frequent”,5,0)))))

    TOTAL VALUE:
    SUM({Severity Value},{Probability Value})

    ACCEPTABILITY:
    IF({Total Value}<=3,“Acceptable”,IF({Total Value}<=8,“ALARP”,IF({Total Value}<=10,“Unacceptable”,BLANK())))

    To save a field, you could eliminate the TOTAL VALUE and do the sumation in your ACCEPTABILITY formula, but I thought breaking this out might make it easier to visualize.


    Thanks Gareth.
    Indeed, I actually started from this 2x2 matrix, then broke it down into a base format to be more aligned with airbase approach. That’s why I wanted to work from there, but formulas are fine, they just look a bit too “excelish” to my liking !
    Cheers
    G

    G
    A

    @Gareth_Pronovost hey garett, i watch your youtube channel alot and im trying to get zapier to look up a record based on two fields then add data to the record it finds. i used the and search formula in zapier but its not finding it. I dont have a matching formula in airtable just the zapier. do you know the issue?

    G

    @Gareth_Pronovost hey garett, i watch your youtube channel alot and im trying to get zapier to look up a record based on two fields then add data to the record it finds. i used the and search formula in zapier but its not finding it. I dont have a matching formula in airtable just the zapier. do you know the issue?


    Hi Anthony,

    Sorry, I don’t know what’s causing this issue without looking at your specific database and automation. We do offer hourly consulting on our website if you’d like to have one of our experts take a look - you can sign up for that here.

    Thanks,
    Gareth

    Terms & ConditionsAccessibility statement