Answered

People Picker - ComboBox - Items from a second list

(0) Share

Report

Posted on by crawlejg

Hello,

I have two SP lists: ListA and ListB. In List A, I have a column "AssignedTo" that is a person column. In List B, I have a column called 'Employee' (aslo a people field) and another column called "Available", which a text column containing either 'yes' or 'no'.

I want a form that will display/allow to edit the current record (Form1.Mode=edit) or to create a new record (Form1.Mode=new).

I can accomplish all of that easily enough. Where I am running into trouble is in displaying the 'available' people only in the combobox. In the Items property of the combobox, the default is Choices[@ListA].AssignedTo). But, that allows selection of any person in our Office365 group. If I change the items property to Choices[@ListB].Employeee, I still get everyone. I assume this is because both person columns are configured in SP to pick from our Office365 group.

I tried to filter the items property Filter(ListB,Available="yes").Employee but, that yields all blanks and the field editor choices are greyed out

If I leave off Employee, I still get just blanks in the combo box, but the field editor choices are no longer greyed out

I am able to get a list of people using Distinct, but then it is no longer a Person field, but rather a text field called 'Value'

I need to be able to patch the choice back to the datasource, so I need the person field.

Also, when I changed the Items property for the combobox from Choices([@ListA].AssignedTo), I no longer see the default value from the list. It only appears if I re-enter Choices([@ListA].AssignedTo) in the items property. The DefaultSelectedItems property is set to Parent.Default

I feel like I must be missing something simple and fundamental, but I am stuck. I have been working on this for several hours without making any progress. Any help would be greatly appreciated.

-crawlejg

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

v-hanytian-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @crawlejg ,

To solve the issue with your description, we created two Lists of SharePoint.

In order to display the Information of the Person, we write the formula into the “items” property: Distinct(Filter(ListB, Available = "Yes"), Employee.DisplayName)

Because we want to the default selected is the value of the current record, we write the formula into the “DefaultSelectedItems” property: ThisItem.AssginTo

Please Note that, we need to write Patch() Formula into the Button to apply the function.
Patch(
    ListA,
    LookUp(
        ListA,
        Title = DataCardValue4.Text
    ),
    {
        AssginTo: LookUp(
            ListB,
            Employee.DisplayName = ComboBox1.Selected.Value,
            Employee
        )
    }
);
Refresh(ListA)

There is the Process how it worked.

Then we will see it worked!

Hope this will help you!

Best Regards,
Tina

Was this reply helpful? Yes No
crawlejg 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Tina. I am still unable to see the value for the DefaultSelectedItem. I created a text box within the datacard to show that I can get the value, but I can't seem to get it to populate the combobox.

I noticed that the combobox choices are populated with text values (from Distinct). My original column type is 'person'. Might that be why I am having issues with ThisItem.AssignedTo (person) field vs the combobox items that are displaying a text field? I tried using ThisItem.AssignedTo.DisplayName, but I get the red squiggly underline indicating an error. Ideally, I would never change the field from a person to text because I will have to change it back to patch properly (I think I would achieve this using the Office365User.SearchUserV2 function and patching with the @OData.type and other fields (dept./claims/etc.) needed to patch a person column.)

-crawlejg

Was this reply helpful? Yes No
v-hanytian-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @crawlejg ,

Glad to hear from you. As a matter of fact, we have notice your original column type id ’Person’, and the data source we have create is also the same type.

However, if you insist on using a column of type Person, we will provide the second way.

You need to write formula into OnStart in the App.
ClearCollect(ListB_copy,ListB);
ClearCollect(ColAssgin,
            ForAll(ListB_copy As a,LookUp(ListB,Available = "Yes",a.Employee)),
            LookUp(ListA,DataCardValue1.Text = Title,AssginTo));

