Modify or create a record (in a data source)/Merge records (outside of a data source)

You'll modify or create a record in a data source, named IceCream, that contains the data in this table and automatically generates the values in the ID column:

MODIFY OR CREATE A RECORD (IN A DATA SOURCE)

Formula	Description	Result
Patch( IceCream, Lookup( IceCream, Flavor = "Chocolate" ), { Quantity: 400 } )	Modifies a record in the IceCream data source: The ID column of the record to modify contains the value of 1. (The Chocolate record has that ID.) The value in the Quantity column changes to 400.	{ ID: 1, Flavor: "Chocolate", Quantity: 400 } The Chocolate entry in the IceCream data source has been modified.
Patch( IceCream, Defaults( IceCream ), { Flavor: "Strawberry" } )	Creates a record in the IceCream data source: The ID column contains the value 3, which the data source generates automatically. The Quantity column contains 0, which is the default value for that column in the IceCream data source, as the Defaults function specifies. The Flavor column contains the value of Strawberry.	{ ID: 3, Flavor: "Strawberry", Quantity: 0 } The Strawberry entry in the IceCream data source has been created.

After the previous formulas have been evaluated, the data source ends with these values:

Merge records (outside of a data source)

MERGE RECORDS (OUTSIDE OF A DATA SOURCE)

Formula	Description	Result
Patch( { Name: "James", Score: 90 }, { Name: "Jim", Passed: true } )	Merges two records outside of a data source: The values in the Name column of each record don't match. The result contains the value (Jim) in the record that's closer to the end of the argument list instead of the value (James) in the record that's closer to the start. The first record contains a column (Score) that doesn't exist in the second record. The result contains that column with its value (90). The second record contains a column (Passed) that doesn't exist in the first record. The result contains that column with its value (true).	{ Name: "Jim", Score: 90, Passed: true }

Use of As or ThisRecord

Using the As or ThisRecord keyword in the formula avoids ambiguous evaluation context.

In the example below, consider the first lookup in the If statement. (OrderID = A[@OrderID]) is expected to compare the OrderId in the lookup scope with the OrderId of collection A in the ForAll scope. In this case, you likely want A[@OrderId] to be resolved as a local parameter. But it is ambiguous.

Power Apps currently interprets both the left-hand side OrderId and right-hand side A[@OrderId] as a field in the lookup scope. Therefore, lookup will always find the first row in [dbo].[Orders1] because the condition is always true (that is, any row's OrderId is equal to itself.)

ClearCollect(

    A,
    Filter(
        '[dbo].[Orders1]',
        OrderId = 8888888
    )
);
ForAll(
    A,
    If(
        LookUp(
            '[dbo].[Orders1]',
            OrderId = A[@OrderId],
            "OK"
        ) = "OK",
        Patch(
            '[dbo].[Orders1]',
            LookUp(
                '[dbo].[Orders1]',
                OrderId = A[@OrderId]
            ),
            {
		OrderName: "val1"
	    }
	),
	Patch(
            '[dbo].[Orders1]',
            Defaults('[dbo].[Orders1]'),
            {
		OrderName: "val2"
	    }
	)
    )
)

Using As or ThisRecord

Whenever possible use the As operator or the ThisRecord to disambiguate the left-hand side. As is recommended for the above scenario.

When your formula uses multiple scopes with ForAll, Filter, and Lookup on the same data source or table, it is possible that the scope parameters may collide with a same field elsewhere. Therefore, it is recommended to use the As operator or ThisRecord to resolve the field name and avoid ambiguity.

For example, you can use the As operator to disambiguate in the example below.

ClearCollect(

    A,
    Filter(
        '[dbo].[Orders1]',
        OrderId = 8888888
    )
);
ForAll(
    A,
    If(
        LookUp(
            '[dbo].[Orders1]' As B,
            B.OrderId = A[@OrderId],
            "OK"
        ) = "OK",
        Patch(
            '[dbo].[Orders1]',
            LookUp(
                '[dbo].[Orders1]' As C,
                C.OrderId = A[@OrderId]
            ),
            {
		OrderName: "val1"
	    }
	),
	Patch(
            '[dbo].[Orders1]',
            Defaults('[dbo].[Orders1]'),
            {
		OrderName: "val2"
	    }
	)
    )
)

Alternatively, you can use ThisRecord for the same purpose.

Power Apps

ClearCollect(
    A,
    Filter(
        '[dbo].[Orders1]',
        OrderId = 8888888
    )
);
ForAll(
    A,
    If(
        LookUp(
            '[dbo].[Orders1]',
            ThisRecord.OrderId = A[@OrderId],
            "OK"
        ) = "OK",
        Patch(
            '[dbo].[Orders1]',
            LookUp(
                '[dbo].[Orders1]',
                ThisRecord.OrderId = A[@OrderId]
            ),
            {
		OrderName: "val1"
	    }
	),
	Patch(
            '[dbo].[Orders1]',
            Defaults('[dbo].[Orders1]'),
            {
		OrderName: "val2"
	    }
	)
    )
)