Creating a Chart and Results Page if two of a possible 4 fields aren't blank

[ltighe]

I'm running a certification scheme whereby if students complete any 2 out of a possible 4 options they are eligible for an award. Students are filling in a log using a Caspio Data form. I want to create a data page so I can search for any students who have completed at least 2 of the options to monitor how many students have reached that stage.

So it looks like

Student ID- Unique

Opportunity 1 - 

Opportunity 2 - 

Opportunity 3 - 

Opportunity 4 - 

Really racking my brains for how to do this, I'm thinking that if  I use a triggered action so that if the field is not blank then the value of 1 is entered into a separate field. This would then allow me to create a search and report so that anyone with a value of over 2 would be displayed. 

I've tried to do this but it creates an error when the students update their log, so I think I'm going wrong somewhere.

 

 

[MayMusic]

In your table add a formula field and use the code below to see how many they have entered:

CASE WHEN [@field:Opportunity1] ='' THEN 0 ELSE 1 END+
CASE WHEN [@field:Opportunity2] ='' THEN 0 ELSE 1 END+
CASE WHEN [@field:Opportunity3] ='' THEN 0 ELSE 1 END+
CASE WHEN [@field:Opportunity4] ='' THEN 0 ELSE 1 END

Now you can search on this field value. There is no need to have trigger for this case.