cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Formula not working when adding permission filter in

I have a gallery that is currently using the below formula allowing me to select between different items depending on their status, as well as the ability to search through the search bar. There is also a filter in place, meaning that the user will only be able to view the form responses that they submitted.

Search(
AddColumns
Filter(
Filter('Hire Authorisation Submit', Email = User().Email
),

_requestTypeFilter = "All" || Status.Value = _requestTypeFilter
),

"Field5Text",Department.Value
),
'Hire Authorisation Search Bar_1'.Text,

"field_6","field_7","field_8","field_9","field_4","Field5Text"
)

This formula was working correctly before I added in the permissions filter, however, now when selecting between the options; all, pending, approved, declined (as shown below) the all button/option pulls through no data. The permission filter work's fine by itself, it's when adding it within the other formula's where it doesn't pull through correctly. 

The error message I’m receiving with the formula is also in the image below, do you know the amends I need to make to this formula, meaning it’ll work correctly across each status option, please?

Data source: 'Hire Authorisation Submit'
Search bar name: 'Hire Authorisation Search Bar_1'
Column within the data source for email field: ‘email’ / “field_3”

 

CamArmstrong_0-1644410049815.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Super User
Super User

Hi @Anonymous 

 

I assume _requestTypeFilter is empty. So I updated the filter now with that assumption.

 

Search(
    AddColumns(
        Filter(
            Filter('Hire Authorisation Submit',
                 email = User().Email
            ),
           IsBlank(_requestTypeFilter) || _requestTypeFilter = "All" || Status.Value = _requestTypeFilter
        ),
        "Field5Text",Department.Value
    ),
    'Hire Authorisation Search Bar_1'.Text,
    "field_6","field_7","field_8","field_9","field_4","Field5Text"
)

 

View solution in original post

6 REPLIES 6
StalinPonnusamy
Super User
Super User

Hi @Anonymous 

 

Set the App Onstart Property to

Set(Userinfo, User())

 

Use the email from user info in the filter query

Search(
AddColumns
Filter('Hire Authorisation Submit',
Email = UserInfo.Email,
(_requestTypeFilter = "All" || Status.Value = _requestTypeFilter)
),
"Field5Text",Department.Value
),
'Hire Authorisation Search Bar_1'.Text,
"field_6","field_7","field_8","field_9","field_4","Field5Text"
)

 

If this does not works then share your previous query that was working.


Thanks,
Stalin - Learn To Illuminate

 

Anonymous
Not applicable

Hi @StalinPonnusamy, thanks for the response, really appreciate the help! Unfortunately, this still hasn't worked. Using your code:

 

 

Search(
AddColumns(
Filter(
Filter('Hire Authorisation Submit',
Email = Userinfo.Email,
(_requestTypeFilter = "All" || Status.Value = _requestTypeFilter)
),
"Field5Text",Department.Value
),
'Hire Authorisation Search Bar_1'.Text,
"field_6","field_7","field_8","field_9","field_4","Field5Text")

 

 

CamArmstrong_0-1644419715058.png

 

Data's not pulling through. I've tried using the permissions filter without any additional to see if this works: Filter('Hire Authorisation Submit',
Email = Userinfo.Email)

 

However, no data pulls through.

 

The initial formula I was using without permissions was (ticket I made for this is: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-add-permissions-on-a-gallery/m-p/1447...😞 

 

Search(AddColumns(

Filter('Hire Authorisation Submit',_requestTypeFilter = "All" ||

Status.Value = _requestTypeFilter),"Field5Text",Department.Value),

'Hire Authorisation Search Bar_1'.Text,"field_6","field_7","field_8","field_9","field_4","Field5Text")

 

Rather then the issue being with the permission formula, could it be the issue is with the:

 

_requestTypeFilter = "All" ||

Status.Value = _requestTypeFilter

 

Since I'm unable to click on All but can select all other status' currently. 

 

Hopefully, the above make sense, let me know if not. Thank you!

StalinPonnusamy
Super User
Super User

Hi @Anonymous 

 

I'm not seeing an issue with the status filter

 

1. Is the email matching with the data? I assume in the data source you have data to filter since you are working on this. Check User().Email vs email field in Data source. Sometimes may vary.

2. Make sure ColumnName is used correctly on the filter (column name should be emailed (same as data source) not Email.

3. Make sure to use column schema name not display name. To know the schema name.

  • Go to SP List settings
  • Click the column
  • In the address bar check &Field=schemaname

Then this filter should work

Search(
    AddColumns(
        Filter(
            Filter('Hire Authorisation Submit',
                 email = User().Email
            ),
            _requestTypeFilter = "All" || Status.Value = _requestTypeFilter
        ),
        "Field5Text",Department.Value
    ),
    'Hire Authorisation Search Bar_1'.Text,
    "field_6","field_7","field_8","field_9","field_4","Field5Text"
)

 
Thanks,
Stalin - Learn To Illuminate

Anonymous
Not applicable

Thanks for the help @StalinPonnusamy , really appreciate it! Unfortunately, I'm still having the same issue and despite many attempts still can't work it out. 

 

I've removed the status buttons with the page for now. Thank you again!!

StalinPonnusamy
Super User
Super User

Hi @Anonymous 

 

I assume _requestTypeFilter is empty. So I updated the filter now with that assumption.

 

Search(
    AddColumns(
        Filter(
            Filter('Hire Authorisation Submit',
                 email = User().Email
            ),
           IsBlank(_requestTypeFilter) || _requestTypeFilter = "All" || Status.Value = _requestTypeFilter
        ),
        "Field5Text",Department.Value
    ),
    'Hire Authorisation Search Bar_1'.Text,
    "field_6","field_7","field_8","field_9","field_4","Field5Text"
)

 

Anonymous
Not applicable

Amazing, thank you @StalinPonnusamy, really appreciate the help!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,256)