Help trying to patch sp list and update existing record or create new

(0) Share

Report

Posted on by markbmcfc

Hi all,

I can't get the syntax right of what i'm trying to achieve, when building a visitor sign in app.

I've got a SP list called VisitorSignIn

A text column in that list called VisitorName

I've got a textbox called tb_visitorname.

Basically, onselect of the sign in button, I want to check the SP list to see if a record exists matching the type in name from the textbox, and if it does, update a few different columns with other values the user will select on the screen.

If the users typed in name doesnt exist, it creates a new record

I can't get my head around the syntax of using Patch, lookup to see if it exists and if so, update it, and if not, create a new record.

I tried creating a collection of the splist, patching the changes to the collection and just using collect(collection, splist) but it kept adding new and wouldn't update existing even though the "visitor name" already existed in another record.

Thanks!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@markbmcfc

First, take the collection out of what you are doing, you don't need this and it will just overcomplicate things.

In general, you can determine if a record exists with the following formula:

LookUp(yourList, criteria, true)

This will return true if the record exists.

To determine in a Patch statement if you have an existing or not, and if not, then create a new, you can utilize a formula such as this:

Patch(yourList, Coalesce(LookUp(yourList, creiteria), Defaults(yourList), {..record..})

This will patch either the existing record if found or create a new if not.

I hope this is helpful for you.
markbmcfc 36 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Randy, nearly there but getting an error.

Patch(VisitorSignIn, Coalesce(LookUp(VisitorSignIn, 'VisitorName' = tb_visitorname.Text), Defaults(VisitorSignIn),
{
VisitorName: tb_visitorname.Text,
Visiting: ComboBox1.SelectedItems,
'Sign in date': Now(),
VisitorType: Radio1.Selected,
Status: "In",
Company: tb_visitorcompany.Text
}))

Runtime error "VisitorName: Field VisitorName is required

As far as i can see i've defined it correctly?

Edit: I unset all the required fields as being required, then tried again. It patched, but it didn't patch anything above between the {}, just a few columns with default values, So i'm guessing i've done something wrong with the record part of your advice.
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@markbmcfc

Your formula is incorrect and it is my fault 🙄

I left out a closing paren after the Default function in the formula.

It should be:

Patch(VisitorSignIn, Coalesce(LookUp(VisitorSignIn, 'VisitorName' = tb_visitorname.Text), Defaults(VisitorSignIn)), { VisitorName: tb_visitorname.Text, Visiting: ComboBox1.SelectedItems, 'Sign in date': Now(), VisitorType: Radio1.Selected, Status: "In", Company: tb_visitorcompany.Text } )

Sorry about that...it was earlier in the morning - wasn't quite awake!
markbmcfc 36 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you! I was bashing my head against the wall on this. Much appreciated!