Patching a SharePoint List from a Collection

[Siddz]

I have a canvas new-employee on-boarding checklist app connected to three SharePoint lists: Employees, Checklist, and Responses.

The Process:
1. The supervisor or the on-boarder clicks an employee's name in GalleryEmployees on Screen1 and navigates to Screen2, which displays GalleryChecklist
2. GalleryChecklist is filtered to display the checklist items for the logged-in user
3. The supervisor or the on-boarder chooses "Yes" or "No" in Dropdown1 for each checklist item and clicks the "Save" icon
4. The "Save" icon patches the "Yes/No" values to the Responses SharePoint list for the selected employee

The Structure:

App > OnStart:

// Store checklist items from Checklist SharePoint list
   Set(colChecklistList, Checklist); 

Screen1 > GalleryEmployees > Items:

// Display the list of employees from the Employees SharePoint list
   Employees 

GalleryEmployees > Icon > OnSelect:

// Store the selected employee's record
   Set(varEmployee, ThisItem); Navigate(Screen2) 

Screen2 > OnVisible:

// Add an "Answer" column to the collection to store the "Yes/No" value from Dropdown1
   ClearCollect(colChecklist, AddColumns(ShowColumns(colChecklistList, "ID", "Title", "Category"), "Answer", "No"));

GalleryChecklist > Items:

// Filter gallery to only show checklist items based on the current user's role (either supervisor or on-boarder from Employees list)
   If(varEmployee.Supervisor.Email = User().Email, Filter(colChecklist, Category.Value = "Supervisor"), 
   If(varEmployee.Onboarder.Email = User().Email, Filter(colChecklist, Category.Value = "Onboarder")))

GalleryChecklist > Dropdown1 > OnChange:

// Store "Yes/No" value in the collection based on Dropdown1's selection
   Patch(colChecklist, ThisItem, {Answer: Dropdown1.Selected.Value})

GalleryChecklist > Dropdown1 > Default:

ThisItem.Answer

Screen2 > Save Icon > OnSelect:

// Upon saving, patch Responses SharePoint list with "Yes/No" values for selected employee
   ForAll(Filter(colChecklist, Category.Value = "Supervisor"), If(Dropdown1.Selected.Value = "Yes", 
   Patch(Responses, {Title: Answer, ChecklistID: ID, EmployeesID:varEmployee.ID})));

My Questions:

1. Upon saving, how can I patch the "Yes" or "No" values to the Responses list for the selected employee (varEmployee)?

