cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seraph787
Level: Powered On

LookUp into AddColumns

Hi I'm trying to allow a user to search my employee gallery by supervisor using a textbox.  However, my supervisor column just refers to the employee ID number.

 

I've tried to set up my gallery's Items property to add a column that looks up the supervisor ID in the employeeCollection but it's no working.  Here's what I've tried.

 

AddColumns(employeeCollection,"supvName", LookUp(employeeCollection, ID = supvID, firstName & " " & lastName))

 

But that doesn't work.  To test it, I created a label in the gallery and set it to "supvName" and it does show something, it shows the very first employee over and over as if that one employee is the supervisor for all employees (including themself).  My first instinct had been to put ThisItem.supvID instead of just supvID (like we do in a gallery label) but it won't accept ThisItem.  Any clues on how I can accomplish this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
cwebb365
Level 10

Re: LookUp into AddColumns

If you are trying to search for other columns, if your collection has the data then you just set your gallery items to something like this. You use Filter('yourcollection',StartsWith(SupervisorName,Textbox.Text))

 

Below is one I use for Inventory in a SharePoint list. StartsWith is for SharePoint, if you use SQL you can use other delegable data sources such as using in instead: Filter('yourcollection',SupervisorName in Textbox.Text)

 

SortByColumns(
Filter(
Inventory,
StartsWith(
FIIX_x0020_ID,
BrowseSearchBox.Text
) || StartsWith(
Serial_x0020_Number,
BrowseSearchBox.Text
) || StartsWith(
Model_x0020_Number,
BrowseSearchBox.Text
) || StartsWith(
Title,
BrowseSearchBox.Text
)
),
"Title",
If(
SortDescending1,
Descending,
Ascending
)
)

 

4 REPLIES 4
cwebb365
Level 10

Re: LookUp into AddColumns

Does the Employee Gallery use the employeeCollection to populate it? 

seraph787
Level: Powered On

Re: LookUp into AddColumns

Yes.

Highlighted
cwebb365
Level 10

Re: LookUp into AddColumns

If you are trying to search for other columns, if your collection has the data then you just set your gallery items to something like this. You use Filter('yourcollection',StartsWith(SupervisorName,Textbox.Text))

 

Below is one I use for Inventory in a SharePoint list. StartsWith is for SharePoint, if you use SQL you can use other delegable data sources such as using in instead: Filter('yourcollection',SupervisorName in Textbox.Text)

 

SortByColumns(
Filter(
Inventory,
StartsWith(
FIIX_x0020_ID,
BrowseSearchBox.Text
) || StartsWith(
Serial_x0020_Number,
BrowseSearchBox.Text
) || StartsWith(
Model_x0020_Number,
BrowseSearchBox.Text
) || StartsWith(
Title,
BrowseSearchBox.Text
)
),
"Title",
If(
SortDescending1,
Descending,
Ascending
)
)

 

Super User
Super User

Re: LookUp into AddColumns

Hi @seraph787,

It looks like you're using a Sharepoint list and I'm assuming a Lookup or Choice type column for Supervisor.  If this is correct, you can use a dropdown control to filter the employees by supervisor. I did a quick test on a list of mine that contains a lookup column (Categories). The Dropdown control Items property is 

Distinct(Products,Category.Value)

and the Items property of the Gallery is 

SortByColumns(
    Filter(
        Products,
        StartsWith(
            Title,
            TextSearchBox1.Text
        ) || Category.Value = Dropdown1.Selected.Value
    ),
    "Title",
    If(
        SortDescending1,
        Descending,
        Ascending
    )
)

In the example below, the search results in either the name specified in the TextInput box or the category in the dropdown.  Please note that the column name for a lookup type column must include the .Value or it won't work.

Capture5.PNG

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

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

Top Kudoed Authors
Users Online
Currently online: 207 members 4,680 guests
Please welcome our newest community members: