Solved: Transform string to operators ("Or" or "And")

mrQ · ‎05-28-2020

I have a string that looks something like this (it's all dynamically merged together from other data sources):

"hello {Contains} bleh {Or} Project {Equals} meh"

Is there a (formula-)way to transform the logical operators, such as {Contains}, {Or}, {Equals} to actual operators so it can be interpreted by Powerapps?

Kind regards,

mrQ · ‎06-12-2020

Thought I'm going to update this thread with the workaround I came up with.

In the end, I triggered a flow that triggers a PowerShell script with Azure Runbook.

This is the script:

Param
(
    # name
    [Parameter (Mandatory= $true)]
    [String] $name,

    # type
    [Parameter (Mandatory= $true)]
    [String] $type,

    # template name
    [Parameter (Mandatory= $false)]
    [string] $templateName,

    # module settings site
    [Parameter (Mandatory= $true)]
    [string] $moduleSettingsSite

)
[OutputType([int])]

# connect
$cred = Get-AutomationPSCredential -Name 'My Service Account'
Connect-PnPOnline -Url $moduleSettingsSite -Credential $cred



#Connect-PnPOnline -Url $moduleSettingsSite -UseWebLogin
$policyList = Get-PnPListItem -List "Policies"

foreach ($item in $policyList) {
    
    $conditionString = $item.FieldValues.condition
    $conditionString = $conditionString.Substring(0,$conditionString.Length-1)
    $conditionString = $conditionString -replace "{Name}", $name
    $conditionString = $conditionString -replace "{Type}", $type
    $conditionString = $conditionString -replace "{Name}", $templateName

    $curResult= $false
    $lastResult=$null
    $operator=$null;
    $conditions = ($conditionString).split(';')
    foreach($condition in $conditions)
    {
        $steps = $condition.split("#")
    
        if($steps[1] -eq "{Contains}")
        {
            $steps[2] = $steps[2] -replace "{", '*'
            $steps[2] = $steps[2] -replace "}", '*'
            $curResult = $steps[0] -like ($steps[2])
        }
        else{
            $steps[2] = $steps[2] -replace "{", ''
            $steps[2] = $steps[2] -replace "}", ''
            $curResult = $steps[0] -eq $steps[2]
        }
        if($operator -ne $null)
        {
            if($operator -eq "{Or}")
            {
                $lastResult = $lastResult -or $curResult
            }
            else{
                $lastResult = $lastResult -and $curResult
            }
        }
        else
        {
            $lastResult =$curResult
        }
        if($steps.length -gt 3)
        {
            $operator = $steps[3] 
        }    
    }
    If($lastResult) {
        $policyID =  $item.Id
        #Write-Output $policyID | ConvertTo-JSON
        return $policyID | ConvertTo-JSON
    }
}

This will return the policyID which is exactly what I was looking for.

Disadvantages of this workaround are the costs (although runbooks are VERY cheap), as well as the time before a script runs, as Azure Runbooks have their own queue-interval. So it might take up to 5mins until I get that policy back. In my case this was an acceptable solution.

I hope this helps someone.

Cheers

View solution in original post

iAm_ManCat · ‎05-28-2020

Hey @mrQ

You could use the Substitute() function to do this, examples available here in the documentation,

Let me know if you get stuck, happy to help further,

Cheers,

ManCat

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

mrQ · ‎05-28-2020

Hi @iAm_ManCat

Thanks for your quick answer.

Substitute results in a string.

E.g.

// Label5_7 = "hello {Contains} bleh {Or} Project {Equals} meh"
Substitute(Label5_7.Text,"{Contains}","in")

Just gives me another string without the logical operators, that is:

"hello in bleh {Or} Project {Equals} meh"

If there would be a function to convert text into operators, that'd be it. For values that would be Value(LabelXY.Text).

iAm_ManCat · ‎05-28-2020

Ouch ok, then I'm afraid you are looking at some more involved code with loads of conditionals (one for each of the 3 {} functions, then within that once for each of the possible operators. To my knowledge there's no easy way to convert text into an actionable operator.

I think that personally I would control this sort of thing with dropdowns and then conditional logic to choose which operators to use based on the text value of the dropdowns.

Out of interest, how are you intending on using this?

Cheers,

Sancho

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

mrQ · ‎05-28-2020

@iAm_ManCat
Thanks again for your quick answer.

I'm willing to put in the work. Could you elaborate more on how you would approach this?

The reason I'm asking for this is because I'm building a "policy builder" to be used for an internal system. People need to be able to set their own conditions. This policy builder is saving those values into a collection. There might be several policies in a list, each containing those conditions (in a form of a simple string, that's why I opened this post). The string looks like the one I've mentioned above and I already used Substitute to replace all the curly brackets. However, in order to put this whole string into an if function, I need to convert those operators 😕

iAm_ManCat · ‎05-28-2020

Ok, if it's an open-ended list of queries then I'm not sure how you'd accomplish that. I was thinking if it was just the three operators you could write conditional checks against the chosen text and then perform specific operations based on that, but open-ended query I'm not so sure how I would do that or whether that is even possible.

@PaulD1 @timl have you come across anything similar before?

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

timl · ‎05-28-2020

Thanks for the mention @iAm_ManCat 🙂

I did something that was shockingly almost identical to this, but I did it with angular js rather than Power Apps. I think this would definitely be tough in Power Apps.

@mrQ - what's your target data source? SharePoint, SQL, or CDS? Would you be open to the idea of writing your own web service/custom connector to carry this out?

mrQ · ‎05-28-2020

@timl

Thanks for your response and thanks @iAm_ManCat to tag "your" guys 🙂

My data source is SharePoint. I'd rather not use a web service/custom connector to do this. Mainly because of licensing/costs and lack of knowledge too 🙂

PaulD1 · ‎05-28-2020

Hmmm... How about building up the query string in OData format and passing that through to a Flow? I think you could then use the string as the filter argument of a 'Get Rows' for the applicable data source. Two cons of this approach are that I think you would need premium licensing for the http return action (to get the result data back into PowerApps in a usable fashion) and you have the added complexity of the 'external' Flow to manage.

Not quite the same thing, but might help with some ideas...

I inherited an App which has a lot of user-query options/permutations and for which performance was poor so I needed to make delegable (SQL data source). There are multi-select combo boxes (e.g. Zone and Status) - the user can select from multiple zones and statuses (e.g. Zone1, Zone2, Status1, Status2) and the filter should return any records where (Zone = Zone1 Or Zone = Zone2) And (Status = Status1 or Status = Status2) - part of the solution I came up with is below.

The Zones and Statuses selected are put into a text string separated with commas, the formula then uses FirstN, Last and Split to check the elements and make them part of the filter. The Coalesces substitutes an 'X' (which I know is not in any of the valid values), so if there are only two zones specified (for example) the solution still works.

It isn't open ended (below you can select up to 4 zones and 7 statuses - I found that if there were too many permutations the formula became non-delegable) but can be mixed with Filter and Search and still be delegable. Below is just a snippet of the code - I have to check for all combinations of whether Zone, Status and Lead are populated and have a similar block of code. It may be possible to make the filter more compact but nesting too many IFs made it non-delegable... it works and I don't want to touch it any further 🙂

Search is a useful operator as it will ignore a blank predicate, so you can assign a text value to a label and use that in the Search. If you don't actually need to limit your returned results by the search, just make sure the label's text value is blank. This can save nesting a few levels of IF. Note that Search is non-delegable for SharePoint and if you try to use Conditional (IF, Switch) statement for the predicate that will also make it non-delegable, hence having to put the conditional as the Text property of a label and then reference the label.

mrQ · ‎05-28-2020

@PaulD1

Thanks for your response.

I was thinking about using flow for that too, but decided against it as I can't dynamically build a condition. Flow conditions/expressions need the operator to be before the actual values. E.g.

if(
    or(
        equals('TestTitle','bleh'),
        and(
            equals('TestType','duh'),
            contains('TestTemplatetype','type')
        )
    ),
    true,
    false
)

That makes it impossible to build it dynamically.

However, I wasn't thinking about odata and query it then. How would you do that in flow? Never heard of building conditions with odata before.

Regarding licensing: As long as I can patch something into a SharePoint list and have flow check that SharePoint list, I don't need licenses... that's the preferred way.

In the end, I just need the ID of the policy that matches the condition to be written into a SP list, that's all 🙂