Dropdown with filtered lookup field

[Jeff_P]

Hi,

Here is my problem :

I have a list (app_users) of users with application profiles (administrator, supervisor, user and whatever). Each user can have multiple profiles. So a user can appear in the list multiple time with a different profile.

profile is a lookup field based on a list (app_profile).

appuser is a person field

I'd like to make a dropdown where a user can select one of its profiles

I searched for a while and tried many different things without success.
I tried these without success :

Filter(app_users;appuser.Email=User().Email).profile (works great as items property in a DataTable rendering box if I remove the other fields)

Filter(Choices(app_users.profile); Value in Filter(app_users;appuser.Email=User().Email).profile)

I think it's because Filter(app_users;appuser.Email=User().Email).profile return a table  with multiple fields instead of 1 but I can't find a solution.

[WarrenBelz]

Hi @Jeff_P ,

An unusual one, but I think this is what you need

Filter(
   Choices(app_users.profile); 
   Value in 
   Filter(
      app_users;
      appuser.Email = User().Email
   ).profile.Value
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Jeff_P]

Thank your for your help, unfortunately it doesn't work.

When I try 

Filter(Choices(app_users.profile); Value in Filter(app_users;appuser.Email=User().Email).profile)

I get an error on Value and power apps says it can not convert this Text to Record

With your proposal
Filter(Choices(app_users.profile); Value in Filter(app_users;appuser.Email=User().Email).profile.Value)
I get an error on the second Value and power Apps says that the name isn't valid and "Value" not recognized.

[WarrenBelz · Accepted solution]

@Jeff_P ,

Try this

Filter(
   Choices(app_users.profile); 
   Value in 
   Filter(
      AddColumns(      
         app_users;
         "ProfileVal";
         profile.Value
      );
      appuser.Email = User().Email
   ).ProfileVal
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Jeff_P]

@WarrenBelz are you magician or kind of ?

just a typo in the AddColumns but it works great !

AddColumns(      
         app_users;
         "ProfileVal";
         profile.Value
      )

Thank you so much.

[WarrenBelz]

@Jeff_P ,

Typo fixed (hazards of free-typing code) - also one of the many reasons I do not use Complex field types (Lookup and Person never, Choice only when I want to store multiple values) - you will find many fewer limitations and workarounds.

[Jeff_P]

It seems that it can be easier with you method and avoid all the choice stuff

Filter(
        AddColumns(
             app_users;
             "ProfileVal";
             profile.Value
        );
        appuser.Email=User().Email
    ).ProfileVal

[WarrenBelz]

@Jeff_P ,

Yes, but if Profile was a Text field (and you simply coded the choices into the Items of the dropdown), you would not need the AddColumns. You could also Sort on the field in a Delegable manner (which you cannot do with Choice columns) and would not have quite a few other workarounds to contend with.