Can i display a note based on the number of options in a filtered list?

[Trippetoe]

Hello. 

I'm using Survey123 Connect to create survey that contains a 'cascading select'. A contractor must first select their company name and then select an active Work Order Number. The Work Order Number options are filtered by the company name. There may be cases where a company does not have any active Work Order numbers.  In those cases, i'd like to display a note that gives some details on who to contact, etc. 

I'm imagining that the 'relevant' filter for the note would be something like 'count number of options in work order number question = 0'.  So far, i can't figure out how to do that in Survey123 Connect. Does a function like that exist?

thank you. 

[abureaux]

You can use string-length() to count the characters in the search response and determine if it contains anything.

E.g., In the Relevant column: string-length(${my_search})=0

[Trippetoe]

Hi @abureaux 

Thanks for your suggestion, but it doesn't work as i need it to. It seems that your formula detects whether or not a value has been selected from the Work Order Number question. I need a formula that can detect whether there are any options available in the Work Order number question. Its possible that there are no options in the Work Order Number questions based on the filter value in place because of the previous question's (Company) answer (hopefully that makes sense!)

[abureaux]

My apologies for the confusion. The above formula would work with search(), but not cascading selects.

There is no explicit formula for detecting the number of items returned through a choice filter. You would have to keep tabs on which contractors do not have any Work Order Numbers.

As an alternative, have you considered using a Dynamic Choice List?

I put together an example as proof of concept, but I have a server issue to deal with. I will try to post later.

[abureaux · Accepted solution]

If I select an option that doesn't have any results (in your case, no Work Orders), the string-length of the JSON is 0. And if anything is returned, then the string-length is >0. Your note could be keyed to that calculation (e.g., calc_length, above).

I just made a very lazy CSV called B.csv which contained a few options and uploaded it to Portal (below). Essentially, you would just need to maintain a Feature Layer of Work Order Numbers.

[Trippetoe]

Hey @abureaux 

Based on your suggestions I'm gonna take a deeper dive into this, but before i got lost in that I wanted to Thank You for your sharing your time and knowledge. I really appreciate it. I'll get back to you later with results from my fiddling around. 

[Trippetoe]

Hi @abureaux 

It has been a fun adventure learning more about the search() functionality. Thank you for pointing me in that direction. Broadly speaking, I think your suggestion will work fine.

I am seeing two bits of functionality, though, that are a bit nettlesome. 

1. When the survey loads and before any Company is selected, all possible Work Order Numbers are available in the dropdown. Once a company is selected, the list is filtered as expected, but i'm a bit bothered by having all work order numbers present prior to selection. I certainly could hide the Work Order question until a Company is selected, but the question is 'foundational' to the survey and i'm a bit hesitant to hide/display the question depending on the state of the Company selection
2. If i select a company, then make a Work Order Number selection and then clear the Company selection, the selected Work Order Number remains visible. Once i make a new Company selection, the selected work order number is cleared and all is as expected. I would expect the Work Order Number to be cleared if the Company  selection is cleared

Those two items aren't enough to prevent me from using the Search() functionality, but just troublesome enough to slightly irk me.  Do you have any suggestions for making the Work Order Number list blank until, and only if, a company name is selected?

[abureaux · Accepted solution]

1. As you mentioned, hiding the Work Order Number part until a company name is added would do the trick. Personally, I'd go (and have gone) this route.
   
   However, there may be an alternative... I haven't tried this before, but I think this works in theory. If you include a second item in the WHERE clause of the search() it could reference a calculate in your survey and some field in the Feature Layer. The calculate could be something like if(${company_name}='','RandomContent',''). This second WHERE clause referencing both this calculate and something in the Work Order Number Feature Layer would never match so long as the company name was empty. But as soon as you add a company name, they could match (basically just trying to simulate 1=1).
2. That is a good thing with using the relevant method to hide an item: it clears the selections made.
   
   I think the behaviour you are seeing may related to the autocomplete appearance, and I don't believe that can be changed. You could always swap to minimal appearance. Below is a Cascading Select showing that minimal clears the selection automatically:
   

[Trippetoe]

Using a Calculate as the value of the searchText worked perfectly.  I didn't have as much good look changing out the autocomplete appearance for minimal. The app behaved the same regardless of that setting. But I'm calling this a victory for now and moving on to the next hurdle.

Thanks again for your help. It is super-appreciated!