topic Using a choice filter and calculation for the same question in ArcGIS Survey123 Questions

Using a choice filter and calculation for the same question

EricaNova — Fri, 19 Jan 2024 19:41:55 GMT

I am authoring a survey with Survey123 XLSForms and I cannot quite get where I want with choice filters and the relevant field.

The form has a question, "date_drit_test" that the user can leave blank (if no test occurred) or fill out (if a test occurred):

If the user selects a date, then another question, 'drit_test_result' should appear to indicate test results (which was hidden if no date was selected):

However, I do not want any null values in the 'drit_test_result' column. If a user does not select a date for the DRIT test, I want the value of 'drit_test_result' to be filled with "Not tested"/"NT".

I've got everything working save the last bit. I also suspect I could make my choice filter without the "helper" column 'date_drit_result' but couldn't figure out how to do it - any thoughts there?

Relevant worksheets & fields:

survey worksheet
type	name	label	calculation	choice_filter	relevant
date	date_drit_test	Date of DRIT test
hidden	date_drit_result	Was there a DRIT test date?	if(string-length(${date_drit_test})>0, "test_date_present", "test_date_absent")
select_one test_result	drit_test_result	DRIT test result		test_filter=${date_drit_result}	string-length(${date_drit_test})>0

choices worksheet
list_name	name	label	test_filter
test_result	INC	Inconclusive	test_date_present
test_result	N	Negative	test_date_present
test_result	P	Positive	test_date_present
test_result	NT	Not Tested	test_date_absent
test_result	RT	Retested	test_date_present
test_result	F	Failed (PCR)	test_date_present

Re: Using a choice filter and calculation for the same question

DouglasKnabe — Fri, 19 Jan 2024 20:03:48 GMT

Can you use a calculated field to store the final result? if(string-length(${drit_test_result})>0,${drit_test_result},"Not Tested")

to go further, you could set use a note to display this in lieu of the test_result question and use the relevant column to only display it when there's no date selected

Re: Using a choice filter and calculation for the same question

abureaux — Fri, 19 Jan 2024 21:50:54 GMT

Simple solution is to rename drit_test_result to something list drit_test_result_slct then add a calculate:

EDIT: Updated calculate. I forgot to include the jr:choice-name() component to save the label rather than the name. Basically, just need to choose whether you want to save the name or label from drit_test_result_slct.

Re: Using a choice filter and calculation for the same question

EricaNova — Mon, 22 Jan 2024 13:42:14 GMT

