Filter gallery based on global variable

[GraemeNZ]

Hi - I have an app which I want to use as a portal for guest users (customers) to access lists in our SharePoint site. I thought the app was working fine until I did the first set of user testing today.

I have two global variables (which work correctly). They identify if the user is an FIW Approver (in which case everything shows) or if the user is a customer they should only be able to see their own items. However, I have a gallery where the customer can see all items. Intuitively I know the code is wrong but I’m getting tangled in the syntax and can’t work out how to get it to work.

The incorrect code is this:

//If current user is approver show all

If(

    gvCurrentUser = "FIW Approver",

    SortByColumns(

        Search(

            If(

                IsBlank(cbCustFilter_Ing.Selected.Value),

                'Master Ingredients List',

                Filter(

                    'Master Ingredients List',

                    Customer.Value = cbCustFilter_Ing.Selected.Value

                )

            ),

            TxtIngFilter.Text,

            "Title"

        ),

        "Title",

        Ascending

    ),

//Else filter the data based on the customer logged in

    SortByColumns(

        Search(

            If(

                IsBlank(cbCustFilter_Ing.Selected.Value),

                'Master Ingredients List',

                Filter(

                    'Master Ingredients List',

                    Customer.Value = gvCustomer

                )

            ),

            TxtIngFilter.Text,

            "Title"

        ),

        "Title",

        Ascending

    )

)

I’ve tried playing around with the formula but I just keep getting lost in the syntax. 

In another gallery, this works correctly. If the current user is not an approver they only see their ingredients with expiring or expired Halal certs. This is how I know the gv's are working as intended. 

//If current user is approver show all, else filter the data based on the customer logged in

If(

    gvCurrentUser = "FIW Approver",

    SortByColumns(

        Filter(

            'Master Ingredients List',

            (HalalStatus.Value = "Expired" || HalalStatus.Value = "Expiring") && Halal.Value = "Yes"

        ),

        "Title"

    ),

    SortByColumns(

        Filter(

            'Master Ingredients List',

            (HalalStatus.Value = "Expired" || HalalStatus.Value = "Expiring") && Halal.Value = "Yes" && Customer.Value = gvCustomer

        ),

        "Title"

    )

)

Screenshot from user testing. The User is from Spring Sheep so should only be able to see Spring Sheep items in the gallery. They should still be able to search based on the text input of TxtIngFilter (ingredient name).

Can anyone help me?

GraemeNZ

[Ashwin7104]

Hey @GraemeNZ ,

Does using Item-Level permission address your problem as we're using SharePoint List as backend ?

Customers can only read and edit their own records once the settings are set, but admins (with cancel checkout permission) can view and edit all products. You can avoid writing more code in PowerApps by doing so.

https://techcommunity.microsoft.com/t5/sharepoint/list-item-level-permission/m-p/1793445 

Thanks.

[GraemeNZ]

hi @Ashwin7104 

My question is not about changing the whole permission structure - my question is about changing the syntax of the existing code. In plain language, this bit:

//Else filter the data based on the customer logged in

If a customer is logged on, sort the Master Ingredients List according to title, and only show the items where Customer.Value=gvCustomer, and still allow user to filter the list based on TxtIngFilter.

I tried to remove the IF formula but then I can only see ingredients that have no customer value entered, and I should be able to see everything because I am an FIW Approver...

I can't work out which part of my formula is wrong, or how to structure it. In some examples SortByColumns comes first, other time second - sometimes search is before filter, and other times filter is before search...

PS: Item-level permissions won't work for us as there are multiple customer users who need to be able to see/edit all their customer-items (not just their own), and all customer users are guests (external) users.

[BCBuizer]

Hi @GraemeNZ ,

Give this a go:

SortByColumns(
    Search(
		Filter(
			'Master Ingredients List',
			If(gvCurrentUser = "FIW Approver",
				If(IsBlank(cbCustFilter_Ing.Selected.Value),
					true,
					Customer.Value = cbCustFilter_Ing.Selected.Value
				),
				Customer.Value = gvCustomer
			)
		)
	),
    "Title",
    Ascending
)

[GraemeNZ]

Hi @BCBuizer 

Just to clarify - would you replace my whole code, or just the 2nd bit after my "//Else" comment?

Also I see the code to search based on the TxtIngFilter text input is missing.

[BCBuizer · Accepted solution]

Hi @GraemeNZ ,

Right on both: this is to replace the Items property of your gallery. I have inserted the text input:

SortByColumns(
    Search(
		Filter(
			'Master Ingredients List',
			If(
				gvCurrentUser = "FIW Approver",
				If(IsBlank(cbCustFilter_Ing.Selected.Value),
					true,
					Customer.Value = cbCustFilter_Ing.Selected.Value
				),
				Customer.Value = gvCustomer
			)
		),
		TxtIngFilter.Text,
        "Title"
	),
    "Title",
    Ascending
)

Basically this checks if the current user is an FIW apprower and then if a customer is selected. If the user is not an FIW approver, only gvCustomer is shown.

[GraemeNZ]

Hi - that works for me as Approver, I just need to schedule some time with a customer user to check. I'll keep you posted. 😊

[BCBuizer]

Hi @GraemeNZ ,

Glad this is working so far. Looking forward to hearing about the other tests.

Quick tip: you can, for testing purposes, add a button that sets value for gvCurrentUser and gvCustomer:

OnSelect = Set(gvCurrentUser,"not FIW approver"); Set(gvCustomer, "Spring Sheep")

This should only show the items for this particular customer.

Of course don't forget to remove the button before publishing 😉

[GraemeNZ]

A test button - perfect. That's exactly what I need. Your code works great thanks @BCBuizer