cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gaëlle
Level 8

Working with ComboBox multiselect and transforming strings

Hello everyone !

I think the heat wave from last week just got my last neurons...
I know I can figure how to work with ComboBox and multiselect, but I can't put my fingers on it !

 

So here is the deal :

I have a Power Application, running on On-Prem SQL server datasource.

In this PA, I have a Form filled by a user. In this form, I have a Datacard called "Persons in Charge". This DataCard will use Office365Users to get the coworkers of the current user.

This DataCard contains a ComboBox (Single Layout) whith the following properties:

 

ComboBox1.SearchFields=["DisplayName"]
ComboBox1.PrimaryText=["DisplayName"]
ComboBox1.Items = Office365Users.DirectReports(myManagerProfile.Id);
ComboBox1.OnChange = Set(Responsables,Responsables&ComboBox1.Selected.Id&";")

 

Once the user clicks on "Validate", the SQL table is filled with Persons in Charge = id1;id2;id3 etc...

 

That is fine by me.

Now, I have two silly questions for you :

  1. I would like to display the Persons in charge Display Names instead of their ID in the View Form.
    •  I have to edit the string and replace every ID by the real name.
    •  I know I should use split to get each id, but it will create a Table. I don't really know what to do with this.
      Split(ThisItem.Responsable,";").Result gets me the IDs. But I don't know what to do.. a ForAll? A Text ?
  2. I would like to display the Persons in charge Display Names as default on my ComboBox if the user is editing the EditForm .
    •  I have no idea how i could do that...

Do you have any idea how I could achieve these goals ?

 

Thanks you very much !

Have a nice day

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

Thanks for your time @strategery !
But i could make it work (and did not take the time to answer my own question).

Here are the specificities of my ComboBox "PersonsInCharge_Cbbx" :

PersonsInCharge_Cbbx.Items = Office365Users.DirectReports(myManagerProfile.Id)

PersonsInCharge_Cbbx.SearchFields = ["DisplayName"]
PersonsInCharge_Cbbx..DefaultSelectedItems =
If(
   CreateEditFormCM.Mode = FormMode.New,
   [],
   //Search if there are emails stored in the Parent.Default
   If(
      Not(
         IsBlank(
            Find(
               "@",
               Parent.Default
             )
         )
   ),
   /*Spliting the string of emails into an array and replace the email address by the  DisplayName for each one.*/
      ForAll(
         Split(
            Parent.Default,
            ";"
         ),
         First(Office365Users.SearchUser({searchTerm: Result})).DisplayName
         )
     )
)

Here is the Update of my DataCard PersonsInCharge_DataCard1 :

PersonsInCharge_DataCard1.Update =
    Replace(
        Concat(
            PersonsInCharge_Cbbx.SelectedItems,
            ";" & Mail
        ),
        1,
        1,
        ""
    )


I hope this will help someone !

strategery
Level 8

Re: Working with ComboBox multiselect and transforming strings

I believe you are correct in assuming the DefaultSelectedItems is not quite right.  Try dropping off the ".DisplayName" inside your ForAll and see if that helps.  I think what's happening is you're providing a list of "DisplayName" when DefaultSelectedItems needs records that match those of "Items".

 

Your combobox should show the names of selected items within it or "12 Items Selected" (for example) if it can't fit them all in the space.

Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

This thread is a mess because of me, so here is the full solution, thanks to @strategery who helped me a big lot on this !

 

The context :

I wanted to display the coworkers of the current user in a combo box control. When the current user selects one/several coworkers and hit "Save", the datas will be stringified into a SQL onPrem data table.
When editing a record, the DefaultSelectedItems property from the combo box control should be set to the datas already saved (as a string, little reminder)

How to Get the woworkers : Use Office365Users connector to get the current user Manager's ID and then get the coworkers with the ReportsTo.

 

The formulas :
At the beginning of the app, on the first screen, setting myManagerProfile as following :

Set(
   myProfile,
   Office365Users.MyProfile()
);
Set(
   myManagerProfile,
   Office365Users.Manager(myProfile.Id)
)

