Update last SharePoint List item that matches Form answer

[NicolásN]

Hi.

I have a attendance MS form that people fill on the field, where the most relevant field is "vehicle plate", but it is not an unique value as the vehicles rotate and are used randomly.

I also have a flow that captures the form's answers and stores them in a SharePoint list.

I need some particular answers about the vehicle to go to the last instance of that vehicle in the SharePoint list.

Let's say that Paul in vehicle "A1" fills the attendance form but "vehicle cleanliness" actually refers to the last time vehicle "A1" was delivered by Bob. I need Paul's answer to "vehicle cleanliness" to update Bob's row, so as the days go by we have an indicator of the state Bob delivers the vehicle in.

I can't add any unique IDs, I need to go by "vehicle plate" only. So everytime someone fills attendance in a vehicle, the flow needs to check the last row in the list that matches that plate and redirect some form's answers to that row.

Any help is appreciated.

[v-mengmli-msft]

Hi @NicolásN ,

Do you want to get this?

The Initial data, no one submitted updates.

When someone submits an update:

Please try this:

Hope this helps you!

-----------------------------------------------------------------------------------------------------------------------------------

If my answer solves your problem, please accept it as a solution to help more people stuck on the same problem find it.

Best regards,

Rimmon Li

[NicolásN]

Hi @v-mengmli-msft and thank you for taking the time to answer. I apologize if I'm reading this late, I can only login to my MS account when I'm in the office.

I think I failed to explain myself properly.

What I have is a form that over three hundred people fill on the field when starting their shift. It works both as an attendance form and a vehicle checklist.

Each time the form is completed, the answers go to a SharePoint list which I plan to use to make a couple dashboards.

If I understand your example, you take a vehicle from a SharePoint list and compare it to the "vehicle" answer in the form. This wouldn't work for me because there's not a fixed vehicle list I can work from; the "vehicle" value is filled manually on the form. Also, each vehicle plate appears multiple times on the SharePoint list (everytime it was used).

On the attendance form, there are many answers that refer to the state the vehicle was left. Also, there's a little branch you can fill when ending the turn to record mileage and such.

What I'm trying to do is the following:

- Bob starts his shift in vehicle "A1", fillls form, and gives "vehicle cleanliness" three stars.

- Flow checks the last instance of "A1" in the SharePoint list. If it exists, "cleanliness" column is updated on that item.

- Flow records most of the answers on the SharePoint list as a new item.

- Bob ends his shift, fills form (same form, different branch) and records mileage of vehicle "A1".

- Flow checks the last instance of "A1" in the SharePoint list. It exists because Bob already created that item when filling the form at the start of his shift. "Shift end mileage" column is updated on that item.

Basically I want to learn how to search the last instance of a value in a column of a SharePoint list, update some columns in that item, and move on.

[v-mengmli-msft]

Hi @NicolásN ,

Since I don't know the structure of your data, I can only assume the primary result you want to get.

If vehicle A1 is in the list, then update the A1 record content, the flow I provided can do this, for vehicles that are not in the list, you can use create item action in the no condition of condition to create a new record.

Best regards,

Rimmon Li

[NicolásN · Accepted solution]

Hello @v-mengmli-msft and good morning.

I managed to solved it! I don't know if it is the correct way of doing it but so far it works.

I used a 'Get items' on the list, with a filter query using the vehicle plates entered in the form, ordered by 'ID desc' with a top count of 1, to get the ID of the last item in the list with the same plates. I then used 'Update item' using that ID number.

One of the reasons it wasn't working at first is because I needed to put the vehicle plates dynamic content between single quotes ('dominio').

[v-mengmli-msft]

Hi @NicolásN ,

Well done, you can also filter records through Filter query when get items, but this method has a limitation, you can't filter multiple strings at the same time, for example, if you want a column whose values contain A and B, then you need the condition method I provided to filter, add multiple contains, and set the relationship between them to and.

If my answer is helpful to you, I hope you will accept it as a solution and reward me with a thumbs up.

Thanks!😀

Rimmon Li