Count number of occurances in a table line

[PeterER8]

 

Hi

Apologies upfront if my questions have been asked before (I could not find the answer). In my defense I am new to Caspio, started testing it last week and now have lots of questions. This one is regarding DataType formula in tables and specifically trying to count the number of occurrences of a specific text string in that data row. Easy thing to do in excel (yes, I am damaged by too much excel usage). Basically I am after “count cells that contain x”. I understand the aspect that this only works for the row in which the formula field is present and that is perfectly fine.

Example: I have the following table with the Counted_Field_Green field being a formula field and the values in each field below that.

Table Fields: Date, Name, Reason_1, Reason_2, Reason_3, Reason_4, Test, Counted_Field_Green

Table contents: 01/05/2021, Peter Max, Green, Yellow, Green, White, Yes, 2

 

This is what happens here, the formula in Counted_Field_green takes a look at the at the 4 Reason fields and counts the number of times that the entry “Green” shows up as a value. Kind of how countif works in excel or at least, I am after same functionality.

Is there a way to make this happen in a Caspio table?

If not is there a way to make it happen and populate a predefined field in a DataPage that calculates the presence of x in the entry fields?  

 

I really appreciate any help you can give me!!!

 

 

[JolliBeng]

Hi @PeterER8,

Do you want to count only the number of cells that has 'Green' value per record? Is your expected output similar to the following?


If yes, you can just use CASE-WHEN statements in your formula field that will check if the field has 'Green' value.

(CASE WHEN [@field:Reason_1] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_2] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_3] = 'Green' THEN 1
ELSE 0
END)
+
(CASE WHEN [@field:Reason_4] = 'Green' THEN 1
ELSE 0
END)

 

 

[PeterER8]

Thank you JolliBeng!

This is perfect t!

I assume this can also be used with null in the logical expression, for example in a case where the Reason_1 field is empty :

(CASE WHEN [@field:Reason_1] = NULL THEN 1
ELSE 0
END)

 

 

 

 

[PeterER8]

I just tired my expression with Null and that did not work Hmm

[JolliBeng]

You can use the IS NULL  to set a criteria if the field is null. It should look like the following: 

 

(CASE WHEN [@field:Reason_1] IS NULL THEN 1
ELSE 0
END)

 

[PeterER8]

Thanks @JolliBeng

 

[NiceDuck]

You may also try 

(CASE WHEN [@field:Reason_1] = '' THEN 1
ELSE 0
END)

 

[NiceDuck]

OR

(CASE WHEN isnull([@field:Reason_1],1) = 1 THEN 1 ELSE 0 END)