Thanks for your suggestion @abureaux ! I'm not sure I understand your response fully and I'm getting an error when I try to incorporate your suggestion (maybe around the first appearance of ")

Re: Using a choice filter and calculation for the same question

EricaNova — Mon, 22 Jan 2024 15:43:28 GMT

I replaced the single quotation marks with "" and it works better... but now I'm getting labels in one field and code in the other:

Re: Using a choice filter and calculation for the same question

abureaux — Mon, 22 Jan 2024 15:58:39 GMT

There are three ways you could do this.

This is what I was thinking. Basically, it automates the "Not Tested" part.

Here is what you have. This works perfectly fine but just takes an extra click (just make the select_one "required"):

Here is a slightly modified version of yours that removes that extra click. It's convoluted, but it does work:

EDIT: I removed the 'note' from Option 3. It was just there for proof of concept since the select_one is hidden for part of this operation. It wasn't required to make this work.

Re: Using a choice filter and calculation for the same question

EricaNova — Mon, 22 Jan 2024 15:58:09 GMT

Your answer looks cleaner - but then am I storing the full text (e.g., "Not Tested" vs "NT")? How can I add a domain to that calculate field?

Re: Using a choice filter and calculation for the same question

abureaux — Mon, 22 Jan 2024 16:06:21 GMT

Each of the three options all have slightly different applications, so just select the one that works best. At first, I was just thinking Feature Reports, which Option 1 would do well.

Options 2 and 3 will handle domain values better since they deal with the actual select_one. Option 3 would automate the process more for your end users since they don't need to make a selection at all.

Re: Using a choice filter and calculation for the same question

EricaNova — Mon, 22 Jan 2024 17:48:38 GMT

Thanks so much for staying with me @abureaux !

I tried Option 3, and it is mostly working. Users don't see options for test result when they leave the date field empty. If they leave the date field empty, "NT" / "Not Tested" is selected. If they choose a date they can choose any of the other test result options. However, the drit_test_result field is still displaying codes instead of labels in my table when I view it in Survey123 (my other fields with domains show the labels).

Also, when I attempt to edit a record and delete the entry from date_drit_test, "NT" is not auto-populated (even though "calculateMode" is set to "always").

Erica

Re: Using a choice filter and calculation for the same question

abureaux — Mon, 22 Jan 2024 19:21:20 GMT

Opps. There was an error on my part in Option 3. This should work as expected.

The idea is you would use "drit_test_result" as your reference field as it will have either the user's selection (if there is a date) or "Not Tested" (if there is no date). This should minimize user input while preserving your domain values. "drit_test_result_slct" can be ignored in the table as it is just an intermediary field, but you don't want to make it null if you are allowing editing because you want that user selection (if any) to be preserved.

Survey tab:

Choices tab:

When you submit:

When you Edit a submission:

Note: That "Just a random select" at the end is just a plain select_one I used for side-by-side testing.

Re: Using a choice filter and calculation for the same question

EricaNova — Tue, 30 Jan 2024 15:08:50 GMT

I was having trouble getting @abureaux 's solution to work. I didn't want an extra field in my database, and I also wanted users to see the label, rather than the code, in the final field.

I found out that select_one questions with a choice filter will not automatically create a domain for you, so you must manually create the domain in AGOL if you want one. https://community.esri.com/t5/arcgis-survey123-questions/survey123-name-field-displaying-instead-of-label/td-p/1054639

Solution (with one caveat)

If a user does not select a date for date_drit_test, user does not see any choices for drit_test_result; the code "NT" will be entered into the database with associated label "Not Tested" when user presses submit:

If a user does select a date for date_drit_test, they will be provided with the list of appropriate choices.
The only disadvantage of this solution is that as soon as a user selects a date, the next question drit_test_result is highlighted in red and tells them the question is required. I would prefer they get that warning at the bottom of the form or after they try to press submit, but I couldn't figure out how to delay the required message. I'm all ears if anyone has ideas!

Here is the database as shown from Survey123 Data tab - note labels appear, not codes.

XLSForm options to reproduce:

survey worksheet
type	name	label	appearance	required	default	calculation	choice_filter	bind::esri:fieldType	body::esri:visible
date	date_drit_test	DRIT test date
text	date_drit_result	DRIT date present	hidden			if(string-length(${date_drit_test})>0, "test_date_present", "test_date_absent")		null
select_one test_result	drit_test_result	DRIT test result		yes	NT		test_filter=${date_drit_result}		string-length(${date_drit_test})>0

choices worksheet
list_name	name	label	test_filter	owner_notes
test_result	I	Inconclusive	test_date_present	Unlike other choice lists in this survey, this choice list has a filter associated with it.
test_result	N	Negative	test_date_present	Therefore, you must create and update or edit the domain by hand to match this list in AGOL if the choice list changes.
test_result	P	Positive	test_date_present	See more here:
test_result	R	Retested	test_date_present	https://community.esri.com/t5/arcgis-survey123-questions/survey123-name-field-displaying-instead-of-label/td-p/1054639
test_result	U	Unfit to test	test_date_present
test_result	NT	Not Tested	test_date_absent

Re: Using a choice filter and calculation for the same question

EricaNova — Tue, 30 Jan 2024 16:42:29 GMT

I was having trouble implementing @abureaux 's solution - I wanted the labels, not the codes, to appear in my data table (like other select_one questions) and I also didn't want to save a second field that would potentially confuse my users.

I came up with a fairly simple solution that does what I want. There is one caveat I describe below - would be great if anyone has an idea for how to get around it!

Please note: I had to add the domain manually in AGOL for this question. I went into the hosted feature layer overview, clicked "Data", clicked "Fields", clicked my drit_test_result field, and clicked "Create List" and entered the same values that are present in my choice list on my XLSForm. Apparently, when you use a choice filter, domains do not automatically get added to your feature layer like other select_one questions. See more, here: https://community.esri.com/t5/arcgis-survey123-questions/survey123-name-field-displaying-instead-of-label/td-p/1054639

So, finally:

If a user does not enter a drit_test_date, they do not see options to select a drit_test_result. They submit the form, and code "NT" (label "Not Tested") is recorded in the database. Here is what the form looks like:

If a user does enter a drit_test_date, they see options to select a drit_test_result. Unfortunately, the field is automatically flagged with red with a "required" message before they have a chance to click an option. I can live with this but I'd rather they get an error message after hitting submit.

Here is the data view (from Survey123 data tab) of both options, showing labels, not codes.

Here are the options I used in the survey and choices worksheets:

survey worksheet
type	name	label	appearance	required	default	calculation	choice_filter	bind::esri:fieldType	body::esri:visible
date	date_drit_test	DRIT test date
text	date_drit_result	DRIT date present	hidden			if(string-length(${date_drit_test})>0, "test_date_present", "test_date_absent")		null
select_one test_result	drit_test_result	DRIT test result		yes	NT		test_filter=${date_drit_result}		string-length(${date_drit_test})>0

choices worksheet
list_name	name	label	test_filter	owner_notes
test_result	I	Inconclusive	test_date_present	Unlike other choice lists in this survey, this choice list has a filter associated with it.
test_result	N	Negative	test_date_present	Therefore, you must create and update or edit the domain by hand to match this list in AGOL if the choice list changes.
test_result	P	Positive	test_date_present	See more here:
test_result	R	Retested	test_date_present	https://community.esri.com/t5/arcgis-survey123-questions/survey123-name-field-displaying-instead-of-label/td-p/1054639
test_result	U	Unfit to test	test_date_present
test_result	NT	Not Tested	test_date_absent