Combobox to show data from 2 lists sharepoint

[Vinith]

Hi All,

Need Help to resolve below:

I have ID and Name fields both are combobox with type and auto select.

List-1 :Emp , List2: Empdetails

Both list have with same fields ID, Name

Combo1: ID_combo

Combo2: Name_Combo

If required we can change the columns name aswell its feaseble.

problem here is i have to search from two lists and populate distinct data in both ID,Name Combo box, If user selects/type ID then name should be auto poulated in Name_combo box with an option to select among them and also if selects/type Name from Name_combo then ID should be auto populated in ID_conbo box.

Data i have is :

Emp List:   ID          Name                    Empdetails:      ID Name

                  c123       pink                           c123                 Pink

                                                                  c123                Yellow

                                                                  c123                 blue

As it has multiple names so we should  have option to select Name among them for repective ID

I have tried with below code , But its correct or not am not sure

Collect(Onecolumn,ShowColumns(Emp,"Name"));Collect(Onecolumn,ForAll(Empdetails,{Name:'Name'}))

Getting below error:

Showcolumn name is not avaliable but name column already exists in sharepoint.

Any help would be highly helpful.

Thanks in advance

[v-yutliu-msft]

Hi @Vinith,

Do you want that the data in combo box1 is filtered according to the selection of combo box2 and the data in combo box2 is filtered according to the selection of combo box1?

As combo box1 is depent on combo box2, if combo box2 is depent on combo box1 too, then combo box1 is depent on itself. I'm afraid it's not suppoeted in PowerApps currently.

I suggest you add a combo box.

Make combo box2 filtered according to the selection of combo box1, combo box3 filtered according to the selection of combo box2.

I've made a similar test for your reference:

Set combo box1's Items: EmpList    Primary Text/Search Field: ID

Set combo boxx2's Items:Filter(Empdetails,ID in ComboBox1.SelectedItems.ID)   Primary Text/Search Field: Name

Set combo box3's Items: Filter(EmpList,Name in ComboBox2.SelectedItems.Name)  Primary Text/Search Field: ID

Then, if you select id in EmpList, combo box2 will display correspoding Name in Empdetails.

If you select name in Empdetails, combo box3 will display correspoding ID in EmpList.

Best regards,

Community Support Team _ Phoebe Liu

[Vinith]

@v-yutliu-msft 

Hi,

Thanks for providing the solution, I hope you didnt understand the requirement.Let  me explain again.

Datebase: sharepoint

Lists: Emp,Emp Details

Step1 : I have a combo box , User should be able to provide search with both ID, Name   from single combobox (single search with multiple columns) 

Step2 : for combobox i have 2 lists tobe searched in for ID & Name, i,e User should be able to search from 2 lists(Emp,Emp details), I might need to use Joins or lookup -  But in combobox user should be able to diplay either Id or Name from Both lists.

Ex:   Emp       ID          Name                              EmpDetails   ID             Name

                      C123     rishab                                                      c123      Rishab dr   

                      C123     rishab MBBS                                            c123       Rishab mbbs,MD

Like in above example i have 2 lists EMP, Empdetails

Both lists have same fields ID, Name

in combobox when user types C123 then user should have an option to select either of them(Rishab,Rishab dr,Rishab MBBS, Rishab MBBS, MD)

Thanks for your help in advance.

[v-yutliu-msft]

Hi @Vinith ,

Do you want to filter data according to two lists?

I suggest you save them to one collection.

Try this formula:

Collect(data,Emp);Collect(data,EmpDetails)

Then, data in the two lists will all be saved in the collection named data.

Set the combo box's Items: data

Best regards,

Community Support Team _ Phoebe Liu

[Vinith]

thanks @v-yutliu-msft 

can we able to combine the 2 columns in a list and show them in combobox?

empId   Emp Name     Output needed in combobox

 c123        pink                   c123-pink

 c456         blue                  c456-blue

---

@v-yutliu-msft wrote:

Hi @Vinith ,

Do you want to filter data according to two lists?

I suggest you save them to one collection.

Try this formula:

Collect(data,Emp);Collect(data,EmpDetails)

Then, data in the two lists will all be saved in the collection named data.

Set the combo box's Items: data

Best regards,

Community Support Team _ Phoebe Liu

---

[v-yutliu-msft · Accepted solution]

Hi @Vinith ,

Yes, I suggest you try the Concatenate function.

Try this formula:
ClearCollect(collection2,ForAll(collection1,Concatenate(empId,"-",Emp Name ))

Set the combo box's Items: collection2

Here's a doc about Concatenate for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-concatenate

Best regards,

Community Support Team _ Phoebe Liu