Our agency is utilizing Survey123 Connect to record compliance data. There are places in the survey where the data entrant utilizes model language to explain the given score (e.g., "An adopted FY[yy] budget was not provided before the [mm/dd/yy] documents deadline.").
I have been asked if it is possible to autofill model language into the text box that can then be modified by the user (e.g., "An adopted FY22 budget was not provided before the 06/30/2021 documents deadline.") so that they don't have to retype the model language in its entirety. Complicating the matter, which model language is utilized is dependent on what score the entity received, so what autofills into the box has to be different depending on selections from one or more select_one responses earlier in the survey.
Is there a good way to achieve this in Survey123 connect?
Solved! Go to Solution.
This one looks pretty straightforward. The constraints on Budget Status come from the preceding questions, but the model language looks like it is 1:1 with the total choices available in Budget Status, right?
In that case, you could have an external CSV with the 12 possible choices in Budget Status and bring it in with a pulldata. But since it's such a small list, you could also handle this with a hidden select_one.
Now, getting additional values into that model language would require some concatenation of other questions / calculations. But it's doable. Try to break it up into chunks whenever possible so as to avoid a gigantic IF expression, as those can be a little hard to keep organized.
Here's the workbook:
type | name | label | appearance | calculation | choice_filter |
select_one yes_no | third_party | Third Party? | horizontal | ||
select_one yes_no | meetings | Meetings? | horizontal | ||
select_one budget | budget_status | Budget Status | third_party = ${third_party} and meetings = ${meetings} | ||
select_one language_code | language_code | lang code | hidden | ${budget_status} | |
text | model_language | Model Language | jr:choice-name(${budget_status}, '${language_code}') |
And under the Choices sheet:
list_name | name | label | third_party | meetings |
yes_no | yes | Yes | ||
yes_no | no | No | ||
budget | 0 | No Budget | no | yes |
budget | 1 | Budget is adopted, but is not being implemented | no | yes |
budget | 2 | Budget is adopted and implemented, but not listed as a separate enterprise budget | no | yes |
budget | 3 | Budget is adopted, implemented, and listed as a separate enterprise | no | yes |
budget | 4 | Neither the entity nor the manager have adopted a budget | yes | yes |
budget | 5 | Either the entity or manager has adopted a budget, but it is not being implemented | yes | yes |
budget | 6 | Either the entity or manager has adopted and implemented a budget, but the other has not | yes | yes |
budget | 7 | Both the entity and manager have adopted and implemented budgets | yes | yes |
budget | 8 | The entity has not adopted a budget | no | no |
budget | 9 | The entity has adopted a budget, but it is not being implemented | no | no |
budget | 10 | Neither the entity nor the manager have adopted a budget | yes | no |
budget | 11 | Either the entity or the manager have adopted a budget, but it is not being implemented | yes | no |
language_code | 0 | Model language for the first choice. | ||
language_code | 1 | Some othermodel language for the second choice. | ||
language_code | 2 | Etc. | ||
language_code | 3 | Fourth Choice. | ||
language_code | 4 | More model language, fifth choice. | ||
language_code | 5 | Number 6. | ||
language_code | 6 | And so on | ||
language_code | 7 | And so forth | ||
language_code | 8 | and another choice | ||
language_code | 9 | third to last | ||
language_code | 10 | second to last | ||
language_code | 11 | last |
Yes, this sort of thing can certainly be done, though the expression may be quite a whopper.
You might be able to split it across multiple hidden fields. Meaning one question calculates to a string based on some response, a second to another string, and the displayed question in the form concatenates those two.
Can you give some examples of the questions involved in this situation? It's hard to give any specific guidance without knowing more about the form or schema of the underlying table.
In the case of Explanation of Budget Score, there can be twelve different versions of the model language depending on prior responses - questions and answers are paraphrased for simplicity's sake:
Is there a third party manager?
Does the entity's governing body meet routinely, per bylaw requirements?
Budget Status - No Third Party Manager with Meetings
Budget Status - Third party manager with Meetings
Budget Status - No third party manager, no meetings
Budget Status - Third party manager, no meetings
Right now, I have a single text box for responses and 12 dependent pulldata commands underneath that lists the appropriate model language.
For the convenience of the reporting manager, I would need to code this so that all responses would appear in a single column on the back end. With all the dependencies, I'm not certain whether this is realistic.
This one looks pretty straightforward. The constraints on Budget Status come from the preceding questions, but the model language looks like it is 1:1 with the total choices available in Budget Status, right?
In that case, you could have an external CSV with the 12 possible choices in Budget Status and bring it in with a pulldata. But since it's such a small list, you could also handle this with a hidden select_one.
Now, getting additional values into that model language would require some concatenation of other questions / calculations. But it's doable. Try to break it up into chunks whenever possible so as to avoid a gigantic IF expression, as those can be a little hard to keep organized.
Here's the workbook:
type | name | label | appearance | calculation | choice_filter |
select_one yes_no | third_party | Third Party? | horizontal | ||
select_one yes_no | meetings | Meetings? | horizontal | ||
select_one budget | budget_status | Budget Status | third_party = ${third_party} and meetings = ${meetings} | ||
select_one language_code | language_code | lang code | hidden | ${budget_status} | |
text | model_language | Model Language | jr:choice-name(${budget_status}, '${language_code}') |
And under the Choices sheet:
list_name | name | label | third_party | meetings |
yes_no | yes | Yes | ||
yes_no | no | No | ||
budget | 0 | No Budget | no | yes |
budget | 1 | Budget is adopted, but is not being implemented | no | yes |
budget | 2 | Budget is adopted and implemented, but not listed as a separate enterprise budget | no | yes |
budget | 3 | Budget is adopted, implemented, and listed as a separate enterprise | no | yes |
budget | 4 | Neither the entity nor the manager have adopted a budget | yes | yes |
budget | 5 | Either the entity or manager has adopted a budget, but it is not being implemented | yes | yes |
budget | 6 | Either the entity or manager has adopted and implemented a budget, but the other has not | yes | yes |
budget | 7 | Both the entity and manager have adopted and implemented budgets | yes | yes |
budget | 8 | The entity has not adopted a budget | no | no |
budget | 9 | The entity has adopted a budget, but it is not being implemented | no | no |
budget | 10 | Neither the entity nor the manager have adopted a budget | yes | no |
budget | 11 | Either the entity or the manager have adopted a budget, but it is not being implemented | yes | no |
language_code | 0 | Model language for the first choice. | ||
language_code | 1 | Some othermodel language for the second choice. | ||
language_code | 2 | Etc. | ||
language_code | 3 | Fourth Choice. | ||
language_code | 4 | More model language, fifth choice. | ||
language_code | 5 | Number 6. | ||
language_code | 6 | And so on | ||
language_code | 7 | And so forth | ||
language_code | 8 | and another choice | ||
language_code | 9 | third to last | ||
language_code | 10 | second to last | ||
language_code | 11 | last |
I like where this is going. The other part of the ask is that the user can amend the model language within the text box, as there are parts of the response that are unique to each community and cannot be fully automated. Is there a way to allow for that, or would that have to be captured in a separate text box?
Yes! That text box in the animated GIF can be selected and edited after this initial calculation happens.
You could possibly have the model language input placeholder text like "Model language for community [XXXX]", then use a regular expression constraint to ensure that the placeholder are replaced by some other text.
You could also concatenate text from another box, but this would require a bit more work, and if the model text isn't consistently formatted, may be tricky. Supposing the model text all followed the format "string_part_1 + placeholder + string_part_2", then you could just repeat my above example with a separate hidden select_one for each part of the full string.
But in my opinion, that's getting too complicated. Just allow the users to edit the model text, and think about having that constraint to check that something's been entered.
Also! Be sure to set those hidden fields to null types, otherwise you'll get useless data in your table.
Thank you, Josh. One last question (hopefully)...
The current organization of the Choices sheet allows us to use different list_name values to assign points to their responses, which are used in a calculation at the bottom of the sheet:
Budget | 0 | Neither the owner or the contracted managing entity have adopted a budget. (0 Points) |
Budget | 10 | Either the owner OR the contracted managing entity has adopted a budget, but it is not being implemented. (10 Points) |
Budget | 13 | Either the owner OR the contracted managing entity has adopted and implements a budget, but the other has not. (13 Points) |
Budget | 15 | The owner AND the contracted managing entity have each adopted a realistic budget and budget amendments are adopted as needed. Accurate monthly financial reports are prepared and submitted to the owner's governing body. (15 Points) |
BudgetNo3p | 0 | The owner has not adopted a budget. (0 Points) |
BudgetNo3p | 10 | The owner has adopted a budget, but it is not being implemented. (10 Points) |
BudgetNo3p | 13 | The owner has adopted and implements a budget, but not all utility-related income and expenses are listed in a separate enterprise budget. (13 Points) |
BudgetNo3p | 15 | The owner has adopted a realistic budget, with all related income and expenses listed in a separate enterprise budget. Budget amendments are adopted as needed, and accurate monthly financial reports are prepared and submitted to the governing body. (15 Points) |
Bgt3rdPNoMtngs | 0 | Neither the owner or the contracted managing entity have adopted a budget. (0 Points) |
Bgt3rdPNoMtngs | 10 | Either the owner OR the contracted managing entity has adopted a budget, but it is not being implemented. (10 Points) |
BgtNo3rdPNoMtngs | 0 | The owner has not adopted a budget. (0 Points) |
BgtNo3rdPNoMtngs | 10 | The owner has adopted a budget, but it is not being implemented. (10 Points) |
Calculation (based on names from workbook):
(${Training_yes_no}-(-${Meeting_pts})-(-${Third_Party_Budget})-(-${No_Third_Party_Budget})-(-${Third_Party_Budget_NoMtngs})-(-${No_Third_Party_Budget_NoMtngs})-(-${Revenue_pts})-(-${Third_Party_WorkComp})-(-${No_Third_Party_WorkComp})) |
Is there a clean way to implement your solution, using a single list name and filtering options, while still assigning point values to the responses?
I think you could repeat the language_code method and have a separate hidden question that associates each budget code with a point value. With an associated integer question, you can see the point values:
If you don't want to use up a field just for this, you can also use the "jr:choice-name" function inside of a calculation. Just don't forget to put it inside of number(...)! Otherwise you'll concatenate your point values and get 10 + 0 = 100!
${other_points} + number(jr:choice-name(${budget_status}, '${point_value}'))
This is exactly what I did that I posted below but this post is a better way to explain it.
You can have a calculation add text and then the user can edit. When they do the little refresh icon will show, but if the user does not touch it then it will let them edit and keep the edits.
If you have a few messages a big if should do it. If the text was not dynamic you could use pulldata for a cleaner look. Now can you have field names in a pulldata that then fire - no idea you can try it.
I am guessing you want them to be able to add text but not edit the standard text? Then it is easier. Have one field for the a pulldata or if to get the standard statement. Then a second field the user can type in whatever. Then a 3rd field that concats these two together for the final text to submit. I am doing this one on a project so that we can standardize a few comments. I have checks for the standard and then a free form box that get combined. I do not love it but could not change the schema so it works. I even made it so depending on if they pick yes or no they get different standard comments. I did this one using choice filter vs pulldata. I just tried and the label will not take a field though.
Hope that helps more into may help