Conditional Rollup or Count question

Question

So, my first question on this forum  :smiling_face_with_halo: 
I have a base with 2 tables: Table 1 deals with applications for following trainings. Table 2 has an overview of all personal details.
Table 1
John | training 1 | formal (training) | man (lookup field from table 2)
Sara | training 2 | formal | woman
Eddy | training 3 | informal | man
John | training 4 | informal | man
Table 2
John | man | training 1, training 4
Sara | woman | training 2
Eddy | man | training 3
Question: I want to know if (so yes/no or 1/0) John, Sara and Eddy followed (a) any formal trainings and (b) any informal trainings. No idea how to get this done.
Eg: 2 extra columns: “formal” and “informal” that states “yes” or “no” of “1” or “0”. I don’t need to know how many formal/informal trainings they did, just if they did any at all, but conditionalized on the factor formal vs informal.
Thank you in advance!

AlliAlosa · Accepted Answer

Hi there!I would add two rollup fields on Table 2, that both point at your field on Table 1 which denotes whether the training is formal or informal. Then use the following two functions (one for each field)IF(FIND("Formal", ARRAYJOIN(ARRAYUNIQUE(values))), "Yes", "No")andIF(FIND("Informal", ARRAYJOIN(ARRAYUNIQUE(values))), "Yes", "No")IMPORTANT: I’m assuming your field that denotes whether the training is informal or formal is a single select field. I highly suggest editing those options to begin with capital letters. i.e. “Formal” vs. “formal”.If you don’t do this, the FIND() function will find the word “formal” inside of “Informal” and give you a “Yes” when you really expect a “No”. The function is case sensitive, so capitalizing the “F” will solve that problem.Hope that helps!

Jan_Segier1 · Answer

Hi there!I would add two rollup fields on Table 2, that both point at your field on Table 1 which denotes whether the training is formal or informal. Then use the following two functions (one for each field)IF(FIND("Formal", ARRAYJOIN(ARRAYUNIQUE(values))), "Yes", "No")andIF(FIND("Informal", ARRAYJOIN(ARRAYUNIQUE(values))), "Yes", "No")IMPORTANT: I’m assuming your field that denotes whether the training is informal or formal is a single select field. I highly suggest editing those options to begin with capital letters. i.e. “Formal” vs. “formal”.If you don’t do this, the FIND() function will find the word “formal” inside of “Informal” and give you a “Yes” when you really expect a “No”. The function is case sensitive, so capitalizing the “F” will solve that problem.Hope that helps!WOW! This is far above my hat  :crazy_face: , but it works and I do understand it. Wasn’t really familiar with those functions, let alone with them combined…THANK YOU for your quick response!  :kissing_heart:

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded