Answered

Multi-select Combo Box with O365 people picker - not saving to SQL on-prem back end on update

(0) Share

Report

Posted on by CS-24071844-0

Hello!

I have a canvas app with an on-prem sql back end. On the EditForm of the app, there is a varcharMAX field with a combo box inserted into the DataCard. The data source of the combo box is O365.

The intent is for users to select multiple people and for the selections to save to sql. I can get the data to save for a single selection, but not for multiple.

Combo Box current settings:

Items: Office365Users.SearchUserV2({searchTerm:cmbRCAMtgInvitees.SearchText, top:10}).value

DefaultSelectedItems: RenameColumns(Split(ThisItem.RCA_x0020_Meeting_x0020_Invitees, ", "), "Result", "RCA_Invitees")

Data Source is Office365Users

Data fields Primary text is Mail, Secondary text is DisplayName, SearchField is Mail.

DataCard Update Setting:

If(!IsEmpty(cmbRCAMtgInvitees.SelectedItems), Left(Concat(cmbRCAMtgInvitees.SelectedItems, Trim(RCA_Invitees) &", "),(Len(Concat(cmbRCAMtgInvitees.SelectedItems, Trim(RCA_Invitees) &", "))-2) ))

Any ideas would be GREATLY appreciated!

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@CarrieSperls

Your DefaultSelectedItems property (DSI) formula is never going to provide the selections as your Items property if a table of records of Office365 Users...not a single column of RCA_Invitees!

Where is the column RCA_Invitees coming from?? It is not part of an Office365 user record!

Was this reply helpful? Yes No
CS-24071844-0 80 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes The column RCA_Invitees is coming from the on prem sql db. The idea is to search/select names using O365 then save the selected names to the RCA_Invitees column in sql.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@CarrieSperls

Understood, but your cmbRCAMtgInvitees Items property is:

Office365Users.SearchUserV2({searchTerm:cmbRCAMtgInvitees.SearchText, top:10}).value

Correct?

If so, then there is no RCA_Invitees column in that user record, and thus the formula in your update:

...Concat(cmbRCAMtgInvitees.SelectedItems, Trim(RCA_Invitees)...

Will not have that column in the SelectedItems!

This is also why I mentioned that your DSI will never work - because the User Record does not have an RCA_Invitees column, so it will never match.

What part of the Office365 User record do you want??

Was this reply helpful? Yes No
CS-24071844-0 80 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes
Thanks, Randy! I am trying to search by Mail (the user id assigned by my organization) and save the DisplayName from the User Record.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
@CarrieSperls

Then you will want to focus around that!

I would consider a potential flaw in your design first. You ware saying you are storing the DisplayName in your concatenated string in your column.

This means, for the DSI to work, it is going to need to re-lookup each person in that string by DisplayName.

If you have a potential for more than one user with the same name, then I would consider moving to storing by email address...as that will always be unique.

Otherwise, your formulas should be as follows:

Combobox Items:

Office365Users.SearchUserV2({searchTerm: Self.SearchText, top:10}).value

DefaultSelectedItems:

Filter( ForAll( Split(ThisItem.RCA_x0020_Meeting_x0020_Invitees, ", "), First(Office365Users.SearchUserV2({searchTerm: Result, top:1}).value) ), !IsBlank(DisplayName) )

DataCard Update Setting:

Concat(cmbRCAMtgInvitees.SelectedItems, Trim(DisplayName), ", ")

That will give you what you want.

Was this reply helpful? Yes No
CS-24071844-0 80 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes you...are...my...hero!! I agree with and implemented your redesign suggestion. THANK YOU to the moon and back!

Was this reply helpful? Yes No