Then we need to set property of Combox .
DefaultSelectedItems: LookUp(ListA,DataCardValue1.Text = Title,AssginTo)
Items: If(LookUp(ListB,LookUp(ListA,DataCardValue1.Text = Title,AssginTo.DisplayName) = Employee.DisplayName,true),
    ForAll(ListB_copy As a,LookUp(ListB,Available = "Yes",a.Employee)),
    ColAssgin)

Let us check if it works.

This is the original data

This is the modified data.

It is worked.
Hope this will help you.

Best Regards,
Tina

Was this reply helpful? Yes No
crawlejg 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you, Tina.
To which data card is DataCardValue1.Text referring to in your solution? If the collections are being created in the OnStart property, there aren't really any data cards yet? It cannot be the datacardvalue for my combobox on the form because that yields an error. I am not using the "Title" field in my application. Would this instead be the "ID" field? If so, it limits my items to just one choice. Below are the OnStart, DefaultSelectedItems and Items properties in my app.

OnStart:
DefaultSelectedItems:
LookUp(ListA,DataCardValue39.Text = Title,AssignedTo)

Items:
If(LookUp(ListB,LookUp(ListA,DataCardValue39.Text = ID,AssignedTo.DisplayName) = Employee.DisplayName,true),
ForAll(colListB As a,LookUp(ListB,Available = "Yes",a.Employee)),colAssignChoices)

DataCardValue39 is the combobox. DataCardValue5 (not used) is the ID column. Both are in Form2 (datasource is List A).

Thank you,
crawlejg

Was this reply helpful? Yes No
v-hanytian-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @crawlejg ,

Glad to hear from you!

DataCardValue1.Text referring to in my solution is the Form connected to ListA, which control type is Combo Box.

The collections are being created in the OnStart property, you can Click “App” and “Run on Start” to make the data from two data source combined.

You mentioned that you are using the “ID” field in your app, you may change the “Title” into “ID” in the formula.

We found that the screenshot you offered have some error, may I ask the error message, so that we can solve the issue together.

Hope this will help you.

Best Regards,
Tina

Was this reply helpful? Yes No
crawlejg 56 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello!
The DataCardValue39 I used is pointed to the combobox on the form as you suggested.

The error is that ".Text" is not an available property for it.

That error causes the "=" to also have an error. The two errors are shown below:

I tried also using DataCardValue39.Selected.DisplayName (which is available), but it also gave an error.
Thanks again!
-crawlejg

Was this reply helpful? Yes No
Verified answer

v-hanytian-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @crawlejg ,

Glad to hear from you! Thank you for providing the error message.

May I ask whether this form display the column ID? If yes, change “DataCardValue39” into “Int(DataCardValuex.Text)”, the “DataCardValuex“ is the control of Showing ID.
DefulatSelectedItems: LookUp(ListA,Int(DataCardValue3.Text) = ID,AssginTo)

Items: If(LookUp(ListB,LookUp(ListA,Int(DataCardValue3.Text) = ID,AssginTo.DisplayName) = Employee.DisplayName,true),
ForAll(ListB_copy As a,LookUp(ListB,Available = "Yes",a.Employee)),
ColAssgin)

Hope this will help you.

Best Regards,
Tina

Was this reply helpful? Yes No
crawlejg 56 on at

Like
a
(2)

Report
Copy link

Link copied!

Thanks again Tina!
I could not get it to work exactly as you showed, but I was able to take from parts of what you created and make it work. I could not get the LookUp to work inside the ForAll. It always returned all of the items in List B, so I filtered the list first. Below is what I ended up with:

OnStart:
ClearCollect(ListB_copy,Filter(ListB,Available="yes"));
ClearCollect(ColAssignedTo,
ForAll(ListB_copy As a,a.Employee));

Items:
ColAssignedTo

DefaultSelectedItem:
LookUp(ListA,DataCardValue40.Text = ID,AssignedTo) -- DataCardValue40.Text is the ID column in the form.

Your help got me to a solution and I have marked your response as such. Thank you so much.
-crawlejg

Was this reply helpful? Yes No