SharePoint List Data to Collection - Update Records and Write Back to SharePoint List

[mkusler]

I have a simple PowerApp that is essentially a form that the user fills out that collects information from 15 different fields, free text and drop downs. Each data card corresponds to a column in my SharePoint list. The idea is simple, each time the form is completed and submitted, a new row is created in SharePoint.

Working in conjunction with the PowerApp above, I have another app that serves as a work queue.

The work queue has logic that only shows certain Work Order #s depending on the selections made on the first input form above. The queue is then sorted by Work Order # and then by created time. The users have the ability to select multiple records, typically grabbing all from the same Work Order # that is present.

Gallery Code

Items:

SortByColumns(

Filter('Daily Picking Data - NEW DRAFT','Material Needed'.Value = "Yes" && Fulfillment.Value = "Review Needed" && Cancellation.Value = Blank()),

"WO_x0023_", Descending, "Created", Descending)

Once the user decides which records/tasks they are going to work on by checking the boxes, they then click the arrow to advance to the next screen.

Checkbox Code

OnCheck:

Collect(SelectedWO,ThisItem)

OnUncheck:

Remove(SelectedWO, LookUp(SelectedWO, ID = WorkqueueGallery_1.Selected.ID))

Arrow Code

OnSelect: Navigate(Screen1)

Once the user gets to Screen 1, they will see a data table containing specific information from the data collection that was created.

The user then uses the information from this screen to complete some manual tasks. I am using a data table format so that the user can print the screen and have a cleaner view, especially when many records may be selected.

My questions now… how can I bulk update only the entries that populate on this table in the SharePoint data source, not the collection? Within my SharePoint List I have two other fields that I would like this bulk update to impact, which ultimately drive the work queue previously mentioned:

• Fulfiller Initials
• Fulfillment

In my previous version of the app the user was taken to a page for each individual record where they would look at the data, perform their task and then complete the following by entering their initials, choosing Fulfillment = “Complete” and then hitting submit.

Once they submit, they are taken back to the work queue page and the Work Order # drops off the list and they keep working through the queue.

Having said all that, how can I replicate the functionality above, except that it will bulk update all the selected records in the SharePoint data source, that landed in my temporary collection and were shown in the data table?

[RandyHayes · Accepted solution]

@mkusler 

First, I would eliminate the collection as you already have a table of data...your gallery.

So, your second gallery of items on Screen1 simply needs to have an Items property of: Filter(yourcheckoffGalleryName.AllItems, yourCheckBoxName.Value)

This will contain all the records from that gallery that were selected.

Beyond that, you would simply do the following with your "bulk update":

Patch(yourSPList,
    ForAll(Filter(yourcheckoffGalleryName.AllItems, yourCheckboxName.Value),
        {ID: ID,
         columnYouWantToChange: columnValueYouWant
        }
    )
)

I hope this is helpful for you.

[mkusler]

@RandyHayes 

I added a new gallery (CheckGallery) to Screen 1 that shows records that were selected on my main screen in my first gallery (WqGallery).

Then I added a button on Screen 1 with the following version of the code you provided:

Patch('Daily Picking Data - NEW DRAFT',
ForAll(Filter(CheckGallery.AllItems, Checkbox3.Value),
{ID: ID,
Fulfillment: Complete
}
)
)

However, I get an error stating: "Invalid Argument Type (Table). Expecting a Record Value Instead"

[mkusler]

@RandyHayes 

Update: I got the button working, however I have a new issue. When I select a record (or multiple) using the checkboxes and then press the button, it clears all the records from the gallery, even ones I didn't check and subsequently the whole gallery disappears. 

How can I stop that from happening?

[RandyHayes]

@mkusler 
Usually if you see that error on the Patch statement, then there is a column name in your record returned from the ForAll that is not correct.  Needs to be the real name of the column!

But if you are seeing what you say now in the Gallery...then what is the formula you ended up with for that button?

Also, what is the formula for the Items property of the Gallery?

[mkusler]

@RandyHayes 

I think the problem was that my Fulfillment fields in SP is a "Choices" field. So once I tied it to the dropdown box I created for the user to select 'Complete", it all started working.

Button Formula: 

Patch('Daily Picking Data - NEW DRAFT',
ForAll(Filter(CheckGallery.AllItems, Checkbox3.Value),
{ID: ID,
Fulfillment: Dropdown1.Selected, 'Fulfiller Initials': TextInput2.Text
}
)
)

Gallery Formula:

Filter(WqGallery.AllItems, Checkbox1.Value)

Besides the entire gallery disappearing after hitting the button, the functionality works. It is writing back to my data source and then dropping them off the work queue. 

I guess I could look at the fact that if the gallery disappears after hitting the button once, it forces the user to reconsider what they are doing in the first place if they need to go back and make different selections. Sort of a safety feature if you will.....

Getting closer though! Thank you!

[RandyHayes]

@mkusler 

Ah yes, the Choice column.  What you have is sufficient then and I am assuming then that Dropdown1 has an Items property that has a Choices function in it.  All good!

So, you're referring to the second gallery, not the first.  I had assumed you meant that your first gallery (the work queue) became empty.

The reason that your second gallery becomes empty is because the checkboxes in the first get cleared.  And the reason that they get cleared is because of your Items property.  Whenever you make ANY change to the 'Daily Picking Data - NEW DRAFT' list, PowerApps will re-evaluate that formula.  The reason is, you are using a SortByColumns function in this case.  PowerApps assumes that the list has changed and that it has to do the sort over again.

Once that is re-evaluated, the controls in the gallery will return to Default.

SO...back to the collection...this is a valid use of a collection (although it can also be done with a simple variable).

But the question is...do you want those checkboxes to still be checked?  You seemed to be leaning toward a "this is a safety feature" the way it is.  Do you want to retain that safety feature or keep them checked?

[mkusler]

@RandyHayes 

That all makes total sense! I appreciate the explanation. Let me try and meet with the folks who will actually be using these apps on a daily basis and see how they feel about this happy accident that could provide some checks and balances as people use the app. 

I will let you know, but for now I'm considering this solved!

[rs7005]

Is it possible to pull from the same list you created but write back to a different list?