How save a record if it does not already exist

(0) Share

Report

Posted on by Aurora

364

Hi All,

I have an app that is used to quote parts my company produces. In the app, a user can save their quote and reopen it later - I achieve this by saving collections to a data table, and then re-loading the collection from the table inside the app. Where I could use some help is:

It's easy to load & save collections to a table, but is there an easy way to prevent saving an item if it is already present in the table? I grab the objects ID when loading them into a collection, so I am wondering if I can use that ID to prevent patching a row to my DataSource if it detects that ID in said data source. I can provide my code below.

Save: ForAll(colConnectors, Patch( 'Wire Harness Connector Collections', Defaults('Wire Harness Connector Collections'),
{ 'Quote #': TextInput6_2.Text,
ConnectorName: ThisRecord.ConnectorName,
Cost: ThisRecord.Cost,
Family: ThisRecord.Family,
Image: ThisRecord.Image,
'Part #': ThisRecord.PartNumber,
'Terminal Qty': ThisRecord.TerminalQty,
'Wire Qty': ThisRecord.WireQty}))

Load: ClearCollect(
colConnectorsEdit,
Filter(
'Wire Harness Connector Collections',
'Quote #' = Gallery2.Selected.Result))

Does anyone have a suggestion for how I can prevent patching records that already exist in the table if I create another save button for quotes that have been loaded into my app?

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sort by

MSM_Scoozi 63 on at

Like (0)

Report
Copy link

Link copied!

You could try using a conditional as a quick solution.

If(IsBlank(Lookup('Wire Harness Connector Collections', 'Quote #' = Gallery2.Selected.Result)), Patch, Don't Patch)

If the object returned is blank then you know that no match exists and you can then Patch your record, if it returns a record then you don't want to Patch and can do something else here or just leave it out of the statement. I hope I understood the question correctly!

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@Aurora

To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not. ForAll is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance especially with a Patch!

Since you are loading the ID of the records along with everything else, your formula should be the following:

Patch( 'Wire Harness Connector Collections', ForAll(colConnectors, { ID: ID, 'Quote #': TextInput6_2.Text, ConnectorName: ConnectorName, Cost: Cost, Family: Family, Image: Image, 'Part #': PartNumber, 'Terminal Qty': TerminalQty, 'Wire Qty': WireQty } ) )

Patch is smart enough to know what to do when you provide the ID (Primary Key). If it has a value, then Patch will update the record. If it is Blank (make sure your new records have blank ID's), then it will create a record.

You don't need to reference your columns with ThisRecord. In fact, you should avoid that operator as it can be very misleading!

Also, if all of the columns of your record are being spelled out in your formula, then you can cut all the typing time with the following formula:

Patch( 'Wire Harness Connector Collections', ForAll(colConnectors As _item, Patch(_item, {'Quote #': TextInput6_2.Text}) ) )

As you appear to only be changing one particular column value.

I hope this is helpful for you.

Was this reply helpful? Yes No
Aurora 364 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes

Hi Randy, the collection "colConnectors" is collected with this formula:

Collect(colConnectors,
{ PartNumber: Radio1.Selected.Result,
TerminalQty: LookUp(
'AUNA Wire Harnesses',
'Part Number' = Radio1.Selected.Result).'# of Terminals',
Image: Image2.Image,
Cost: Label31.Text,
ConnectorName: TextInput3.Text,
WireQty: TextInput4.Text,
Family: LookUp('AUNA Wire Harnesses',
'Part Number' = Radio1.Selected.Result).'Family/Brand'});

And is patched to my Dataverse table with the formula I listed in my previous post. I do not give each row an ID when they are collected, but they do automatically receive one when I patch my collection to dataverse. When I load my data back into the app, it goes into a collection called "colConnectorsEdit" when is treated just like "colConnectors" - minus the fact different controls write to either collection. "colConnectorsEdit" is then used to finish the quote.

I am currently getting an error trying to implement the formula you recommended. "colConnectorsEdit" has the field "cr9dc_wireharnessconnectorcollectionsid" which is what I wanted to use as my ID. When I try to put this in my formula, I get an error saying it expects a record value but is getting a table one instead.

Do you have any suggestions for what I am missing?

Was this reply helpful? Yes No
Aurora 364 on at

Like (0)

Report
Copy link

Link copied!

Also for my formula that loads previously saved records into a collection - I have explicit column selection turned off.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Aurora

Ah, DataVerse...

Okay, first, don't try to create your own identifier for the record. Each record will already have a unique identifier. This is your Primary Key. And, it is getting collected into your in-memory table.

What you need to do is look at your list schema and see what is the Primary Key column and get the schema name for it. Then, replace ID: ID in the formula I provided with the name of your primary column. That is the key that is needed for Patch.

Was this reply helpful? Yes No
Aurora 364 on at

Like (0)

Report
Copy link

Link copied!

@RandyHayes
Here is my schema name for the id column...
but I am getting an error still telling me I need a record value instead of a table one

Was this reply helpful? Yes No