Update Y/N Column based on multiple choice field

[AMR1009]

I am trying to build a flow to pick up items from a multiple choice field and match them with a Y/N column. I have 48 Y/N columns, 1 Y/N column that indicates attendance, and a multiple choice column that has all of the names of the 48 Y/N columns. 

What we need: After the multiple choice column has been filled out, if the attendance column is marked with a 'Yes', a flow would be triggered and match the multiple choices to the 48 Y/N columns and mark the matched ones with a "Yes".

Sounds complicated, but if at all possible, it would make our lives so much easier. 

Thank you!

[wskinnermctc]

Can you provide a screenshot of the multiple choice column and some of the Y/N columns?

I'm having a hard time understanding what is happening. Would like to see what this multiple choice column option look like. 

Is this all happening in the same SharePoint list? Or is it being imported from a MS Form or something else?

[AMR1009]

Yes, of course. See screenshot. 

This list tracks training attendance per associate/per date (whether they attended or not). However, if they do attend, they are  marked with a Yes and based on the multiple choice column (Training Type) selection, we would then mark with a "Yes" under the column that matches each name on the multiple choice field. (I am so sorry for the lengthy explanation).

The reason we decided to have both, the multiple choice column in addition to the Y/N columns is because we also need to track whether the associate did not attend a specific training session so they can get rescheduled in the future. Additionally, we need to pull reports based on positive attendance per each specific Y/N column. 

The first 7 columns of the list are being extracted via powerautomate through file upload in powerapps.

[AMR1009]

Yes, of course. See screenshot. 

This list tracks training attendance per associate/per date (whether they attended or not). However, if they do attend, they are  marked with a Yes and based on the multiple choice column (Training Type) selection, we would then mark with a "Yes" under the column that matches each name on the multiple choice field. (I am so sorry for the lengthy explanation).

The reason we decided to have both, the multiple choice column in addition to the Y/N columns is because we also need to track whether the associate did not attend a specific training session so they can get rescheduled in the future. Additionally, we need to pull reports based on positive attendance per each specific Y/N column. 

The first 7 columns of the list are being extracted via powerautomate through file upload in powerapps

[wskinnermctc]

Hhhmmmm very interesting. Don't apologize for lengthy explanation, it helps.

I think it can be done, but it would require that the column name matches the value. Also that the column with the options is a choice field (it looks like it is).

I'm testing something right now for it. I'm assuming the trigger will be "When an Item is Created" so basically the item is created in the list with the first 7 columns filled, and then power automate will get the item and do the hard work on the choice column to update the other columns. 

Before I start trying to get this configured. Why can't this be done when the item is initially created from the power app? I'm not familiar with the file upload in power apps. Are they dropping a document in power apps and it is filling in all of this information automatically? Or does the user make any selections in power apps?

You don't have to explain too much about the power app. I'm basically saying that if a person is making these selections in power apps than it should be easy to get the info into sharepoint list.

[AMR1009]

Yep. The column with the options is a multiple choice column that contains the same names of the rest of the y/n columns. This is what the file looks like since that is really all we need from a training standpoint (associate name, and if they attended + any additional notes that might need to be shared).

 

The person leading the training session would fill that out and drop it on the app screen where they would select the session date, the associate's location, and what type of training (this dropdown populates the same choices from the choices column in the SP list).

--I am currently working on making all of the values in the choice column be identical to the other column names.

[AMR1009]

forgot to mention -- once the user hits 'submit' a flow is triggered to extract the info from the file + transfer it to SP. it also looks up additional information from other sources to fill out the remainder of the first 7 fields. 

[wskinnermctc]

Thank you for the information, it is helpful. I am not going to be able to work on this today, but I can on Monday. 

If you want to try to test some things, I'll let you know what I planned on trying.

https://www.tachytelic.net/2020/10/remove-unwanted-characters-power-automate-flow/

The link above shows instructions for how to increment through letters of a text string and remove the unwanted/invalid characters. The Invalid Characters are pre-typed in an array.

This isn't exactly what I was going to do, but the concept was similar. Trying to put the column names in an Array and then the Multiple Choice responses in another Array, then compare the two. Finally trying to get that into a format that can be easily used for an update item.

I think using HTTP requests might be helpful since you could pull the field to get all of the multiple choice options, put them in an Array. (This would prevent needing to hard type names into the flow every time the column is updated.)

Maybe use another HTTP request to update the item. This could be a loop where the column name and choice are a variable. So it would basically update the item 48 times for each column individually. 

The alternative will be a basic Update Item action and then have to put some kind of if() expression that will check whatever compare array.

Lastly I think there needs to be an additional column that is almost for errors or no matched items. So if something is put into the multiple choice column that shouldn't be there (due to how the PowerApp upload works) it would list those specific options. Like hey here are some options that are in the multiple choice column but didn't actually update a real column. 

