cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seraph787
Advocate III
Advocate III

Combo Box with Distinct Lookup Values

I have a table of employees.  Every employee has an ID.  Every employee has a supervisor and that supervisor field stores the ID of the supervising employee.

 

I have a combo box which I'd like to populate with the names of all employees who are supervising so I need ot to be distinct.  And I want the dropdown box to look up the distinct IDs of each supervising employee and show the user the full name of the supervisor in the combo box.  

 

So far this is what I have:

 

To accomplish each separately

 

Distinct(providerTable,supvID)

 

AddColumns(
    providerTable,
    "supvFullName",LookUp(
    providerTable,ID=supvID)

But I am not sure how to combine these make the Items field of my combo box show the fields from my table which are lastName &" "& firstName.  I tried the code below but it's not working.

 

Distinct(providerTable,AddColumns(providerTable,"supvFullName",LookUp(lastName,ID=supvID)))

Once I've done that, I know I'm going to need to be able to still have the combo box return the ID or supvID so I can filter my gallery by the supervising employee selected from the combo box.  

 

How do I do that?

1 ACCEPTED SOLUTION

Accepted Solutions
v-micsh-msft
Community Support
Community Support

So the ProviderTable contains two fields which relates to ID:

ID: the actual Employee ID,

supvID: the supervisor Employee ID,

 

Right?

 

If this it the case, then please try each of the following code with the items property of the ComboBox control:

First one:

Filter(ProviderTable, ID in Distinct(ProviderTable, supvID).Result)

Second One:

AddColumns(Distinct(ProviderTable, supvID), 
"supvFullName",
Lookup(ProviderTable,
ID=result,
lastName&" "&firstName))

If I understand your issue correctly, then both of the formulas would work.

 

Regards,

Michael

Community Support Team _ Michael Shao
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
Jeff_Thorpe
Super User
Super User

I have an idea that might help. I think you can filter the providerTable to show just the people that are supervising by being a little creative with the AddColumns, Lookup and Filter functions. This assumes every employee is listed only once on the providerTable.

 

I free handed this example, so I hope it isn't too buggy.

 

 

Filter(
    AddColumns(
        providerTable,
        "IsSupv",
        If(
            IsBlank(
                Lookup(
                    providerTable,
                    ID = supvID
                )
            ),
            "No",
            "Yes"
        )
    ),
    IsSupv = "Yes"
)

 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
v-micsh-msft
Community Support
Community Support

So the ProviderTable contains two fields which relates to ID:

ID: the actual Employee ID,

supvID: the supervisor Employee ID,

 

Right?

 

If this it the case, then please try each of the following code with the items property of the ComboBox control:

First one:

Filter(ProviderTable, ID in Distinct(ProviderTable, supvID).Result)

Second One:

AddColumns(Distinct(ProviderTable, supvID), 
"supvFullName",
Lookup(ProviderTable,
ID=result,
lastName&" "&firstName))

If I understand your issue correctly, then both of the formulas would work.

 

Regards,

Michael

Community Support Team _ Michael Shao
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

Thanks Jeff and Michael!

 

Jeff,

I couldn't get your first formula to work, but the second one worked great!

 

Here's what I ended up doing:

SortByColumns(
    AddColumns(
        Distinct(
            providerTable,
            supvID
        ),
        "supvFullName",
        LookUp(
            providerTable,
            ID=Result,
            firstName&" "&lastName
        ),
        "supvFirstName",
        LookUp(
            providerTable,
            ID=Result,
            firstName
        ),
        "supvLastName",
        LookUp(
            providerTable,
            ID=Result,
            lastName
        )
    
    ),
    "supvLastName"
)

Pardon my excitement, but this just made my day! 🙂

 

Thanks again!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (37,082)