cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShadowTech
Post Prodigy
Post Prodigy

Filter gallery by profile location

Hello,

 

I'm trying to filter a gallery by the users (User accessing the app) Office location and looks in the Title field for the first three digits. The first two characters determine office location.

 

example:

02-00002  (Fresno)

03-00002  (Oakland)

 

Basically if the user profile office location is Fresno, then it will show only records with "02-"

 

Onstart of app

Set(
    varUserEmail,
    User().Email
);
Set(
    varUser365Record,
    Office365Users.UserProfileV2(varUserEmail)
)

 

Gallery

If(varUser365Record.officelocation = "Fresno",
Title = "02-")

 

I know I have something wrong here. Any help is much appreciated.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

@ShadowTech ,

You know your model better than me obviously - you could do a Collection at App OnStart like this

ClearCollect(
    colCode,
    {
        Loc: "Fresno",
        Code: "02-"
    },
    {
        Loc: "Oakland",
        Code: "03-"
    },
    {
        Loc: "San Diago",
        Code: "04-"
    }
)

for the 12 areas and then

With(
   {
      wCode:
      LookUp(
         colCode
         Loc=var365Record.OfficeLocation
      ).Code
   },
   Sort(
      Search(
         Filter(
            AddColumns(
               AllBranches,
               "CreateBy",
               'Created By'.DisplayName
            ),
            (
               StatusDropdown.Selected.Value = "All" ||
               Status.Value = StatusDropdown.Selected.Value
            ) &&
            (
               DispostionDropdown.Selected.Value = "All" ||
               Disposition.Value = DispostionDropdown.Selected.Value
            ) &&
            (
               !RushOTCheckbox.Value ||
               'Rush Overtime'
            ) &&
            (
               !QuoteReqdCheckbox.Value ||
               'Quote Req''d'
            ) &&
            If(
               ButtonVar = 1,
               'Created By'.Email = User().Email
            ) &&
            StartsWith(
               Title,
               wCode
            )
         ),
         SearchBar.Text,
         "Title",
         "CompanyName",
         "CustomerContact",
         "Tag_x0023_",
         "CreateBy"
      ),
      Created,
      Descending
   )
)

 

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.

 

View solution in original post

13 REPLIES 13
WarrenBelz
Super User III
Super User III

Hi @ShadowTech ,

Close

If(
   varUser365Record.officelocation = "Fresno",
   StartsWith(Title, "02-")
)

 

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.

@WarrenBelz  tried it alone in the gallery and it works great. Based on my current gallery, what would be your recommendation for placement. I think I might be doubling up as well in the gallery. Any suggestions would be welcome.

 

Sorry for not uploading this in the beginning.

 

The two variables are filtering based on button presses.

 

Sort(
    If(
        ButtonVar = 1,
        Filter(
            Search(
                AddColumns(
                    AllBranches,
                    "CreateBy",
                    'Created By'.DisplayName
                ),
                SearchBar.Text,
                "Title",
                "CompanyName",
                "CustomerContact",
                "Tag_x0023_",
                "CreateBy"
            ),
            If(
                StatusDropdown.Selected.Value = "All",
                true,
                Status.Value = StatusDropdown.Selected.Value
            ),
            If(
                DispostionDropdown.Selected.Value = "All",
                true,
                Disposition.Value = DispostionDropdown.Selected.Value
            ),
            If(
                RushOTCheckbox.Value = true,
                'Rush Overtime',
                RushOTCheckbox.Value = false
            ),
            If(
                QuoteReqdCheckbox.Value = true,
                'Quote Req''d',
                QuoteReqdCheckbox.Value = false
            ),
            'Created By'.Email = User().Email
        ),
        ButtonVar = 2,
        Filter(
            Search(
                AddColumns(
                    AllBranches,
                    "CreateBy",
                    'Created By'.DisplayName
                ),
                SearchBar.Text,
                "Title",
                "CompanyName",
                "CustomerContact",
                "Tag_x0023_",
                "CreateBy"
            ),
            If(
                StatusDropdown.Selected.Value = "All",
                true,
                Status.Value = StatusDropdown.Selected.Value
            ),
            If(
                DispostionDropdown.Selected.Value = "All",
                true,
                Disposition.Value = DispostionDropdown.Selected.Value
            ),
            If(
                RushOTCheckbox.Value = true,
                'Rush Overtime',
                RushOTCheckbox.Value = false
            ),
            If(
                QuoteReqdCheckbox.Value = true,
                'Quote Req''d',
                QuoteReqdCheckbox.Value = false
            )
        ),
        Filter(
            Search(
                AddColumns(
                    AllBranches,
                    "CreateBy",
                    'Created By'.DisplayName
                ),
                SearchBar.Text,
                "Title",
                "CompanyName",
                "CustomerContact",
                "Tag_x0023_",
                "CreateBy"
            ),
            If(
                StatusDropdown.Selected.Value = "All",
                true,
                Status.Value = StatusDropdown.Selected.Value
            ),
            If(
                DispostionDropdown.Selected.Value = "All",
                true,
                Disposition.Value = DispostionDropdown.Selected.Value
            ),
            If(
                RushOTCheckbox.Value = true,
                'Rush Overtime',
                RushOTCheckbox.Value = false
            ),
            If(
                QuoteReqdCheckbox.Value = true,
                'Quote Req''d',
                QuoteReqdCheckbox.Value = false
            )
        )
    ),
    Created,
    Descending
)

 

