I am trying to navigate an issue around delegations whereby my SharePoint list has 120,000 records, and each record has one or more members of staff who own the record (Column name 'StaffID', eg value u1234567). I use the following formula in the Items property of a gallery to only display the list of records relating to that logged in staff member, so they can then action the record:
Filter(SharePointListName, 'Staff ID'= varUserID)
where varUserID is a global variable derived from the User() properties.
This works fine except for some records my 'Staff ID' column can contain data such as "u1234567, u2345678" (ie 2 people own the record).
I want this one record to show up in both peoples list, so I change the Item property formula to:
Filter(ARIES2020FORCodes, varUserID in 'Staff ID')
However this doesnt work on my list of 120,000 records as the 'in' operator is not delagible so I only get results from the first 2,000 records.
So I hit a brick wall with that approach (I believe).
My new plan of attack is for my SharePoint list to only have 1 Staff ID value for each row (as this then avoids delegation issues). If there are 2 owners for a record, I update my SharePoint list to have 2 rows for the record each with a different Staff ID.
My only challenge now is if one staff member updates the value of another column in the record, I want it to update both records based off another key field (publicationID). This way the other user can see the change too.
I know perhaps not the best architectural design but it will work for what is a short term project.
So asking if any thoughts on whether this can be done and what the syntax is - like a patch or something - I would be very grateful for your thoughts.