This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Other questions
- Re: Lookup two fields simultaneously: how to turn ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
1989
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15, 2018 03:13 AM

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 9

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15, 2018 05:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15, 2018 07:44 AM

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

G

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 15, 2018 10:24 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16, 2018 01:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 16, 2018 02:53 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 17, 2018 11:32 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 18, 2018 12:26 AM

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 12, 2021 10:00 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 13, 2021 09:34 AM

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