Answered

Form to List - New AND update items

(0) Share

Report

Posted on by CROKat

155

I am trying to create a flow that will take a form submission and use that information to either create a new SharePoint List item, or update an existing List item depending on the submission and whether certain fields match. Users will submit a request via the form, and occasionally will need to update those requests before they have been completed. I want them to be able to submit the form with their updates and it automatically updates the List item they previously submitted.

When I looked into this, I found that there should be a way to do this via Flows/Power Automate. I have also looked into PowerApps but both of these are making my brain explode. Is a Flow the best way to do this? If not, what is a better way?

my formula in the Condition:

empty(outputs('Get_items')?['body/value'])

I really have no idea what I am doing wrong here...or maybe I just have no idea what I am doing 🙂

@wskinnermctc

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (1)

Sundeep_Malik 6,484 on at

Like (1)

Report

Hey @CROKat

Not sure that condition expression would work or not.

The expression you would be using in condition should look something like this:

length(outputs('Get_items')?['body/value'])

And on right side you can write is equal to 0.

This will mean if the length of get items is 0, its a new item and if the length is not 0, the item is already there and you need to update it.

Check if this works, any doubts or errors just reply.

I hope this helps 🙂

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Same thing as last time. You need to find the InternalName of the SharePoint column to use in the filter query of the Get Items.

(I know for sure it is not "Request ID" because InternalNames of column names can't have a space.) It is most likely "RequestID" or it is "Request_x0020_ID" depending on how you created the column.

Go look at the post here that talks all about column names in the filter query.

Then it depends on the column type. Is your "RequestID" column a text/string or is it a Number type. Below is an example of the List Settings that show the column type.

List Settings

If you show me screenshots of your column internal name and the column type I will show you how to filter this correctly.

Was this reply helpful? Yes No
CROKat 155 on at

Like (0)

Report

Thanks for the reply. I have modified the flow a bit to work with the new updates to my form/list and now it looks like this:

Below is what my columns look like. Unlike yours there are spaces. Why is that? I created the "Request ID" column so that users can create a unique identifier that I will use if they make updates. This is what I wanted to do the filter query with.

Was this reply helpful? Yes No
Verified answer

wskinnermctc 6,519 Moderator on at

Like (0)

Report
You still didn't show the column InternalName. But whatever, I have made an example that filters by a Text type column. I was going to make an example that filtered by a number type too, but since you just have a text, I'll show that.

I'm going to show my example SharePoint List and List Settings:

Example List ViewSharePoint List Settings

To find the column InternalName you need to look in the URL of the edit column.

The column "Request ID Text" has an InternalName of "RequestID_Text"The column "Request ID Number" has an InternalName of "Request_x0020_ID_Number"

Here is the example form I am using:

Example FormExample Form Submission

Here is the flow that will trigger when a form is submitted:

Example Flow Overview

Here is the first part of the flow that is filtering the Get Items. Look in the Filter Query to see how it is written and uses the form response as a filter. See how even though my column display title was "Request ID Text" the InternalName was "RequestID_Text" so that is what I use in the filter query.

Flow Part 1 - Get Items Filter Query

If I was using the number column instead of the text the Filter Query would look like this:

Filter Query when using the number column "Request ID Number" with an InternalName of "Request_x0020_ID_Number"

The rest of the flow has the condition that checks the results of the Get Items. It is checking the length() of Get Items values which is basically a count of how many items were returned. If the length is 0 zero, then no items matched the RequestID. So it should make a new item on the list.

Condition is checking the Length() of Values from the Get Items

Flow Part 2 - Condition Check and Update Item

The use of the variable to get the Item ID is to prevent using an Apply to Each. But it also assumes there is only 1 row that will be returned with the RequestID. If there are multiple rows with the same RequestID, this will only update the first one that was retrieved.

The expression has to be manually typed into the field.

first(outputs('Get_items')?['body/value'])?['Id']

Since it is trying to avoid the apply to each the expression is using the body of the Get Items and then references the ID column.

Use the First Item from Get Items ID value

So this is a basic example, but hopefully it gives you an idea of what is supposed to happen.

Was this reply helpful? Yes No
CROKat 155 on at

Like (0)

Report

THANK YOU SO MUCH, oh my gosh. It worked flawlessly. I have been trying to figure this out for so long, you just made my month! I am still relatively new to all of this so I really appreciate you taking the time to help me get it right!

Theoretically, could I add multiple values to the filter query if I wanted the record to match in multiple fields/columns in order for the record to update?

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

Correct, the filter query can have multiple comparisons.

https://learn.microsoft.com/en-us/sharepoint/dev/business-apps/power-automate/guidance/working-with-get-items-and-get-files#filter-queries

I'm sure you will need to adjust the flow some to reinforce accuracy. Having users input a value that needs to be compared is always risky and error prone.

Was this reply helpful? Yes No