One-to-many relationship and ForAll Patch

[jchenevert]

Hello,

I am trying to determine what the best methods are for relating new child records to parent records as they are created. Let me explain my setup. I have two tables one called parentDatabase with a one to many relationship with a table called childDatabase. The childDatabase table has a column that matches the unique value in the parentDatabase. I use that value in a lookup to relate the parent to the child record in a gallery.

(OnSelect property of a button in a gallery. Items are a filtered list of children records) - 

LookUp(parentDatabase, uniqueValue = ThisItem.childUniqueValue)."name of the relationship",ThisItem)

This works correctly but I want to see if I can relate new child records as they are created in a ForAll patch. I think my issue with the forall patch might be the scope in my relate formula. I use "ThisRecord" to refer to the record within the forall patch but PowerApps seems to think that ThisRecord within the LookUp in the relate function are columns from the parentDatabase rather than the current value in the forall patch. The only error I get is that the relate function has invalid arguments. 

Below I am trying to create the child record from values in a collection, then lookup the parent record to relate with the newly created child record. 

ForAll(collection, 
    Patch([@childDatabase],Defaults([@childDatabase]),
        {
		childDatabaseRow: Column in collection,
		childDatabase value to link back to parent: value in parentDatabase
        }
    );
    Relate(LookUp(parentDatabase, uniqueValue in Parent = ThisRecord."value in parentDatabase").call_relationship,ThisRecord.id)
);

Is this approach correct? How can I get the scope of my formulas refer to the correct record?

I have spent a lot of time reading about the relate formula in the forums and microsoft's docs. I know that you can't put a use the relate function in a patch but I did not see any information saying you can't use it in a forall. Here is a forum post that tries to address a similar problem but ends up not using the relate function. Patch Lookup column dataverse - Power Platform Community (microsoft.com)

I appreciate any help or suggestions you can give.

[AmDev]

Hi @jchenevert 

Can you try introducing 'As' to rescope your Forall source and its associated records (I have went with 'ThatRecord', but this can be anything) - this allows you to then refer to the outer record inside a nested function, without it being confused with the inner record.

ForAll(collection As ThatRecord, 
    Patch([@childDatabase],Defaults([@childDatabase]),
        {
		childDatabaseRow: Column in collection,
		childDatabase value to link back to parent: value in parentDatabase
        }
    );
    Relate(LookUp(parentDatabase, uniqueValue in Parent = ThatRecord."value in parentDatabase").call_relationship,ThatRecord.id)
);

Hope this helps - Good luck!

[jchenevert]

@AmDev 

Thanks for the quick response. Let me do some testing on my end to see if using "As" fixes my scope issue.

[jchenevert]

@AmDev 

I restructured my screen to make the patch happen at a different time so I can isolate the Relate in the ForAll statement. When I nest the relate function in a forall I get this error after using the "As" operator.  I don't know if it makes a difference but when I mouse over the relationship portion of the formula, I get the "The specified column is not accessible in this context". Otherwise I get the "The function 'Relate' has some invalid arguments". I have verified the relationship name is correct. 

//Filters table by a selected record ID in previous screen (contains >=1 records) 
ForAll(Filter([@childTable], childUniqueRecord_id= selectedRecord.id) As tableRecord,
    Relate(
        //tableRecord.uniqueChild links to one crd2e_parentTableUniqueValue
LookUp([@parentTable], crd2e_parentTableUniqueValue= tableRecord.uniqueChild).crd2e_relationship,
        tableRecord
    )
);

I have also tried removing the Relate formula to just have the Lookups within the for all. If I do that, I don't get any error. So it seems that the error occurs in within the Relate function but only in the first LookUp and using the "As" operator does not correct the context issue.

[AmDev]

@jchenevert - I was hoping for an easy fix with 'As' on your first expression.

The only thing I would say is to try and troubleshoot the Relate, by taking it outside the ForAll and mimic the relation context i.e. first child record to parent relation field and see how that goes, before trying to work back into the ForAll.

I don't have that much experience with Dataverse, so don't want to waste any more of your time. With that I'm going pass the baton onto those more familiar with dataverse than I.

@CNT 

@WarrenBelz 

Good luck

[WarrenBelz]

Sorry @AmDev . I am a SharePoint user.

[AmDev]

Apologies @WarrenBelz ... 

Me too!..

[dragonhry]

your tip was really usefull here.