Relate multiple records at once CDS

[Jackw]

Hey,

What would be the best way to relate multiple records at once? I was thinking I could just do a relate inside of a forall but that is not working. It fails on run. I am trying to have my users select one recored in a gallery (lets call this R1). The recored they select has a relationship table to more records (lets call them R2). All of the R2s that are related to the selected recored (R1) need to get related to another recored (R3), and still be related to the selected recored (R1). R1 needs to be related to R3 as well, I will most likely just relate R1 and R3 on select of R1.

Please help.

[v-xida-msft]

Hi @Jackw ,

Could you please share a bit more about the relationship between the R1 and R3? Does the R3 have a One-to-Many relationship to R1?

Does the R3 record has a one-to-many relationship table to the R2s?

I assume that the Entity the R3 record existed has a One-to-Many relationship to the Entity the R1 record existed. I have made a test on my side, I think the ForAll function could achieve your needs, please try the following workaround:

1) R1 record is existed in Entity1, R2 records existed in Entity2, the relationship name is called "Entity2 (prefix_Entity2_LookUpFieldName_Entity1)"

2) R3 record existed in Entity3, Entity3 has One-to-Many relationship to Entity2, the relationship name is called "Entity2 (prefix_Entity2_LookUpFieldName_Entity3)"

3) Entity3 has One-to-Many relationship to Entity1, the relationship name is called "Entity1 (prefix_Entity1_LookUpFieldName_Entity3)"

Set the OnSelect property of the Gallery1 to following:

// relate R2s record to R3 record
ForAll(
       R1Record.'Entity2 (prefix_Entity2_LookUpFieldName_Entity1)',
       Related(
                R3Record.'Entity2 (prefix_Entity2_LookUpFieldName_Entity3)',
                ThisRecord
       )
);
// relate R1 record to R3 record
Relate(
        R3Record.'Entity1 (prefix_Entity1_LookUpFieldName_Entity3)',
        R1Record
)

Note: The R1Record represents the R1 that you mentioned, the R3Record represents the R3 that you mentioned.

Please try above solution, check if the issue is solved.

Relate function in PowerApps

Regards,

[Jackw · Accepted solution]

They are all Many to Many. Your code should work. It is what I was trying to do. But for some reason the "ThisRecord" is not acting as a valid parameter. I will have to open a support ticket, I think. 

[v-xida-msft]

Hi @Jackw ,

Is the "ThisRecord" operator is not available on your side?

This new feature has been released in PowerApps, please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/formulas-thisrecord-as-and-sequence/

Regarding the needs that you mentioned, I think the solution I provided above could achieve your needs. If the 'ThisRecord' operator is not available on your side, you could modify your formula as below:

// relate R2s record to R3 record
ForAll(
       RenameColumns(R1Record.'Entity2 (prefix_Entity2_LookUpFieldName_Entity1)', "Entity2_Unique_Identifier", "Entity2_Unique_Identifier1"),
       Related(
                R3Record.'Entity2 (prefix_Entity2_LookUpFieldName_Entity3)',
                LookUp(Entity2, 'Entity2_Unique_Identifier' = Entity2_Unique_Identifier1)
       )
);
// relate R1 record to R3 record
Relate(
        R3Record.'Entity1 (prefix_Entity1_LookUpFieldName_Entity3)',
        R1Record
)

Please try it, if it could help in your scenario, please consider go ahead to click "Accept as Solution" to identify my reply as helpful.

Regards,