Thanks again for your assistance.

@ShadowTech ,

Yes it is always good to post the whole problem as many responders need to decide if they can give the matter proper attention at the time. Firstly, I cannot imagine you are going top hard-code all the area codes, so you will need a list of users and their area codes. Assuming you are calling this AreaList and you have two fields - the user's email UserMail and the prefix (02-) in AreaCode, you would do something like this. I have condensed your code a lot as you had unnecessary repetition - also ButtonVar of 2 was the same as no ButtonVar.

With(
   {
      wCode:
      LookUp(
         AreaList,
         UserMail=User().EMail
      ).AreaCode
   },
   Sort(
      Search(
         Filter(
            AddColumns(
               AllBranches,
               "CreateBy",
               'Created By'.DisplayName
            ),
            (
               StatusDropdown.Selected.Value = "All" ||
               Status.Value = StatusDropdown.Selected.Value
            ) &&
            (
               DispostionDropdown.Selected.Value = "All" ||
               Disposition.Value = DispostionDropdown.Selected.Value
            ) &&
            (
               !RushOTCheckbox.Value ||
               'Rush Overtime'
            ) &&
            (
               !QuoteReqdCheckbox.Value ||
               'Quote Req''d'
            ) &&
            If(
               ButtonVar = 1,
               'Created By'.Email = User().Email
            ) &&
            StartsWith(
               Title,
               wCode
            )
         ),
         SearchBar.Text,
         "Title",
         "CompanyName",
         "CustomerContact",
         "Tag_x0023_",
         "CreateBy"
      ),
      Created,
      Descending
   )
)

If you get any errors, note this was free-typed so please have a good look for the issue before coming back and remember it involves the new list I mentioned.

 

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.

@WarrenBelz I get what your saying but just to clarify, these are not area codes. These are Office location numbers (about 12 total). So I would think it makes more sense to place those in the formula then have a list of a few hundred users. Also having to edit the list ever time someone comes and goes will not be sufficient. That's why I am looking at the Office365UserProfile for location and matching it up to the Title field.

 

These numbers are RMA numbers. the two digits before the dash designate the office location.

 

Your thoughts?

@ShadowTech ,

You know your model better than me obviously - you could do a Collection at App OnStart like this

ClearCollect(
    colCode,
    {
        Loc: "Fresno",
        Code: "02-"
    },
    {
        Loc: "Oakland",
        Code: "03-"
    },
    {
        Loc: "San Diago",
        Code: "04-"
    }
)

for the 12 areas and then

With(
   {
      wCode:
      LookUp(
         colCode
         Loc=var365Record.OfficeLocation
      ).Code
   },
   Sort(
      Search(
         Filter(
            AddColumns(
               AllBranches,
               "CreateBy",
               'Created By'.DisplayName
            ),
            (
               StatusDropdown.Selected.Value = "All" ||
               Status.Value = StatusDropdown.Selected.Value
            ) &&
            (
               DispostionDropdown.Selected.Value = "All" ||
               Disposition.Value = DispostionDropdown.Selected.Value
            ) &&
            (
               !RushOTCheckbox.Value ||
               'Rush Overtime'
            ) &&
            (
               !QuoteReqdCheckbox.Value ||
               'Quote Req''d'
            ) &&
            If(
               ButtonVar = 1,
               'Created By'.Email = User().Email
            ) &&
            StartsWith(
               Title,
               wCode
            )
         ),
         SearchBar.Text,
         "Title",
         "CompanyName",
         "CustomerContact",
         "Tag_x0023_",
         "CreateBy"
      ),
      Created,
      Descending
   )
)

 

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.

 

View solution in original post

@WarrenBelz This is what I have.

 

OnStart

 