I don't know if that is necessary, but kind of a contingency since there seems to be possibility of a variability of inputs.

So not exactly sure how this will look, but thats my general idea.

[wskinnermctc]

Do you still need a solution for this? I have a working flow, but it's been a week since the last message, so I don't want to write up all the instructions if you already have this solved some other way.

[AMR1009]

Hi there! Yes, I still need a solution as I haven't been able to successfully build a flow that does what I need. I appreciate you following up!

[wskinnermctc]

Ok I have the flow made with picture instructions. The primary key to make the flow work starts with you to manually input the column names (Title and InternalName) as well as the corresponding multi-choice response into an Array variable. This variable will be used to reference what was selected in the multi-choice response to the corresponding column.

The benefit of this manual array is that you can easily match choice values to the columns and don't need to choice options to exactly match the column name. 

The difficult part of this manual array is making sure the text match exactly as well as the format of the array itself. If you are off by a space or incorrect letter, then it will not work correctly.

FYI: I made the flow and manual array in this format so that it is possible to adjust the flow and add some steps so that the column names and choice values can be pulled using an HTTP request without needing to type the full manual array. However, I think the example is better with the manual array since it is easier to adjust for text errors.

Below is my example SharePoint List and Columns:

SharePoint List ViewSharePoint Multi-Choice ColumnSharePoint List Settings

Below I am showing the individual Boolean (Yes No) type column names. This is to give an idea of how the column Title (what is displayed) can be different than the column InternalName that is used by power automate.

Column - AmazonColumn - Crate & BarrelColumn - NissanColumn - Amazon - SecureColumn - DSGColumn - Marvel Studios

SharePoint columns have a Title and InternalName. The Title is what is displayed and can be changed. The column InternalName will never change after creation, even if the Title is changed, and the InternalName will encode special characters. 

Below I am making a table to show how my SharePoint List Boolean (Yes No) columns correspond with the Multi-Choice options.

Column Title	Column InternalName	Corresponding Multi-Choice Option
Amazon	Amazon	Amazon
Crate & Barrel	CrateBarrel	Crate & Barrel
Nissan	Nissan	NISSAN
Amazon - Secure	Amazon_x0020__x002d__x0020_Secur	Amazon Secure
**bleep**'s Sporting Goods	Dick_x0027_s_x0020_Sporting_x002	DSG
Marvel Studios	Marvel_x0020_Studios	Marvel

This same type of relationship will be typed into the Manual Array in the flow.

Below is an outline of the flow actions:

Example Flow Overview

I have renamed actions using custom titles, but the type of action such as "Parse JSON", "Select", "Set Variable", "Update Item", etc, is within the first part of the name so you should be able to tell what type of action to add to your flow. For example, the action "A2 Parse JSON - ManualColumnReferenceArray" is a Parse JSON action that I renamed. Similarly, the action "B1 Select - MultiChoice Response Format" is a Select action that I renamed.

I will begin to show detailed parts of the flow with descriptions.

Flow Detail 1

The beginning of the flow is basically a trigger for when an item is created and then to Initialize Variable of an Array. This Array will be manually filled in a following step with the column names and corresponding choices. So leave it blank when it is initialized.

The manual array needs to be filled in the step "A1 Set Variable - Manual Column Reference" in the correct format. You need to insure the text matches column names and choices as well as no additional spaces added.

Flow Detail 2 - Manual Array Reference

Below is the text format of what to use in the "A1 Set Variable - Manual Column Reference" so you can copy and paste into your flow and edit the column names. If you need more columns, just add to the example but remember to not have a comma after the last item.

[
  {
    "InternalName": "Amazon",
    "Title": "Amazon",
    "ChoiceOption": "Amazon"
  },
  {
    "InternalName": "CrateBarrel",
    "Title": "Crate & Barrel",
    "ChoiceOption": "Crate & Barrel"
  },
  {
    "InternalName": "Nissan",
    "Title": "Nissan",
    "ChoiceOption": "NISSAN"
  },
  {
    "InternalName": "Amazon_x0020__x002d__x0020_Secur",
    "Title": "Amazon - Secure",
    "ChoiceOption": "Amazon Secure"
  },
  {
    "InternalName": "Dick_x0027_s_x0020_Sporting_x002",
    "Title": "**bleep**'s Sporting Goods",
    "ChoiceOption": "DSG"
  },
  {
    "InternalName": "Marvel_x0020_Studios",
    "Title": "Marvel Studios",
    "ChoiceOption": "Marvel"
  }
]

The action "A2 Parse JSON - ManualColumnReferenceArray" is a Parse JSON on the array variable. The schema should not change since the values being used are the same no matter how many columns you add to the array.

Use the text below to copy and paste directly into the Parse JSON schema. 

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "InternalName": {
                "type": "string"
            },
            "Title": {
                "type": "string"
            },
            "ChoiceOption": {
                "type": "string"
            }
        }
    }
}

