Nested if or formulamap

Deal Codans, I have a set of columns (call them a, b, c, d, e). I’d like to evaluate each of the five individually and if the value for each is less than a threshold, then the result in column f will be a text object that contains specific text related to each of columns a, b, c, d, e). In other words, if a and d are both less than the threshold, column e will contain the concatenation of the unique value for column a plus the unique value for column d).

Each of the columns a, b, c, d, e are survey answers. If the answer is below the threshold, I want to return a recommendation unique to each answer. So the recommendation for a is different than the recommendation for d, but I want column e to show both recommendations (as a bulleted list).

It seems like formulamap and/or nested if statements would be the way to go. I can’t see how switch or switchif would work. I’m completely stuck.

Any guidance?

Thanks so much!
Jeb

Hi @Jeb_Dasteel

Can you share a dummy doc?

Hi Jeb,

I don’t think nested If() is going to work - logic will stop as soon as the first True occurs. You will need sequential if() statements.

I also don’t see how ForEach() is going to allow you to step through columns, it is designed to step through rows.

The best would be to transpose the feedback from your survey from columns into rows, that will make all of your subsequent logic much easier. You can then use Min() to determine the lowest response, and use that to populate a column from your suggestion tables. You can then filter on non-blank rows to prepare a report.

Regards
Piet

Hi Cristian. Here’s a link to the doc. If you go to the All Respondents page and look at the top table - scroll to the right and you’ll see the "Leadership Comments: Low column as an example. This is where I’m trying to apply the logic I described. The actual commentary resides in the helper table page, at the top under Commentary. The first table called Leadership: Low is the one that coincides with the column I mention above.

I tried at one point to use a formula table to transpose rows and columns, but that seemed counterproductive. And, in any event, I’m way beyond that now - in that most everything works.

Really appreciate any guidance you may have.

Hello Piet. You’ll see my reply to Cristian, above. I tried to transpose but that seemed like more of a pain than it’s work - and I managed to get pretty much everything else to work the way I wanted - though it’s maybe not the most elegant solution in the world. Now I’m just stuck on this issue… Thanks so much.

Jeb

Hi @Jeb_Dasteel

I had a look at the doc.

As @Piet_Strydom correctly pointed, a simple SwitchIf would stop after first true is found.

I played with your use case and put something together in the doc below.

It would have been a bit of a pain to build things based on your columns name as their names is long so I build the tables with column names such as Column 1, Column 2 etc. but you get the point.

As having to press on a button, once the JotForm data get synced, you can have a daily automation that does this or you will see it visually as you go through the data as a filtered view.

For a better quality of life I would recommend you to switch, have the description containing the whole text of what the column represents and a shorter visual name as this will be used in the formula editor.

Let me know if it solves your use case.

1 Like

Hi Cristian. MANY thanks for this. I think it will work. I need to go through it completely to make sure I fully understand and will then take a transposed comments table (single table, not the multiple I have at the moment), and test it out.

Again, thank you for all the terrific assistance. I’ll circle back to you in the next couple of days to confirm that I’m all set.

All the best,
Jeb

1 Like

Hi @Jeb_Dasteel

If you want to consolidate your helper tables into a single one from as you have now, just make one table with all 20 columns with only 2 rows (low & high).

I edited my doc and added columns for governance part and also all helper tables are consolidated in one big table.

All you have is to add the other two sections (organizational, technology, edit the transpose button formula so that the sequence is up to 20 columns not 10 as of now, duplicate the canvas columns for comments and adapt the formulas for the target (organizational and technology).

Have fun and let me if you need guidance.

1 Like

That’s amazing, Cristian. Thank you!