Create an EditForm with your datasource.
And in the DataCard with your "PersonsInCharge", fill these properties as below :
(it will store your email as the following : ;User1@domain.com;User2@domain.com)

 

PersonsInCharge_DataCard.Default = ThisItem.PersonsInCharge
PersonsInCharge_DataCard.Update = Replace( Concat( ComboBox_Ctrl.SelectedItems, ";" & Mail ), 1, 1, "" )


Then, create a ComboBox Control and set its properties to :

 

 

 

 

ComboBox_Ctrl.Items = Office365Users.DirectReports(myManagerProfile.Id)
ComboBox_Ctrl.DefaultSelectedItems =
If(
    CreateEditForm.Mode = FormMode.New,
    Blank(),
    If(
        Not(
            IsBlank(
                Find(
                    "@",
                    Parent.Default
                )
            )
        ),
        ForAll(
            Filter(
                Split(
                    Parent.Default,
                    ";"
                ),
                !IsBlank(Result)
            ),
            First(Office365Users.SearchUser({searchTerm: Result}))
        )
    )
)

And finally set your Save button as following :

If(
    CreateEditForm.Mode = FormMode.New,
    SubmitForm(CreateEditForm),
    Patch(
        '[dbo].[MyDataTable]',
        First(
            Filter(
                '[dbo].[MyDataTable]',
                Id = Value(myID_DataCard.Text)
            )
        ),
        CreateEditForm.Updates
    );
);
ResetForm(CreateEditForm)


I hope this could help some of you !

Thanks again to @strategery, without whom I could not have completed this !

 

6 REPLIES 6
strategery
Level 8

Re: Working with ComboBox multiselect and transforming strings

This gets complicated, and I'm not sure the specifics of your form and function, but maybe this will help!

To address #1:

Concat(Split(ThisItem.Responsable,";"),Office365Users.UserProfile(Result).DisplayName & Char(10))

Breakdown:

  • Concat takes a table and concatenates records according to expression you enter
  • "Result" here is representative of each record returned by the Split function, so it takes each record and performs a UserProfile lookup to get the DisplayName, then appends Char(10) to it (a new line)
  • You could easily replace Char(10) with any separator you want, e.g. "; " or ", "

To address #2:

I don't fully understand what you're trying to do but I think you can do what you're asking with ShowColumns and correctly setting the DataCard properties.

I highly recommend this video by @Shanescows

https://www.youtube.com/watch?v=jmh8eeQVAIc

It covers SQL relationships and forms, but the methods should easily translate to your setup and help you set up the form combobox, it's extremely helpful and informative.

Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

Thanks for your time @strategery !
But i could make it work (and did not take the time to answer my own question).

Here are the specificities of my ComboBox "PersonsInCharge_Cbbx" :

PersonsInCharge_Cbbx.Items = Office365Users.DirectReports(myManagerProfile.Id)

PersonsInCharge_Cbbx.SearchFields = ["DisplayName"]
PersonsInCharge_Cbbx..DefaultSelectedItems =
If(
   CreateEditFormCM.Mode = FormMode.New,
   [],
   //Search if there are emails stored in the Parent.Default
   If(
      Not(
         IsBlank(
            Find(
               "@",
               Parent.Default
             )
         )
   ),
   /*Spliting the string of emails into an array and replace the email address by the  DisplayName for each one.*/
      ForAll(
         Split(
            Parent.Default,
            ";"
         ),
         First(Office365Users.SearchUser({searchTerm: Result})).DisplayName
         )
     )
)

Here is the Update of my DataCard PersonsInCharge_DataCard1 :

PersonsInCharge_DataCard1.Update =
    Replace(
        Concat(
            PersonsInCharge_Cbbx.SelectedItems,
            ";" & Mail
        ),
        1,
        1,
        ""
    )


I hope this will help someone !

Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

Hi @strategery ,

My solution is partly wrong, because when I edit an already existing record, my DefaultSelectedItems display in the right way, but if the user update other fields (like Description or else), without re-selecting any Items, hey are not taken into account when the user saves the record (with a submitForm).