The next flow steps are to initialize two more variables that will be used later and map the outputs of the SharePoint List Item choice columns using a Select action.

Flow Detail 4 - Select Choice Values

Within the Select action the From: is the full choice column body and the Map is the choice column Value. There will be multiple dynamic content options for a choice column, so be sure to put the Body in the From and the Value in the Map. 

The next flow step is an Apply to Each which is using the array variable that has the column names. The apply to each is going through each column name and corresponding choiceOption and comparing it to what is mapped in the Select action. 

So for each column name, it is asking, "Does the Select output contain the ChoiceOption for this column? If yes, then put a value of true, if no, put a value of false."

Flow Detail 4 - Apply to Each Column

The format and expression used in "C1 Append to Array Variable - BooleanColumnResultsArray" is so that it can add each value to an array. The format is below:

{
  {InternalName}: {true/false}
}

 The if() expression used to determine if the value should be true or false is below:

if(contains(body('B1_Select'),items('C0_Apply_to_Each')?['ChoiceOption']),true,false)

The exact example used with my action names is below:

{
  items('C0_Apply_to_Each')?['InternalName']: if(contains(body('B1_Select_-_MultiChoice_Response_Format'),items('C0_Apply_to_Each')?['ChoiceOption']),true,false)
}

The action "D1 Set Variable - ResultsObject" is taking the variable array "BooleanColumnResultsArray" and formatting it into a single object. It is doing this by converting it to a string, replacing the array brackets, and then setting it to a json object.

This is so that the Update Item can be used without an Apply to Each which would be required if using an Array variable. Since it will be a single object, you can call individual items out of the ResultsObject in the Update Item step.

The expression used in "D1 Set Variable - ResultsObject" is below:

json(replace(replace(replace(string(variables('BooleanColumnResultsArray')), '[{', '{'), '}]', '}'), '},{', ','))

The Update Item is relatively simple for calling values out of the variable ResultsObject.

Flow Detail 5 - Update Item

The only difficult part of the Update Item action is needing to manually type in the expression that will reference the value from the ResultsObject variable.

Below are the specific steps for putting the expression to get the value out of the variable. It is basically variable('ResultsObject')?['InternalName'] for each column.

The InternalName is what was used in the ManualColumnReferenceArray;

Update Expression 1Update Expression 2Update Expression 3Update Expression 4

I made an item in my SharePoint list that had all choice options selected and here are the results:

Flow Results 1Flow Results 2Flow Results 3SharePoint List Results

This might seem like a lot, but the flow is actually short. You just have to make sure the items are referenced correctly. I wanted to add a lot of pictures to help reference. 

Let me know if this works for you,

[AMR1009]

Hi there! I had to put a pin on this project to shift my focus towards a different one. I was able to replicate the flow but I'm getting 2 different errors - one says "Actions in this flow may result in an infinite trigger loop" and the second one says that the 'from' field in the select multichoice format is 'null'... but i am now realizing that because i had to tweak it to work in the inverse, i might have broken it lol. I made the flow for a second app that works in a similar manner but it needs to pick up the 'yes' from the text columns (y/n columns are a pain), and select the corresponding choice in the choice column. 

[wskinnermctc · Accepted solution]

The infinite trigger error might be due to using When an Item is Created or Modified. In my example I used "When an Item is Created" so there is no way there could be an infinite loop.

I'm not understanding the second error due to what you have changed. You will have to explain that more.

Are you saying that have changed the Power App to no longer fill in a Multi-Choice column, but instead it will put a Yes into each the individual columns? And now you want to read all of the Yes columns and update the item multi-select choice column with the choices that have a corresponding Yes column?

[Matthy79]

Hello,

If I understand everything correct this can be done with 2 or 3 simple steps and I will provide a quick solution for it but first of all we should be careful about the infinity loop. If the trigger watches for updates, this could be a problem because if you update the list when using flow, this will also be recognized as update and trigger the flow again ending in an infinity loop.

So how does it work right now? The user creates the item and updates it later. Or will the item be updated all the time? Is it possible, that the user also updates the items by removing one of the choices and the flow should also switch a yes back to no? This all is important to know.

[Matthy79]

This is how I would do it.

But as I said. Depending on the requirement you may have to add an additional step to "translate" the multichoices to the yes/no names and maybe check if the last update was from flow and of course if it is possible to remove a multichoice value later this also has to be modified.

[AMR1009]

At the moment, it is set up for manual updates. So, once my team receives notification that an upload has been completed, we go in and see who is marked with positive attendance and what was taught in the training session. Based on that, we put a checkmark under the corresponding column. Sounds simple, but when you're supporting 500+ associates, it can get messy and chaotic. lol

[AMR1009]

I created an additional test list to see which way would be the most effective to see the data in both formats, y/n columns as well as combined into one string.