Survey123 Return results with CONTAINS function?

764
10
Jump to solution
06-03-2020 02:57 PM
by Anonymous User
Not applicable

Hi all,

I have a survey and I want to find a value in a string that is after the "="  sign. Where if the value they select contains FAC, FACW or OBL after the equal sign - then answer YES to a dominant species of vegetation.  If it is a no, then No is automatically selected in dominant species.

Just not sure how to format this formula in the survey:

if(selected(${VegetationPlantNames} contains 'FAC", or 'FACW' or 'OBL') after "=" THEN TreeStratumDominantSpeciesYN = 'YES'

Please help.

See attached survey.

Thanks,

Paul

0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Notable Contributor

See above this works for me

selected(${VegetationToReportYN},'Yes') and contains(substr(${VegetationPlantNames}, string-length(${VegetationPlantNames})-3, string-length(${VegetationPlantNames})), "ACW")

View solution in original post

10 Replies
by Anonymous User
Not applicable

I understand the CONTAINS returns a true or false. Is there another way to search within a string in Survey123?

0 Kudos
DougBrowning
MVP Notable Contributor

Having the HTML in there does make it harder.  You syntax is wrong but you may just be showing an example.

Looking at your sheet what you want to check is always at the end.  So how about something like this

conatins(substr(question, string-length(question)-4, string-length(question)), "FACW")

then add the rest on the same way with with an "or" in between (note you could do -3 for the rest but -4 should also work).

Hope that helps.

0 Kudos
by Anonymous User
Not applicable

Thanks Doug,

I have the following as the expression (for testing):

selected(${VegetationToReportYN},'Yes') and contains(substr(TreeStratumPlantNames, string-length(TreeStratumPlantNames)-3, string-length(TreeStratumPlantNames)), "ACW")

I think it is searching the entire external_choices section and always comes back passing the criteria. That above statement is in this line:

select_one yes_noTreeStratumDominantSpeciesYN<center><font size="1">Dominant Species:</font></center>

under relevant.  Is this how it should work, because I only want TreeStratumDominantSpeciesYN to be YES if this condition is valid for the selected tree Otherwise, it will be "No". Do you additional thoughts on this?

Thanks,

Paul

0 Kudos
DougBrowning
MVP Notable Contributor

I am not seeing the field TreeStratumPlantNames.  Should it be VegetationPlantNames?

This works

selected(${VegetationToReportYN},'Yes') and contains(substr(${VegetationPlantNames}, string-length(${VegetationPlantNames})-3, string-length(${VegetationPlantNames})), "ACW")

0 Kudos
DougBrowning
MVP Notable Contributor

Oh and you are missing the field syntax ${}.  Not sure if you just were giving an example or you missed that.

0 Kudos
by Anonymous User
Not applicable

My apologies, I have made a few changes to my local form. Please use the name (VegetationPlantNames) in the posted form, not (TreeStrarumPlantNames).

0 Kudos
DougBrowning
MVP Notable Contributor

See above this works for me

selected(${VegetationToReportYN},'Yes') and contains(substr(${VegetationPlantNames}, string-length(${VegetationPlantNames})-3, string-length(${VegetationPlantNames})), "ACW")

by Anonymous User
Not applicable

Thanks, that worked!

Have a great day and thanks again for the guidance.

Paul

DougBrowning
MVP Notable Contributor

Great.  Note I would not put that HTML code in the name field.  It will be in your data forever and that will be a nightmare.  I you really want the colors put them in the label column only.

I also see commas in your lists.  Cannot do that.

0 Kudos