He has to re-select all the persons in charge. And that is quite difficult. I know I should have done something wrong with the DefaultSelectedItems, but I can't see what.

 

Could you help me on that please ?
Thanks !
PS, @v-xida-msft  if you could give a little help on this please Smiley Wink

 

 

strategery
Level 8

Re: Working with ComboBox multiselect and transforming strings

I believe you are correct in assuming the DefaultSelectedItems is not quite right.  Try dropping off the ".DisplayName" inside your ForAll and see if that helps.  I think what's happening is you're providing a list of "DisplayName" when DefaultSelectedItems needs records that match those of "Items".

 

Your combobox should show the names of selected items within it or "12 Items Selected" (for example) if it can't fit them all in the space.

Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

Thank you so much @strategery ; I did not even think of that .DisplayName !
Works like a charm now!

Last question on this though :
Apparently

Reset[@PersonsInCharge_Cbbx]

does Reset the Selected Items, but not the DefaultSelectedItems. Even though I have specified in my DefautlSelectedItems the following formula :

If(
    CreateEditFormCM.Mode = FormMode.New,
    [],//Do everything to populate the default selected items with the parents
)

I can't empty it, and strangely If i've just edited an item and reseted the form, if I open it as a new form, the defaultselecteditems does not take that into account...

Thanks again for your help, but could you give me a bit more hand on this please ?

 

----- EDIT ------
Found the solution to my last problem !!!
instead of [], I had to put Blank() ! and this is working perfectly !

Gaëlle
Level 8

Re: Working with ComboBox multiselect and transforming strings

This thread is a mess because of me, so here is the full solution, thanks to @strategery who helped me a big lot on this !

 

The context :

I wanted to display the coworkers of the current user in a combo box control. When the current user selects one/several coworkers and hit "Save", the datas will be stringified into a SQL onPrem data table.
When editing a record, the DefaultSelectedItems property from the combo box control should be set to the datas already saved (as a string, little reminder)

How to Get the woworkers : Use Office365Users connector to get the current user Manager's ID and then get the coworkers with the ReportsTo.

 

The formulas :
At the beginning of the app, on the first screen, setting myManagerProfile as following :

Set(
   myProfile,
   Office365Users.MyProfile()
);
Set(
   myManagerProfile,
   Office365Users.Manager(myProfile.Id)
)

Create an EditForm with your datasource.
And in the DataCard with your "PersonsInCharge", fill these properties as below :
(it will store your email as the following : ;User1@domain.com;User2@domain.com)

 

PersonsInCharge_DataCard.Default = ThisItem.PersonsInCharge
PersonsInCharge_DataCard.Update = Replace( Concat( ComboBox_Ctrl.SelectedItems, ";" & Mail ), 1, 1, "" )


Then, create a ComboBox Control and set its properties to :

 

 

 

 

ComboBox_Ctrl.Items = Office365Users.DirectReports(myManagerProfile.Id)
ComboBox_Ctrl.DefaultSelectedItems =
If(
    CreateEditForm.Mode = FormMode.New,
    Blank(),
    If(
        Not(
            IsBlank(
                Find(
                    "@",
                    Parent.Default
                )
            )
        ),
        ForAll(
            Filter(
                Split(
                    Parent.Default,
                    ";"
                ),
                !IsBlank(Result)
            ),
            First(Office365Users.SearchUser({searchTerm: Result}))
        )
    )
)

And finally set your Save button as following :

If(
    CreateEditForm.Mode = FormMode.New,
    SubmitForm(CreateEditForm),
    Patch(
        '[dbo].[MyDataTable]',
        First(
            Filter(
                '[dbo].[MyDataTable]',
                Id = Value(myID_DataCard.Text)
            )
        ),
        CreateEditForm.Updates
    );
);
ResetForm(CreateEditForm)


I hope this could help some of you !

Thanks again to @strategery, without whom I could not have completed this !

 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 219 members 2,494 guests
Please welcome our newest community members: