Creating an INDEX MATCH type formula generation from connected Records

Solutions How to build
#1

can I use the IF formula to create an INDEX MATCH type formula?

If not, is there another function I can use to generate an INDEX MATCH result from connected records?

What I want to have is a value (the Priority Ranking number) automatically generated if the related column has a specified single selection choice (ie my selection is a Priority Descriptor such as CRITICAL AND URGENT; if that is the selection, I want the next column PRIORITY RANKING to generate the figure 1.

NB: The active Table is a Task table; I am using the single selection field as a related Record from the Priority Descriptor Table to give me the drop down values and the Priority Ranking is aligned with the Descriptors on the Priority Descriptor Table.

The flow is Task Name (fixed field in the Task Table)/Priority Descriptor (single selection field from the related record) / Priority Ranking (automatically generated based on the drop down selection)

#2

@fernando.nakandakari can you help him?

#3

Hey @mcmdennis

You could nest IFs in the formula to create what you need, but right now connected values are not fully compatible with the formula field, so it’s not possible yet.

I do believe we will have this updated in the next week or so, though :slight_smile:

#4

i hope so

there are a number of other functions you get used to applying with Sheets that make them useful and easy to use (even with their limitations and complexity once you try to scale)

but chamging from spreadsheets to relational databases shouldn’t mean losing any of the features that make Sheets so useful

#5

right now connected values are not fully compatible with the formula field, so it’s not possible yet.
I do believe we will have this updated in the next week or so, though

Hi Fernando, I suppose that is still not possible, is it? Because a field is either a Formula or the field is a Connected field.

You can´t have a Connected Field with a formula, which would be the required solution here. (similar to AppSheet)

As for cascading IFs, I can do that by checking values inserted in the current table, but not based on those values, search for a corresponding value at a related table and use it.

For example… in my Risks table, the user can select probability and impact of the risk
Both come from related tables and they have another column with a value. Thus

Probability Table
1, Very Rare
2, Rare
3, Occasional
etc

Impact Table
1, Very Low
2, Low
3, Medium
etc

Then I have another column that calculates impact based on those selections
{{riscosprob.num}}*{{riscosimpact.num}}

Notice that the related records WERE selected manually, and I am only pulling other columns of that selected related record.

Once I get the calculation, I need to get, from the NUMBER, the text on the Risk Degree table. Now, THAT is the part it’s not possible. The number is a calculation and thus it is not a related record.
And I can´t search a related record based on it. Only if I manually selected the number from the calculation, in another column…

Thus, I ended up having to use a calculated field that returns TEXT (not related records)
IF({{calcrisk}}<1,“Inexistente”,IF({{calcrisk}}<3,“Muito Baixo”,IF({{calcrisk}}<5,“Baixo”,IF({{calcrisk}}<12,“Moderado”,IF({{calcrisk}}<20,“Elevado”,“Extremo”)))))