cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SharePointDev
Frequent Visitor

How to Lookup based Array of string in Power Apps

Hi All,
I'm stuck in a logic where I need to lookup a SharePoint List based on a string that have comma separated employeeIds.
Here is my requirement -
I have an employeemaster list in SharePoint where I have employee people picker and custom employeeId column and first name and last name columns.
In my application I have a combo box in which I'm binding EmployeeMaster list with FirstName and storing regarding employeeIds in comma separated strings in Azure Sql table. But while displaying on view screen I'm not able to query so that all the employees display with firstname.
To store Ids in Sql I'm using something like this -
On default property on textbox-
If(Form3.Mode=FormMode.View,Parent.Default,Concat(DataCardValue19.SelectedItems.'Employee ID',Concatenate(Text('Employee ID'),",")))
On datacard update property -
TextInput7.Text
On DefaultSelectedItems property of combobox -
Filter(EmployeeMasterData,'Employee ID' in ThisItem.StEmployeeID)
On view screen I'm using something like this 
LookUp(EmployeeMasterData,'Employee ID'in (ThisItem.StEmployeeID),'First Name')  Bur it is getting only one value.

Please assist how I can query all my EmployeeIDs and display all the names on View.


1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @SharePointDev ,

Do you mean that:

1)in employeemaster list, you have an employeeId column;

2)in Azure Sql table, you have a column to store selected ids;

3)use a combo box to display employeeId in employeemaster list, use a textinput to display selected ids?

If so, you should set like this:

In edit screen:

1)combo box's Items:

EmployeeMasterData

combo box's DefaultSelectedItems:

Filter(EmployeeMasterData,'Employee ID' in ThisItem.StEmployeeID)

2)textinput's Default:

If(Form3.Mode=FormMode.View,Parent.Default,
   Concat(DataCardValue19.SelectedItems,'Employee ID'&",")
   )

3)This datacard's Update:

TextInput7.Text

 

 In view screen:

Do you want to display all related first names?

If so, you could set one label's Text like this:

Concat(
       Filter(EmployeeMasterData,'Employee ID'in ThisItem.StEmployeeID),
       'First Name'&","
        )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
VijayTailor
Resident Rockstar
Resident Rockstar

 
v-yutliu-msft
Community Support
Community Support

Hi @SharePointDev ,

Do you mean that:

1)in employeemaster list, you have an employeeId column;

2)in Azure Sql table, you have a column to store selected ids;

3)use a combo box to display employeeId in employeemaster list, use a textinput to display selected ids?

If so, you should set like this:

In edit screen:

1)combo box's Items:

EmployeeMasterData

combo box's DefaultSelectedItems:

Filter(EmployeeMasterData,'Employee ID' in ThisItem.StEmployeeID)

2)textinput's Default:

If(Form3.Mode=FormMode.View,Parent.Default,
   Concat(DataCardValue19.SelectedItems,'Employee ID'&",")
   )

3)This datacard's Update:

TextInput7.Text

 

 In view screen:

Do you want to display all related first names?

If so, you could set one label's Text like this:

Concat(
       Filter(EmployeeMasterData,'Employee ID'in ThisItem.StEmployeeID),
       'First Name'&","
        )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

You're like an angel for me Thank you so much.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,203)