Combining Choice With a Filter for a Dropdown Menu

[cmanning]

I have a SharePoint List containing answers from an account questionnaire and there is one record per account and each record has a column for each answer to the questionnaire.  Some answers require a choice column like question 1 for column '1AcctRef'. 

I can successfully populate a dropdown menu for question 1 (i.e., 1AcctRef column) using the code below, which works when viewing the data (i.e., it will populate the dropdown with all choices), however I need it combined with a Filter() so I can retrieve the selected value for question 1 when ClientName = "RI MMIS" (see image above).

Choices([@'RefCollFormResponses'].'1AcctRef') // wks

How can I combine the Choices code above with Filter( RefCollFormResponses, ClientName = varRecordClientName ) to render the selected choice for the RI MMIS account? 

[cmanning]

Any info is appreciated.  I have not been able to locate any examples of combining a Choice() with a Filter(). 

[v-jefferni]

Hi @cmanning ,

Could you please share more details about your scenario? Would you like to render the selections in the dropdown that only belong to a specific client?

If so, you could try below codes OnVisible of the screen or OnSelect of buttons or OnChange of other controls where you would like the dropdown selections to get generated:

ClearCollect(colAcct1, AddColumns(Filter(RefCollFormResponses, ClientName = varRecordClientName ),"AcctRef1",ThisItem.'1AcctRef'.Value))

Use this collection colAcct1 along with a Distinct function in Items of a Dropdown or Combo box and set display field to Result, then you will get the selections of that client.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[cmanning]

@v-jefferni - Thank you for your response.  The image below will help clarify some things.  

• There are 87 questions spread out across the 17 tabs (e.g., Account Reference,  Account Details, etc.) and I am only displaying the questions and answers for each selected tab.
• The page has two modes: View and Edit.  I have gone back and forth about which approach is better: Use a single form and show only the questions and answers for the selected tab.  The other approach is to eliminate the form and just use Patch since the user is only answering certain questions in a session in which case I manage a var for View and Edit mode. 
• Selecting a client will allow access to their responses and provide additional answers by selecting Edit mode via the button.
• Questionnaire will require multiple users to complete the entire questionnaire over an unknown period of time so when in View mode, the user must see the answers populating the respective control types (text, dropdown, combo box, etc).  This requires populating the dropdowns and combo boxes with the value in the RefCollFormResponses SP list for the selected client/account, which if I don't use a form will require combining Filter() to select a specific account/client and Choice() to populate a dropdown with the selected value.

My question was how to combine Filter() with Choice() to populate a dropdown for the selected client.  Your solution requires a collection and it will impact performance greatly if I require 50 collections to correctly populate dropdowns and combo boxes.  Perhaps using a form is the better approach but I'd still like to know how to combine Filter() and Choice() without using a collection.

Thanks again for your response and any additional recommendations are appreciated. 

[v-jefferni]

Hi @cmanning ,

All right, I understand the scenario now. To auto populate those dropdowns with the desired tables, you could use below formula:

Distinct(AddColumns(Filter(RefCollFormResponses, ClientName = varRecordClientName ),"AcctRef1",ThisItem.'1AcctRef'.Value),AcctRef1).Result

Choices() will always return all the selections set for the columns within SP list, it's not necessary for your scenario.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[cmanning]

@v-jefferni - Thanks again for the response.  I am placing the code in the dropdown's Items property.  The Filter() portion works but the AddColumns is throwing an error and it is in the "ThisItem." section as it does not popup any options.  as a result, I am only using the AddColumns portion below since that is the section that has an error.  

// ThisItem. fails to bring up any options
AddColumns( Filter( RefCollFormResponses, ClientName = varRecordClientName ), "AcctRef1", ThisItem.

 Any recommendations are greatly appreciated.

[v-jefferni · Accepted solution]

Hi @cmanning ,

Sorry for my typo, it should be ThisRcord instead:

Distinct(AddColumns(Filter(RefCollFormResponses, ClientName = varRecordClientName ),"AcctRef1",ThisRecord.'1AcctRef'.Value),AcctRef1).Result

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.