Set(
    varUserEmail,
    User().Email
);
Set(
    varUser365Record,
    Office365Users.UserProfileV2(varUserEmail)
);ClearCollect(
    colCode,
    {
        Loc: "Fresno",
        Code: "08-"
    },
    {
        Loc: "Tulare",
        Code: "06-"
    },
    {
        Loc: "Bakersfield",
        Code: "17-"
    },
    {
        Loc: "Redding",
        Code: "07-"
    },
    {
        Loc: "Boise",
        Code: "21-"
    },
    {
        Loc: "Salt Lake City",
        Code: "03-"
    },
    {
        Loc: "Denver",
        Code: "10-"
    },
    {
        Loc: "San Diego",
        Code: "09-"
    },
    {
        Loc: "Emeryville",
        Code: "01-"
    },
    {
        Loc: "Stockton",
        Code: "04-"
    },
    {
        Loc: "Modesto",
        Code: "18-"
    },
    {
        Loc: "Walnut",
        Code: "12-"
    },
    {
        Loc: "Phoenix",
        Code: "11-"
    },
    {
        Loc: "Yuba City",
        Code: "02-"
    },
    {
        Loc: "Livermore",
        Code: "06-"
    }
)

 

 

 

 

 

 

 

Gallery

 

With(
   {
      wCode:
      LookUp(
         colCode,
         Loc=varUser365Record.officeLocation
      ).Code
   },
   Sort(
      Search(
         Filter(
            AddColumns(
               AllBranches,
               "CreateBy",
               'Created By'.DisplayName
            ),
            (
               StatusDropdown.Selected.Value = "All" ||
               Status.Value = StatusDropdown.Selected.Value
            ) &&
            (
               DispostionDropdown.Selected.Value = "All" ||
               Disposition.Value = DispostionDropdown.Selected.Value
            ) &&
            (
               !RushOTCheckbox.Value ||
               'Rush Overtime'
            ) &&
            (
               !QuoteReqdCheckbox.Value ||
               'Quote Req''d'
            ) &&
            If(
               ButtonVar = 1,
               'Created By'.Email = User().Email
            ) &&
            StartsWith(
               Title,
               wCode
            )
         ),
         SearchBar.Text,
         "Title",
         "CompanyName",
         "CustomerContact",
         "Tag_x0023_",
         "CreateBy"
      ),
      Created,
      Descending
   )
)

 

 

 

 

 

 

 

Its working but with a couple issues. The data wont be filtered until I click the "ButtonVar". Actually no data is presented until the "ButtonVar is clicked".

 

There are three buttons total that provide different states. (From left to right) The first presents stats on office location, the second provides stats for the user accessing the app and filters the gallery per what they have created and the third provides stats for the user who created a record when selected. The data only appears if I click the profile button (ButtonVar =1)first. The other issue which I didn't foresee is that the office location still gives the stats of all records submitted a s a whole instead of by office location which is needed.

 

Basically when it does filter the gallery it displays only what I have created not the branch as a whole.

 

This is the formula for the label presenting the submitted records for the branch. Not sure how to modify this.

 

If(BranchStatsButton,CountRows(AllBranches)

 

 

 

 

Not sure if something  like this would work? Just for testing but give a zero value.

 

If(BranchStatsButton,CountRows(Filter(AllBranches, Title = "06-"))

 

 

 

Any suggestions on why this is happening?

 

status01.jpg

 

It looks as if the gallery is only filtering by this, not the collection:

 If(
               ButtonVar = 1,
               'Created By'.Email = User().Email
            ) &&
            StartsWith(
               Title,
               wCode
            )
         )

 

Thanks again for your help.

@ShadowTech ,

Replace the code piece with this (the middle bit)

(
   !QuoteReqdCheckbox.Value ||
   'Quote Req''d'
) &&
(
    ButtonVar <> 1 ||
    'Created By'.Email = User().Email
) &&
StartsWith(
   Title,
   wCode
)

I will deal with the other question after we have solved your original (expanded) question.

You might also consider dealing with this thread as well.

 

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.

 

 

 

 

@WarrenBelz Awesome! That resolved the filtering issue. Also I have accepted your attachments solution on the other thread. Working well by the way.

 

I think the "submitted" and "Status" labels showing the filtered data source is all that's left.

 

Sample below of Status filed labels.

 

Unopened:

If(BranchStatsButton,CountRows(Filter(AllBranches, Status.Value="Unopened"))

 

Thanks again.

@ShadowTech ,

I am not exactly sure what you are doing here, but your first piece should be (note you were missing closing brackets on both)

If(
   BranchStatsButton,
   CountRows(
      Filter(
         AllBranches, 
         StartsWith(
            Title,
            "06-"
         )
      )
   )
)

and the other one is OK except for the bracket (although a different criteria)

If(
   BranchStatsButton,
   CountRows(
      Filter(
         AllBranches, 
         Status.Value="Unopened"
      )
   )
)

Are you simply displaying the number of matching records for these criteria?

 

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,091)