(My current approach adds a new list item (row) each time I click "Save". I'd like to update values if they already exist.)

2. If an employee has stored values in the Responses list, how do I retrieve those values and display them in GalleryChecklist?

[EddieE · Accepted solution]

@Siddz 

Firstly, well done for setting out your question with such great detail. This is not often done by those seeking help but it really helps those who wish to help to work out a solution.

There's probably a few ways to approach a solution here, but if this was my project I'd do this:

- every time a new Employee is added to the Employees List, I'd run a Flow to grab the CheckList Tasks and create the items needed in the Responses List

- I'd set each of these item responses to 'No' in the above Flow

- then, inside your app, when a person selects an employee from your first gallery the gallery on the next screen could show the responses, ie

With(
   {
     wTasks: Switch( User().Email,
               varEmployee.Supervisor.Email, Filter(Checklist, Category.Value = "Supervisor"), 
               varEmployee.Onboarder.Email, Filter(Checklist, Category.Value = "Onboarder")
             ),
     wResponses: Filter( Responses, EmployeesID = varEmployee.ID )
   },

   Filter(
      wResponses,
      ChecklistID in wTasks.ID
   )
)

Then, each time a person selects Yes/No run your OnChange Patch() directly to the Responses List. Also, I cannot fully test the above but I'm pretty sure there won't be any delegation issues use 'in' in that code - could be wrong though ...?

To set this up and give it a try, just create all the Responses for a particular person manually then test this code.

[Siddz]

Thanks, @EddieE.

Using Power Automate and the With() function is an interesting approach I hadn't considered. I may try that route if I can't figure out how to patch properly from a collection to my SharePoint list using ForAll().

This method of patching works, but it creates new list items with each patch instead of updating existing items:

// Button > OnSelect
// This works, but it creates new list items with each patch instead of updating existing rows
   ForAll(Filter(colChecklist, Category.Value = "Sponsor"), 
   Patch(Responses, {Completed:Answer, ChecklistID: ID, EmployeesID:varEmployee.ID})) 

However, when I try to update existing values in the "Responses" list, I get the error below ("The requested operation is invalid.")

// Button > OnSelect
// This throws the error below
   ForAll(Filter(colChecklist, Category.Value = "Sponsor"), 
   Patch(Responses, LookUp(Responses, ID = ChecklistID), 
   {Completed:Answer, ChecklistID: ID, EmployeesID:varEmployee.ID}))

I'm stuck on how to update existing values when patching from colChecklist to the "Responses" SharePoint list.

[EddieE]

@Siddz 

What you can do is add the ID field into your Patch and this will pickup existing records and update them and also add new ones as required, ie

// Button > OnSelect
// This works, but it creates new list items with each patch instead of updating existing rows
   ForAll(
      Filter(colChecklist, Category.Value = "Sponsor") As data, 
        Patch(
           Responses,
           {
             ID: data.ID, 
             Completed: data.Answer, 
             ChecklistID: data.ID, 
             EmployeesID: data.varEmployee.ID
           }
        )
) 

Note, the structure ForAll( Patch( ... ) ) is slower because it patches one record at a time. The Structure Patch( ForAll( ... ) ) is faster. You won't notice a big difference if your data getting patched is only a few records but for large datasets it can make a difference.

I try to use Patch( ForAll( ... )) always now.

I've also used 'As' above to ensure there's no confusion (disambiguation) with the ID field, which can sometimes happen when using ForAll().

[Siddz]

Thanks, @EddieE!

I think we're on the right track, but I'm getting an error ("This type of argument does not match...")

I tried changing "data.varEmployee.ID" to "varEmployee.ID", but that results in this error:

I'll keep tinkering with it. Once I get this working, I'll try to implement Patch(ForAll( ... )) to improve efficiency. Thanks for the suggestion!

[EddieE]

@Siddz 

Wrap the varEmployee.ID in the Value function, ie

EmployeesID: Value(data.varEmployee.ID)

This is because the ID field from SharePoint looks like a number but really isn't a number ... wrapping it in Value() should convert it for you - hopefully!?

[Siddz]

Thanks, @EddieE.

I tried the Value() function earlier, but I got the errors below (different errors depending on where I hovered).

I triple-checked the "EmployeesID" and "ChecklistID" columns in the "Responses" list to make sure they were number types, and they are. I even hard-coded the EmployeesID just to test, but "the specified record was not found" (EmployeesID: 16). 

[EddieE]

@Siddz 

Ah, my bad, I'm trying to do this while distracted ... sorry! varEmployees.ID isn't part of the data table we create, hence the error.

Also, I think you'll need to do a Table append to colChecklist as well as a column Renaming to be able to update records. This will be a little more tricky which is why I suggested the Flow + direct access to the Responses, rather than a cobbled together table. It's doable, but I tend to avoid it because it can be problematic

[Siddz]

Thanks for your help, @EddieE! You gave me a few more ideas to pursue and some great suggestions to improve the app. Much appreciated. 

[Siddz]

Thanks, @EddieE!

I ended up taking your advice by creating a flow that populates my "Responses" list when a new employee is added to the "Employees" list.

The Flow:

• When an item is created in the "Employees" list
• Get items from "Checklist" list
• Create an item in the "Responses" list (this creates an "Apply to each" loop)
• Map the fields in the "Responses" loop with the corresponding dynamic content fields

Back in Power Apps:

Insert a checkbox control in the gallery.

Set the OnCheck property to: 

Patch(Responses, ThisItem, {Completed: "Yes"});

Set the OnUnCheck property to: 

Patch(Responses, ThisItem, {Completed: "No"});  

Set the Default property of the checkbox to: 

If(ThisItem.Completed = "Yes", true, false) // Ensures checkbox values are retrieved when returning to the gallery

Thanks again!