cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PVosEska
Resolver III
Resolver III

Patch multiple records to SQL Server

Hi guys,

 

I created an app months ago, that implements the following formula:

 

 

With(
    {
        newUploadTable: DropColumns(
            Filter(
                samMeasurementsCollection,
                new = 1
            ),
            "new",
            "edit",
            "del",
            "MeasurementID",
            "fromdb"
        )
    },
    Patch(
        '[dbo].[SampleMeasurements]',
        newUploadTable
    )
);

 

 

- It creates a temporary table 'newUploadTable' where I remove some columns to mirror the Database,

- Patch all items to the database

 

All items get patched at the same time, so it takes seconds. Works super well.

 

My current issue is that i'm trying to do the same thing in another app, but can't seem to make it work. The current formula:

 

 

 

With(
    {
        i: DropColumns(
            AddColumns(
                Filter(
                    Fcts,
                    selected = 1
                ),
                "RoleID",
                thisRole.roleID
            ),
            "selected"
        )
    },
    Patch(
        'ate.RoleFact',
        i
    )
);

 

 

- create temp table 'i',

- remove 1 column.

- Patch to DB 

 

Strange enough, I keep getting an error: Server response: <Tablename> failed: Item with id '**,<record>' does not exist.

 

Does anyone know of any limitations with different SQL Servers or versions?

Let me know if you have any tips!

 

Best regards,

 

1 ACCEPTED SOLUTION

Accepted Solutions
PVosEska
Resolver III
Resolver III

@StalinPonnusamy ,

 

So patching multiple items at the same time only works when there is an identity column.

 

I already implemented the ForAll() function around the patch, exactly like you did;

 

ForAll(
    myTable,
    Patch(
        mySource,
        Defaults(mySource),
        {
            myField1: myValue1,
            myField2: myValue2
        }
    )
)

 

This will however upload every row individually, and is not optimal for larger amounts.

 

Gave me an idea to create a progressbar though! The user can at least see something move while they wait 🙂

 

Regards and thanks for you help!

 

Peter

View solution in original post

5 REPLIES 5
StalinPonnusamy
Community Champion
Community Champion

Hi @PVosEska 

 

By Looking at the error message the temp table "I" has a column called id which does not exist in the SQL table.

 

To troubleshoot the issue,

 on the button, click set the below code to verify the names of the columns that match the SQL table

 

ClearCollect(ColTempi,DropColumns(
            AddColumns(
                Filter(
                    Fcts,
                    selected = 1
                ),
                "RoleID",
                thisRole.roleID
            ),
            "selected"
        ))

 

Will remove the columns is not needed or not exists.

 

 

Hi @StalinPonnusamy ,

 

Sorry for the late response. I tried your troubleshooting test, it had the same result.

 

I also tried with a custom made table, where is tried patching the following table:

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
Patch(
    'ate.RoleFact',
    tempTable
)

Same result.

 

I do have to mention btw, that every row should be a unique combination. Both RoleID and FactName are both PK fields, so no duplicate combination can occur. 

 

This is not the case however, just something I wanted to mention.

 

Regards,

 

Peter 

StalinPonnusamy
Community Champion
Community Champion

For SQL Table, We need an Identity column to make a patch command works. I tried this case, Patch to Primary field is not works for me. 

 

But created Primary field on Identity column with the SQL like

CREATE TABLE [dbo].[RoleFact](
    [RoleFactID] [int] IDENTITY(1,1) NOT NULL,
	[RoleID] [int] NOT NULL,
	[FactName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[RoleFactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

Patch command works like

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
ForAll(
    tempTable,
    Patch(
        RoleFact,
        Defaults(RoleFact),
        {
            RoleID: ThisRecord.RoleID,
            FactName: ThisRecord.FactName
        }
    )
)

 

PVosEska
Resolver III
Resolver III

@StalinPonnusamy ,

 

So patching multiple items at the same time only works when there is an identity column.

 

I already implemented the ForAll() function around the patch, exactly like you did;

 

ForAll(
    myTable,
    Patch(
        mySource,
        Defaults(mySource),
        {
            myField1: myValue1,
            myField2: myValue2
        }
    )
)

 

This will however upload every row individually, and is not optimal for larger amounts.

 

Gave me an idea to create a progressbar though! The user can at least see something move while they wait 🙂

 

Regards and thanks for you help!

 

Peter

View solution in original post

StalinPonnusamy
Community Champion
Community Champion

Hi @PVosEska 

 

I just tested this and works.

 

ClearCollect(
    tempTable,
    {
        RoleID: 1000,
        FactName: "TEST1"
    },
    {
        RoleID: 1000,
        FactName: "TEST2"
    },
    {
        RoleID: 1000,
        FactName: "TEST3"
    }
);
Collect(
    RoleFact,
    tempTable
)

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (965)