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

User filtering in Drop downs with If Commands

Hi, 

 

I have a list with the following 

Jcurtis260_0-1670576641494.png

I need a drop-down menu in Powerapps to go

 

Look up user full name - If the title is Service Delivery Manager - Show all users in his region. If not, service delivery manager but field manager - show only users who report to himself from the list using the field manager column. 

 

Currently, I have it set up for Field Manager only using the below command : 

Filter('Engineer List', 'Field Manager' = User().FullName)

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Jcurtis260 ,

Firstly, the error was simply a missing comma (now fixed - I thought you might have picked that up), but this is not the solution now I see your current posted code. You now (as I suspected) are filtering on a second list 'Timesheet-List' based on the result of the user's role in the 'Engineering List'. To get this Delegable, you need to get all the If() statements out of the primary filter and into separate queries (hence the With() statement). I am making some assumptions here as I cannot see any examples of your data - these are: -

  • The Title field of 'Engineer List' contains the user's Full Name on all records
  • The EngineerName of 'Engineer List' is the Service Delivery Manager Full Name
  • The 'Field Manager' of 'Engineer List' is the Field Manager Full Name
  • You have a field in 'Timesheet-List' with the User's Full Name

This is still difficult to keep Delegable (due to the second filter being one-to-many) , so I have also kept this to the newest (Delegation limit) records with the Status of "Submitted"

With(
   {
      wUser:
      LookUp(
         'Engineer List',
         EngineerName = User().FullName
      ),
      wFM:
      Filter(
         'Engineer List',
         'Field Manager' = User().FullName
      )
   },
   With(
      {
         wList:
         Filter(
            Sort(
               'Timesheet-List',
               ID,
               Descending
            ),
            Status = "Submitted"
         )
      },
      If(
         !IsBlank(wUser.EngineerName),
         Filter(
            wList,
            Region = wUser.Region
         ),
         Filter(
            wList,
            YourUserNameField in wFM.Title
         )
      )
   )
)

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

 

View solution in original post

7 REPLIES 7
v-jefferni
Community Support
Community Support

Hi @Jcurtis260 ,

 

I don't understand what's the use of the Dropdown. What is the Items property of Dropdown? Without Dropdown, please try below formula in Items of Gallery, and this will be a user role based filtering:

 

Filter('Engineer List', If(LookUp('Engineer List', EngineerName = User.FullName).Title = "Service Delivery Manager", Region = LookUp('Engineer List', User.FullName = EngineerName).Region , LookUp('Engineer List', EngineerName = User.FullName).Title = "Field Manager", 'Field Manager' = User().FullName, true)

 

 

Best regards,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Thank you @v-jefferni  - Works. I do get a warning. What's the best way to get rid of this warning? 

 

Jcurtis260_0-1670579428076.png

 

Hi @Jcurtis260 ,

 

Is there any non-Text column in your list?

 

If none, try below formula instead:

If(
    LookUp(
        'Engineer List', 
        EngineerName = User.FullName
    ).Title = "Service Delivery Manager", 
    Filter(
        'Engineer List',  
        Region = LookUp('Engineer List', User.FullName = EngineerName).Region
    ), 
    LookUp(
        'Engineer List', 
        EngineerName = User.FullName
    ).Title = "Field Manager", 
    Filter(
        'Engineer List', 
        'Field Manager' = User().FullName, 
    ),
    true
)

 

Best regards,

 

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Hi, 

 

Yes i have none text columns, The first solution you provided fixed it however I get a warning it might not work with large amounts of data. How much would large amounts of data be? 

 

The reason I am using drop downs is that on the gallery I have a filter for two drop downs 1. Date range 2. Engineer.

Hi @Jcurtis260 ,

This should take care of the Delegation issue, but you have not included the code for the date range. Also is all this kept in the one list or are the roles in another list ?

With(
   {
      wName:
      LookUp(
         'Engineer List', 
         EngineerName = User().FullName
      )
   },
   Switch(
      wName.Title,
      "Service Delivery Manager",
      Filter(
         'Engineer List',  
         Region = wName.Region
      ), 
      "Field Manager",
      Filter(
         'Engineer List', 
         'Field Manager' = User().FullName, 
      ),
      'Engineer List'
   )
)

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

Hi Warren, 

 

Thank you for replying, unfortunately, I get received 4 expected 2 arguments. All the roles sit in the Engineer list which I am using to determine who can view what depending on title. Unfortunately, I can't limit the view on SharePoint as the list will need to be edited depending on the job title. 

 

Currently, the code I am using is below, however, I still get delegation warnings:

SortByColumns(
    Filter(
        'Timesheet-List',
        If(
            LookUp(
                'Engineer List',
                EngineerName = User().FullName
            ).Title = "Service Delivery Manager",
            Region = LookUp(
                'Engineer List',
                User().FullName = EngineerName
            ).Region,
            LookUp(
                'Engineer List',
                EngineerName = User().FullName
            ).Title = "Field Manager",
            Title = User().FullName,
            true
        ),
        If(
            Status = "Submitted",
            Status
        )
    ),
    "field_2"
)

  

Hi @Jcurtis260 ,

Firstly, the error was simply a missing comma (now fixed - I thought you might have picked that up), but this is not the solution now I see your current posted code. You now (as I suspected) are filtering on a second list 'Timesheet-List' based on the result of the user's role in the 'Engineering List'. To get this Delegable, you need to get all the If() statements out of the primary filter and into separate queries (hence the With() statement). I am making some assumptions here as I cannot see any examples of your data - these are: -

  • The Title field of 'Engineer List' contains the user's Full Name on all records
  • The EngineerName of 'Engineer List' is the Service Delivery Manager Full Name
  • The 'Field Manager' of 'Engineer List' is the Field Manager Full Name
  • You have a field in 'Timesheet-List' with the User's Full Name

This is still difficult to keep Delegable (due to the second filter being one-to-many) , so I have also kept this to the newest (Delegation limit) records with the Status of "Submitted"

With(
   {
      wUser:
      LookUp(
         'Engineer List',
         EngineerName = User().FullName
      ),
      wFM:
      Filter(
         'Engineer List',
         'Field Manager' = User().FullName
      )
   },
   With(
      {
         wList:
         Filter(
            Sort(
               'Timesheet-List',
               ID,
               Descending
            ),
            Status = "Submitted"
         )
      },
      If(
         !IsBlank(wUser.EngineerName),
         Filter(
            wList,
            Region = wUser.Region
         ),
         Filter(
            wList,
            YourUserNameField in wFM.Title
         )
      )
   )
)

 

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

 

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.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

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 (4,616)