If((Find)) not functioning properly

Topic Labels: Formulas
674 1
Showing results for 
Search instead for 
Did you mean: 
6 - Interface Innovator
6 - Interface Innovator

I frequently use the formula IF((FIND)) formula to create indicator columns with 1s and 0s or emojis.

In my latest project, however, it has not been working out as I want:

The above is a screenshot of a Grid View of form results.
The field “BACKEND_Expertise Category Prefix” is a lookup field that shows the subject area codes of subject areas that our respondents selected from a Linked Record question.

For example in the fifth row, you see “SB, SB, SB, SS, SH”; that’s because the respondent selected the options for “Early Childhood education;” “Out-of school learning;” and “Parent Teacher Involvement;” which are all coded “SB” (which in our taxonomy system, means they fall under Education).

I then created dummy variables/fields using the formula field type to spit out 1 or 0.

IF( FIND(“SB”, {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}), 1, 0 ) = so that all records that have “SB” in the lookup field will get a “1” in the column “Education”, and then I can sum up how many respondents chose that subject area. Basically, the formula looks for the two-letter code from within the lookup field.

^^I have around 10 formula fields with the formula above with switched two-letter codes; e.g. IF(FIND(“SF”,… / IF(FIND(“SG”,… and so forth

However, the output is unreliable.

In some rows where there is an SB, the column for SB has a 0. I also noticed that for each record, only one of these dummy columns get a 1 in it, which is strange… Finally, I found that the comma does not make a difference. I thought maybe changing F(FIND(“SF” → to F(FIND(“SF,” will fix the problem, but it didn’t.

1 Reply 1
7 - App Architect
7 - App Architect

Hi @Adi_Menayang!
Try this

IF( FIND('SB', {BACKEND_Expertise Category Prefix (from Subject Area Expertise)}&''), 1, 0 ) 

What is most likely happening is that you are looking for ‘SB’ in an array and not a string.
Adding &’’ after the field name fixes this. Someone else can probably explain the exact why’s and how’s better, but this usually helps me.