Power Apps: Handling Dynamic Data Stored as Strings in SharePoint

Scenario:

Let’s imagine the client maintains a SharePoint list of available office furniture products. Each item is available in a variety of colors. And those color options vary from one item to another – tables are available in brown, black, or white… chairs are available in brown, black, blue, or gray… and so on.

A Choice Column won’t work since the options aren’t consistent across the entire list – they vary from item to item.

Let’s also imagine there are other limitations and considerations that we have to contend with:

• The client needs to manage the list via a canvas app
• The client needs to maintain a count of how many products they have in each color
• The solution must use ONLY the client’s existing SharePoint “Products” list. No new lists may be created to manage color options, inventory, etc.
• The client also does not want to add new columns to the list for tracking color variants (i.e. – a “Brown” column or “Blue” column, etc.). The solution must use ONLY the existing multi-line text column named ‘Available Colors’
• The ‘Available Colors’ column currently stores the color options and counts as a string in the following format:
  • “Brown (2), Blue (3), Green (4)”

Solution:

This is a solution based on one I recently used in a similar real-world scenario. If you want to recreate this scenario, create a SharePoint list called Products with a Title column and a multi-line text column called ‘Available Colors’. Then create a blank canvas app in PowerApps and connect it to the SP list. Then follow along below.

Canvas App Components:

Controls

• A text label called label_Products
• A text label called label_AvailableColors
• A dropdown input called dd_ProductName
• A vertical gallery called gallery_ProductColors
  • A text label called label_ColorVariant
  • A button called button_Add
  • A button called button_Remove
  • A rectangle called separator

Context Variables

• selectedProduct – Text
• availableColors – Table
• selectedColorRecord – Record
• newColorCounts – Text

Collections

• coll_ProductColors

Implementation:

Set the Items property of our product dropdown, dd_ProductName, to the Products SP list

Next we create a formula in the OnChange property of dd_ProductName that will pull the text string from the ‘Available Colors’ column for the selected product and break it down into a collection containing each color name and count for the selected product.

//Data will be added to coll_ProductColors within a ForAll loop below... since we can't use ClearCollect inside a ForAll loop, we must clear the collection here
Clear(coll_ProductColors);

//Store the selected product name in selectedProduct. Look up the selected product in the Products list and get the 'Available Colors' string and Split it at each space. Assign the resulting table to a context variable named availableColors
UpdateContext(
    {
        selectedProduct: Self.SelectedText.Value,
        availableColors: Split(
            LookUp(
                Products,
                Title = Self.SelectedText.Value
            ).'Available Colors',
            " "
        )
    }
);
//Use the Sequence function to create a table of sequential numbers that correspond to the index of each item in the availableColors table.
//Loop through all the resulting numbers (using the As operator to name each record Ndex)
//Use the Mod function to determine if each Ndex value is odd or even. 
//If odd, add a new record to the collection coll_ProductColors assigning the current (odd) record's value to the property "name" and then assign the immediately following (even) record's value to the property "count"
//The strings from each even-numbered record in availableColors contain the counts for each color. This must be converted to a numerical value.
//The count string also contains a comma and the numerical digit(s) are inside parentheses so we must use the Substitute function to remove the comma, the Value function to convert the text to a numerical value, and the Abs function to make certain the resulting number is not negative
ForAll(
    Sequence(CountRows(availableColors)) As Ndex,
    If(
        Mod(
            Ndex.Value,
            2
        ) = 1,
        Collect(
            coll_ProductColors,
            {
                name: Index(
                    availableColors,
                    Ndex.Value
                ).Result,
                count: Abs(
                    Value(
                        Substitute(
                            Index(
                                availableColors,
                                Ndex.Value + 1
                            ).Result,
                            ",",
                            ""
                        )
                    )
                )
            }
        )
    )
);

The first thing we do in the OnChange formula is Clear the coll_ProductColors collection. We need the collection to be cleared every time this formula executes. Since data will be added to it within a ForAll loop later, and the ClearCollect function can’t be used within a loop, we have to clear it here.

Next we store the selected product name in a context variable called selectedProduct. [This will be important later]

Then we look up the product in the Products list by its Title and get the value inside its ‘Available Colors’ column. That value is a string formatted like this “Brown (1), Black (2), Blue (2)”. It lists the color options by name followed by a space followed by the quantity in parentheses. And each color variant/count are separated by a comma and a space.

