PowerApps merge related tables. Ask Question Asked 3 years, 4 months ago. Active 1 year, 8 months ago. Viewed 2k times 0. I'm new to PowerApps and am exploring.
-->Modifies or creates one or more records in a data source, or merges records outside of a data source.
Use the Patch function to modify records in complex situations. Such as, when you do updates that require no user interaction or use forms that span multiple screens.
How do I make these two tables talk in PowerApps? I'd like to be able to click into one of the Individuals in the gallery, view their information, and be able to update Notes (which goes into a second table). But whenever I include a second table, I get errors. – RCarmody Oct 16 '19 at 3:26. Find the right app for your business needs. Get solutions tailored to your industry: Agriculture, Education, Distribution, Financial services, Government, Healthcare, Manufacturing, Professional services, Retail and consumer goods. PowerApps is not the correct solution for joining tables, SQL or some other Database solution is (or even Excel - depending on the size). Now, if you need an app which takes a selection from EmployeeInformation and displays SuccessorName, you can do this in PowerApps.
To update records in a data source more easily for simple changes, use the Edit form control instead. When you add an Edit form control, you provide users with a form to fill in and then save the changes to a data source. For more information, see Understand data forms.
Overview
Use the Patch function to modify one or more records of a data source. The values of specific fields are modified without affecting other properties. For example, this formula changes the phone number for a customer named Contoso:
Patch( Customers, First( Filter( Customers, Name = 'Contoso' ) ), { Phone: '1-212-555-1234' } )
Use Patch with the Defaults function to create records. Use this behavior to build a single screen for both creating and editing records. For example, this formula creates a record for a customer named Contoso:
Patch( Customers, Defaults( Customers ), { Name: 'Contoso' } )
Even if you're not working with a data source, you can use Patch to merge two or more records. For example, this formula merges two records into one that identifies both the phone number and the location for Contoso:
Patch( { Name: 'Contoso', Phone: '1-212-555-1234' }, { Name: 'Contoso', Location: 'Midtown' } )
Description
Modify or create a record in a data source
To use this function with a data source, specify the data source, and then specify a base record:
- To modify a record, the base record needs to have come from a data source. The base record may have come through a gallery's Items property, been placed in a context variable, or come through some other path. But, you can trace the base record back to the data source. This is important as the record will include additional information to help find the record again for modification.
- To create a record, use the Defaults function to create a base record with default values.
Then specify one or more change records, each of which contains new property values that override property values in the base record. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.
The return value of Patch is the record that you modified or created. If you created a record, the return value may include properties that the data source generated automatically. However, the return value doesn't provide a value for fields of a related entity.
For example, you use Set(MyAccount, Patch(Accounts, First(Account), 'Account Name': 'Example name'); and then MyAccount.'Primary Contact'.'Full Name'. You can't yield a full name in this case. Instead, to access the fields of a related entity, use a separate lookup such as:
When you update a data source, one or more issues may arise. Use the Errors function to identify and examine issues, as Working with Data Sources describes.
Related functions include the Update function to replace an entire record, and the Collect function to create a record. Use the UpdateIf function to modify specific properties of multiple records based on a condition.
Modify or create a set of records in a data source
Patch can also be used to create or modify multiple records with a single call.
Instead of passing a single base record, a table of base records can be provided in the second argument. Change records are provided in a table as well, corresponding one-for-one with the base records. The number of records in each change table must be the same as the number of records in the base table.
When using Patch in this manner, the return value is also a table with each record corresponding one-for-one with the base and change records.
Merge records outside of a data source
Specify two or more records that you want to merge. Records are processed in the order from the beginning of the argument list to the end, with later property values overriding earlier ones.
Patch returns the merged record and doesn't modify its arguments or records in any data sources.
Syntax
Modify or create a record in a data source
Patch( DataSource, BaseRecord, ChangeRecord1 [, ChangeRecord2, … ])
- DataSource – Required. The data source that contains the record that you want to modify or will contain the record that you want to create.
- BaseRecord – Required. The record to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
- ChangeRecord(s) – Required. One or more records that contain properties to modify in the BaseRecord. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.
Modify or create a set of records in a data source
Patch( DataSource, BaseRecordsTable, ChangeRecordTable1 [, ChangeRecordTable2, … ] )
- DataSource – Required. The data source that contains the records that you want to modify or will contain the records that you want to create.
- BaseRecordTable – Required. A table of records to modify or create. If the record came from a data source, the record is found and modified. If the result of Defaults is used, a record is created.
- ChangeRecordTable(s) – Required. One or more tables of records that contain properties to modify for each record of the BaseRecordTable. Change records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.
Merge records
Patch( Record1, Record2 [, …] )
- Record(s) - Required. At least two records that you want to merge. Records are processed in order from the beginning of the argument list to the end, with later property values overriding earlier ones.
Examples
Modify or create a record (in a data source)
In these examples, 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 IDcolumn:
| Formula | Description | Result |
|---|---|---|
| Patch( IceCream, Lookup( IceCream, Flavor = 'Chocolate' ), { Quantity: 400 } ) | Modifies a record in the IceCream data source:
| { 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:
| { 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)
| Formula | Description | Result |
|---|---|---|
| Patch( { Name: 'James', Score: 90 }, { Name: 'Jim', Passed: true } ) | Merges two records outside of a data source:
| { 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.)
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.
Alternatively, you can use ThisRecord for the same purpose.
To learn more about the usage of As operator and ThisRecord see Operators article.
-->When you look at the solution explorer or the three relationship collections in the EntityMetadata, you might think that there are three types of relationships. Actually, there are only two, as shown in the following table.
Note
Unsure about entity vs. table? See Developers: Understand terminology in Microsoft Dataverse.
| Relationship Type | Description |
|---|---|
| One-to-Many OneToManyRelationshipMetadata | A relationship where one record for the Primary Table can be associated to many other Related Table records because of a lookup column on the related table. When viewing a primary table record you can see a list of the related table records that are associated with it. |
| Many-to-Many ManyToManyRelationshipMetadata | A relationship that depends on a special Relationship Table, sometimes called an Intersect table, so that many records of one table can be related to many records of another table. When viewing records of either table in a Many-to-Many relationship you can see a list of any records of the other table that are related to it. |
The EntityMetadataManyToOneRelationships collection contains OneToManyRelationshipMetadata types. One-to-Many relationships exist between tables and refer to each table as either a Primary Table or Related Table. The related table, sometimes called the child table, has a lookup column that allows storing a reference to a record from the primary table, sometimes called the parent table. A Many-to-One relationship is just a One-to-Many relationship viewed from the related table.
Powerapps Merge Tables
Note
Although related tables are sometimes called child tables, don't confuse these with Child tables, which refers to how security is applied to related tables.
More information: Create relationships between tables.
Note
Although related tables are sometimes called child tables, don't confuse these with Child tables, which refers to how security is applied to related tables.
More information: Create relationships between tables.
Cascade configuration
Powerapps Append Tables
When a one-to-many relationship exists, there are cascading behaviors that can be configured to preserve data integrity and automate business processes. More information: Configure relationship cascading behavior.
Create a hierarchy of tables
Within a self-referential One-to-Many relationship you can set a hierarchy by setting the IsHierarchical property to true.
With model-driven apps, this enables an experience that enables you to view and interact with the hierarchy.
For developers, this enables new types of queries based on the hierarchy using the Under and Not Under operators.
More information: Microsoft Dataverse Developer Guide : Query and visualize hierarchically related data.
See also
