Need Help with Filtering a related Gallery for the Role based Security with SP List as Data Source

[SueA]

Hi:

See attached snippet. I have two Galleries side by side.

The ITEMS Gallery (galItemsRT) has a SEARCH Box on the top for ITEM's Title.

The ITEMS Gallery is connected to ARMSitems SP list. (Primary)
The TASKS Gallery (galTasksRT) is connected to ARMStasks SP list. (Item ID = galItemsRT.ID)

I have two Roles Admin and Responsible.

Admin should be able to see everything.

In Responsible Role, Responsible Person and Responsible Backup Person can see only TASKS that are assigned to them.

I can Filter TASKS Gallery with the showing only TASKS that are assigned to the Signed in User who is not Admin as follows:

If(
!isUserAdminSPList,
Filter(
ARMStasks,
'Item ID' = galItemsRT.Selected.ID,
('Responsible Person'.Email = varCurrentEmail || 'Responsible Backup'.Email = varCurrentEmail)
),
Filter(
ARMStasks,
'Item ID' = galItemsRT.Selected.ID
)
)

However, I am unable to filter-out ITEMS for which they have no TASKS assigned to them.

Below is my current code for ITEMS property of galItemsRT, which shows all the ITEMS to everyone:

If(
!isUserAdminSPList,
(With(
{
wList: Filter(
ARMSitems,
Status = "Assigned" || Status = "Pending" && ID in Distinct(
colTasks,
'Item ID'
)
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
)),
(With(
{
wList: Filter(
ARMSitems,
Status = "Assigned" || Status = "Pending"
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
))
)

How can I fix the above code to get the results?

Any help is appreciated.

Thank you,
SueA.

[v-jefferni]

Hi @SueA ,

Please try below formula:

If(
!isUserAdminSPList,
(With(
{
wList: Filter(
ARMSitems,
Status = "Assigned" || Status = "Pending" && ID in Filter(
ARMStasks,
'Responsible Person'.Email = varCurrentEmail || 'Responsible Backup'.Email = varCurrentEmail
).'Item ID' || IsBlank(LookUp(
ARMStasks, 'Item ID' = [@'ARMSitems'].ID))
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
)),
(With(
{
wList: Filter(
ARMSitems,
Status = "Assigned" || Status = "Pending"
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
))
)

Best regards,

[SueA]

Hi, @v-jefferni 

Thank you for your effort in solving this long and complex query. Everything seems ok except I am getting an error at one place. See the attached snippet. And, the error message below in PowerApps as stated below:

Issue

Incompatible types for comparison. These types can't be compared: Number, Table

Location

galItemsRT.Items

How to fix

You might need to convert the value to be the same type, such as converting a date string (e.g., "12/31/2018") to a date value.

If you are comparing records or tables, the field or column types must match exactly.

Please advise how can I fix this?

Thanks,
SueA.

[WarrenBelz · Accepted solution]

Hi @SueA ,

As per the PM you sent me, firstly try condensing the code to this

With(
   {
      wTasks:
      Filter(
         ARMStasks,
         'Responsible Person'.Email = varCurrentEmail || 
         'Responsible Backup'.Email = varCurrentEmail
      ),
      wList:
      Filter(
         ARMSitems,
         Status = "Assigned" || Status = "Pending"
      )
   },
   SortByColumns(
      Search(
         Filter(
            wList,
            isUserAdminSPList ||
            ID in wTasks.'Item ID'
         ),      
         tiSearchRT.Text,
         "Title"
      ),
      "ID",
      SortOrder.Descending
   )
)

My assumptions here are

• Status is a Single Line of Text column
• isUserAdmin is a Boolean (true/false) Variable which is true for admin staff
• colTasks contains a numeric field called 'Item ID' which matches the ID of the "parent" in ARMSItems

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

[v-jefferni]

Hi @SueA ,

Please try the formula from Warren's first. Just to fix the error in current formula:

If(
!isUserAdminSPList,
With(
{
wList: Filter(
ARMSitems As AA,
AA.Status = "Assigned" || AA.Status = "Pending" && AA.ID in Filter(
ARMStasks,
'Responsible Person'.Email = varCurrentEmail || 'Responsible Backup'.Email = varCurrentEmail
).'Item ID' || IsBlank(LookUp(
ARMStasks, 'Item ID' = AA.ID))
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
),
With(
{
wList: Filter(
ARMSitems,
Status = "Assigned" || Status = "Pending"
)
},
SortByColumns(
Search(
wList,
tiSearchRT.Text,
"Title"
),
"ID",
SortOrder.Descending
)
)
)

Best regards,

[SueA]

Hi, @v-jefferni @WarrenBelz 

The TASKS Filter works perfectly.

For the ITEMS filter, I tried both the scripts but as a non-Admin, I am still seeing some of the ITEMS for which I have no TASKS Assigned to me.

I have total of six ITEMS (1, 13,14,15,16,17) for which none of TASKS are assigned to me.

With Warren's script:

As a non-Admin, I am still seeing ITEM# 1, 13, 16, 17 (with No TASKS on the right hand side Gallery). Note for ITEMS# 14 and 15, no TASKS are created. Meaning, if there are zero TASKS, the ITEMS are not seen (Filtered out). However, if there are TASKS but not assigned to the signed in person, then it still shows those ITEMS with TASKS gallery as blank.

With v-Jefferni's script:

As a non-Admin, I am still seeing ITEM# 1, 13, 14, 15, 16. (with No TASKS on the right hand side Gallery). Again, there are no TASKS created for ITEMS # 14 and # 15 and should not be seen by the logged in person.

I have not been able to figure out what is so special about these ITEMS (that I am not supposed to see but still seeing them) except none of their respective TASKS are assigned to me neither as a Responsible Person role nor as a Responsible Backup person (optional) role.

I tried to add the comparison of the Responsible and Backup Person in the above query, which actually may be should be before we compare the relationship IDs (?), but I am getting a RED squiggly (see attached). I am not sure what I am doing wrong...

Any suggestion is appreciated.

Thank you.
SueA.

[WarrenBelz]

Hi @SueA ,

I have amended the original code - I missed one criteria. See if that works.

[SueA]

@v-jefferni 

Thank you for your prompt reply. However, per your suggestion I tried Warren's script and that seem to have done the trick to solve my issue in a timely manner. Many thanks none the less for your time and efforts to help me through this tough issue I was having.

In appreciation,

SueA.