We use the Split function to split the string at each space. This creates a table with each color name and count in a separate row. We then store the table in a variable called availableColors.

Next, we need to loop through every item in the availableColors table, determine whether it contains the color name or the count (based on its index/position in the table), then assign it to the appropriate property in a collection called coll_ProductColors.

Inside our ForAll loop, we get the total number of items in the availableColors table using the CountRows function. Then we use the Sequence function to create a table of sequential numbers (1,2,3…) that correspond to each record in the availableColors table. This table of numbers created by the Sequence function is what we actually loop through. We use the As operator to name each of those records “Ndex”. We then use each Ndex value from the Sequence function to target the items in the availableColors table by their index using the Index function.

We use the Mod function to determine if the Ndex value is even or odd. Mod returns the remainder after a number is dividied by a divisor. We divide each Ndex value by 2, if the remainder is 1 it’s an odd numbered index otherwise, if remainder is 0, it’s an even numbered index.

We do this because we know that the color names are all at odd-numbered indices in the table and the count of each color is in the immediately following record (even-numbered indices).

So, if the Ndex value is odd, we target that item in the table using the Index function and assign that value to the property “name”. We then target the record immediately following it (Ndex.Value + 1) and assign that value to the property “count”. These values are added to the collection coll_ProductColors.

The “name” property should contain a string with only the name of the color. That is what we want, so no cleanup needed there. BUT each of the “count” values will initially contain strings with the numerical digits encased in parentheses and also the commas used to separate each color variant group. We need the count value to be a number, but we need to perform some cleanup on this string before we can convert it to a numerical value.

The Value function will not work with the commas in the string. So first, we will remove the commas with the Substitute function. The Value function will also interpret the parentheses as indicating a negative number so we need to remove the negative sign using the Abs function which returns the absolute value.

We should now have a collection named coll_ProductColors that has a column for the color name (a string with the name only) and a column for the count (a positive numerical value). It should should look like this…

At this point, we realize there’s going to be a problem we need to fix. The dd_ProductName dropdown loads with the the first product in the list already selected. BUT we need its OnChange function to execute to get everything set up properly. So, we want the app to start with no product selected and force the user to have to initially select a product.

To do this, first we have to set the dd_ProductName’s AllowEmptySelection property to true. And then we set its Default property to an empty string "". [More on this later]

Now we need a gallery to display the color options and counts for the selected product. The gallery should also have buttons for adding or subtracting from the color counts.

The gallery_ProductColors Items property should be set to the collection coll_ProductColors.

The gallery should include a text label, label_ColorVariant, with its Text property set to
ThisItem.name & " (" & ThisItem.count & ")"

The gallery should include an Add button and a Remove button. And a rectangle used as a separator.

Let’s deal with the Add button first.

In the Add button’s OnSelect property we use this formula…

UpdateContext(
    {
        selectedColorRecord: LookUp(
            coll_ProductColors,
            name = ThisItem.name
        )
    }
);

Patch(
    coll_ProductColors,
    selectedColorRecord,
    {count: selectedColorRecord.count + 1}
);

UpdateContext(
    {
        newColorCounts: Match(
            Concat(
                coll_ProductColors,
                name & " (" & count & "), "
            ),
            "^(?<trim>.*), $"
        ).trim
    }
);

Patch(
    Products,
    LookUp(
        Products,
        Title = dd_ProductName.SelectedText.Value
    ),
    {'Available Colors': newColorCounts}
);

We lookup the gallery’s currently selected record in the collection coll_ProductColors and store the record to a context variable called selectedColorRecord. This prevents us from having to do two redundant lookups in the next step.

Next we patch the selectedColorRecord in coll_ProductColors and add 1 to its count property.

Now we need to update the ‘Available Colors’ for the selected product in the Products list in SharePoint. BUT first we must get the updated available colors from the coll_ProductColors collection and turn it back into a string.

Remember the format for the string needs to be the color name followed by the count encased in parentheses and each color option separated by a comma and space – “Brown (1), Black (2), Blue (2)”

We use the Concat function to convert the values in all the records in coll_ProductColors into a string. We start the string with the name property followed by a space and open parenthesis followed by the count property followed by a closing parenthesis and comma and space.

