Mastering Power Apps: How to Convert Strings to Operators for Formula Evaluations

In Power Apps, you may encounter an issue where values from a SharePoint list or another data source are interpreted as text, even though they represent operators. This can be problematic when you’re trying to evaluate formulas stored in a SharePoint list. For instance, I was determining product eligibility based on true or false evaluations of formulas stored in a SharePoint list. An example formula was TotalUnits >= %ofTotalInventory. For this tutorial, let’s use var1 = Total Units and var3 = %ofTotalInventory.

You might have tried using the Value() function to convert the operator from text to an actual operator, only to find it doesn’t work. After scouring the internet, you probably encountered two solutions: string interpolation or manually forcing the operator using ASCII.

Background Information:

• PowerApps String Interpolation and Operators
• Microsoft Documentation on Power Fx Operators

Many forums suggest using $"____" as a fix, but this only concatenates var1, var2, and var3 into a single string, such as “123 = 20”, rather than evaluating the expression. Another suggestion is using Char(61) to leverage PowerApps’ built-in ASCII compatibility, but this too fails, resulting in errors about “unexpected values”.

Solution

The most reliable solution I’ve found is to use a collection that functions as a table to store whether a function matches a specific operator combination. Here’s how you can do it:

1. Create a Collection to Store Operator Results:

   ClearCollect(colOperator,
   {
      greaterThan: false,
      equalTo: false,
      lessThan: false
   });

2. Write a Formula to Evaluate Variables Based on the Operator:

   If(
      var1 > var3,
         Update(colOperator, First(colOperator), {greaterThan: true}),
      var1 = var3,
         Update(colOperator, First(colOperator), {equalTo: true}),
      var1 < var3,
         Update(colOperator, First(colOperator), {lessThan: true})
   );

This formula updates the colOperator collection to reflect which condition is true. For instance, if var1 is 123 and var3 is 20, only greaterThan will be set to true.

3. Use the Collection to Determine True Cases:

   If(
      Lookup(colOperator, First(colOperator).greaterThan = true) || 
      Lookup(colOperator, First(colOperator).equalTo = true),
      true
   );

Based on this formula, PowerApps evaluates whether var1 is greater than or equal to var3. You can then add whatever function or code you want to execute when this If statement is true.

By using this method, you can effectively convert strings to operators in PowerApps, ensuring your formulas are evaluated correctly. This approach provides a robust solution for handling operator evaluations in the current version of PowerApps.