Left Function for choice column

[AuburnMist]

Hello,

I could really use some help with a syntax. 

I am trying to define a Left string (Left(String, NumberOfCharacters) for a choice column.

The support on Microsoft's page (Left, Mid, and Right functions in Power Apps - Power Apps | Microsoft Docs) describes how this is achieved with a Single string and a Single-column table, but not with a Choice column.

In my app, I have added 3 choices for a decision similar to:

1) Approved / Approuvé 

2) Not approved  / Non approuvé 

3) Returned / Retourné

To meet my department's language requirements, I had to make the choices bilingual. However, when I am sending out the email, I want to show the English texts first, and further down the email, the French texts. So, I need to split the choice answers into English and French for the email message (sent by SendMailV3).

I have tried this, but it is not working (I'm getting a red squiggly line under the code)

If(Left('PRDecisions.Cmb'.Selected.Value="✅ Approved by Peer Reviewer / Approuvé par examinateur pair", 27, 'PRDecisions.Cmb'.Selected.Value="❌ Not approved by Peer Reviewer / Non approuvé par examinateur pair",31, 'PRDecisions.Cmb'.Selected.Value = "⌛ Returned to GCP for modifications / Retourné au RSC pour modifications",35)

Can this be achieved? Any help would be appreciated. 

Thank you 🙂 

[RandyHayes]

@AuburnMist 

Yes, your syntax is not correct for the function.

The left function takes a string as the first parameter and the number of characters for the last parameter.  Your left function has nothing that resembles those parameters.

However, if your end goal is to extract the English and French from the values and this is all based on a Choice column, then I would simply grab the values to begin with in your app.

So, for example, in the OnStart, you could have the following formula:

Set(glbChoices,
    ForAll(Choices(yourDataSource.yourChoiceColumn),
        With({_text: Split(Value, "/")}
          {
            English: Trim(First(_text).Result),
            French: Trim(Last(_text).Result)
          }
        )
    )
)

This will then give you a table called glbChoices that you can use throughout your app that will contain both the language choices.

This will also give you the ability to make changes in the choices without having to alter your app.

I hope this is helpful for you.

[AuburnMist]

Hi, this sounds really great, but after I add this to OnStart and create the English and French tables, I'm not sure how to identify them in my SendMailV3 code. 

I would need to use the English here; 

"<br><b>Decision: </b>" &'PRDecisions.Cmb'.Selected.Value

and the French here:

"<br><b>Décision: </b>" &'PRDecisions.Cmb'.Selected.Value

Honestly, I'm a little concerned about this solution as it would mean that I would have to add a LOT of new code to my OnStart function. This is because in my organization (and in this app), there are up to 5 approval levels, each with 3 (or 4) decision choices. 

Instead, is there a solution to use Left and just count the characters in the string, like this? But based on the decision choice that is selected?

Single string

The examples in this section use a text-input control as their data source. The control is named Author and contains the string "E. E. Cummings".

SINGLE STRING Formula Description Result

Left( Author.Text, 5 )

Extracts up to five characters from the start of the string.

"E. E."

[RandyHayes]

@AuburnMist 

Sure, you can use the left function, but you will have a tremendous amount more work to do than simply grabbing the values and shaping them to the values you need.

It is much easier in PowerApps to shape data and use it in the app rather than "program" it (as it is not a development platform).  So, doing a lot "Left" functions and if statements is a lot of dev-type work.

If you are dealing with just choices, then this is all pretty easy to shape.

The OnStart was just a suggestion on where to capture it to use throughout the app, but really it can all come from the 'PRDecisions.Cmb' (and other) control.

As an example, I would assume your Items property on 'PRDecisions.Cmb' is Choices(yourDataSource.yourChoiceColumn)

So, if you change your Items property to the following:

ForAll(Choices(yourDataSource.yourChoiceColumn),
    With({_text: Split(Value, "/")}
      {
        Value: Value
        English: Trim(First(_text).Result),
        French: Trim(Last(_text).Result)
      }
    )
)

 Then in your email it becomes this:

English:

    "<br><b>Decision: </b>" &'PRDecisions.Cmb'.Selected.English

French:

    "<br><b>Décision: </b>" &'PRDecisions.Cmb'.Selected.French

[AuburnMist]

Ok! I'm going to go for it! You are right, it will save me a tremendous amount of work. 

So, I started to plug in the code in the Manager's decision column (Items property), but I encounter a teeny problem. Can you check and advise how to correct? 

[RandyHayes]

@AuburnMist 

Yes, please review the formula I sent you.  It is not the same.  You were close, but you omitted the ForAll part of the formula.

To recap based on what you have in the photo, the formula should be:

ForAll(Choices('Routing Slips'.'Manager''s Decision / Décision du qestionnaire'),
    With({_text: Split(Value, "/")}
      {
        Value: Value
        English: Trim(First(_text).Result),
        French: Trim(Last(_text).Result)
      }
    )
)

[AuburnMist]

Oh boy, now I've got this:

[AuburnMist]

Sorry, I mean this (I had a "q' instead of a "g"). But still, not working 😞 

[RandyHayes · Accepted solution]

@AuburnMist 

I see that I missed a comma (actually two of them).

Formula should be:

ForAll(Choices('Routing Slips'.'Manager''s Decision / Décision du gestionnaire'),
    With({_text: Split(Value, "/")},
      {
        Value: Value,
        English: Trim(First(_text).Result),
        French: Trim(Last(_text).Result)
      }
    )
)

[AuburnMist]

Amazing!! I just sent an email to myself and it works like a dream! You are a genius! Thank you for sparing me hours of development work! 👏 👏