BUT there’s a problem with this. It puts an unwanted comma and space at the end of the string. So we use the Match function with a regular expression to target the comma and space at the end of the string and trim it.

We store this finished string to a context variable called newColorCounts. This makes the next lines of code easier to read.

Next, we patch the Products list in SharePoint and update the ‘Available Colors’ column with newColorCounts.

I did this directly in the Add button because that’s what the client wanted. You could also add a confirmation step here and have the Add button open a dialogue asking the user to confirm that they want to update the Products list and then have another button to actually patch the list.

Next, the Remove button’s OnSelect property should be a copy of the formula from the Add button but with one little change. Instead of adding 1 to the count property, it should subtract 1.

UpdateContext(
    {
        selectedColorRecord: LookUp(
            coll_ProductColors,
            name = ThisItem.name
        )
    }
);

Patch(
    coll_ProductColors,
    selectedColorRecord,
    {count: selectedColorRecord.count - 1}
);

UpdateContext(
    {
        newColorCounts: Match(
            Concat(
                coll_ProductColors,
                name & " (" & count & "), "
            ),
            "^(?<trim>.*), $"
        ).trim
    }
);

Patch(
    Products,
    LookUp(
        Products,
        Title = dd_ProductName.SelectedText.Value
    ),
    {'Available Colors': newColorCounts}
);

There’s still one more issue we need to handle.

We set the Default property of dd_ProductName to an empty selection and allowed empty selections because we wanted the app to start with no product selected.

The problem is that now anytime we update the values while in the app, it will refresh that control and set it back to an empty selection. We don’t want this. We only want it to be empty when the app first loads and we want it to retain the user’s selection from then on while the app is in use.

This is why we need to store the selected product in the variable selectedProduct and we need to go back and add some logic to the formula in the Default property for dd_ProductName…

If(IsBlank(selectedProduct), "", selectedProduct)

This will set the default to an empty selection ONLY if the selectedProduct is blank. Otherwise it sets the default to selectedProduct. This will make it default to an empty selection initially but to the most recently selected product from then on.

And that should complete this solution…

Summary:

We now have an app that uses the simple string ‘Available Colors’ from the Products list in SharePoint to display and manage the different color options for each product. It allows the user to easily maintain the count of each color variant. And the solution did not create any new lists or columns.

Here’s a breakdown of the Power FX code in the app…

Clear(coll_ProductColors);

UpdateContext(
    {
        selectedProduct: Self.SelectedText.Value,
        availableColors: Split(
            LookUp(
                Products,
                Title = Self.SelectedText.Value
            ).'Available Colors',
            " "
        )
    }
);

ForAll(
    Sequence(CountRows(availableColors)) As Ndex,
    If(
        Mod(
            Ndex.Value,
            2
        ) = 1,
        Collect(
            coll_ProductColors,
            {
                name: Index(
                    availableColors,
                    Ndex.Value
                ).Result,
                count: Abs(
                    Value(
                        Substitute(
                            Index(
                                availableColors,
                                Ndex.Value + 1
                            ).Result,
                            ",",
                            ""
                        )
                    )
                )
            }
        )
    )
);

UpdateContext(
    {
        selectedColorRecord: LookUp(
            coll_ProductColors,
            name = ThisItem.name
        )
    }
);

Patch(
    coll_ProductColors,
    selectedColorRecord,
    {count: selectedColorRecord.count + 1}
);

UpdateContext(
    {
        newColorCounts: Match(
            Concat(
                coll_ProductColors,
                name & " (" & count & "), "
            ),
            "^(?<trim>.*), $"
        ).trim
    }
);

Patch(
    Products,
    LookUp(
        Products,
        Title = dd_ProductName.SelectedText.Value
    ),
    {'Available Colors': newColorCounts}
);

UpdateContext(
    {
        selectedColorRecord: LookUp(
            coll_ProductColors,
            name = ThisItem.name
        )
    }
);

Patch(
    coll_ProductColors,
    selectedColorRecord,
    {count: selectedColorRecord.count + 1}
);

UpdateContext(
    {
        newColorCounts: Match(
            Concat(
                coll_ProductColors,
                name & " (" & count & "), "
            ),
            "^(?<trim>.*), $"
        ).trim
    }
);

Patch(
    Products,
    LookUp(
        Products,
        Title = dd_ProductName.SelectedText.Value
    ),
    {'Available Colors': newColorCounts}
);