Patch Lookup column dataverse

[Dycons]

Dear all, 

I'm struggling with a patch statement towards a lookup field in dataverse.  Let me try to explain my scenario.

I have following table defined in dataverse with the following relationships.

table : Inspection instruction

Further I made a gallery based on a collection. 

I want to patch the entries of the collection towards the table inspection instruction in dataverse

To achieve this, i used follwing code :

Patch(
    Inspections,
    Defaults(Inspections),
    {
        Reference: gbl_InspRef,
        'Internal Reference': gbl_InspIntRef,
        Date: dte_DateInsp.SelectedDate,
        'Executed By': drp_ExecutedByInsp.SelectedText,
        Remark: txt_RemarkInsp.Text
    }
);

ForAll(
    col_InspInstr,
    Patch(
        [@'Inspection Instructions'],
        Defaults([@'Inspection Instructions']),
        {Reference: GUID(),
        'Inspection Reference': LookUp([@Inspections],Reference in gbl_InspRef
        'Instruction Reference': LookUp([@Instructions],Reference in col_InspInstr.Reference)}
    )
);

The statement  : "'Inspection Reference': LookUp([@Inspections],Reference in gbl_InspRef" gives me follwing error : 

gbl_InspRef is a global variable defined in a previous screen and passed to this screen. (it's also used to patch into another table 'inspection')

The statement :  'Instruction Reference': LookUp([@Instructions],Reference in col_InspInstr.Reference) results in always the same record added, in this case C-VIK-026

as i should expect C-VIK-026 and C-VIK-026-E as is in my collection

What am i doing wrong.

You're help would be very appriciated

Kind regards

Steve 

[CNT]

@Dycons Firstly, LookUps and setting up a M-to-1 relationship does the same thing. Actually you are not using the relationship at all. You can choose which option you want. But I prefer Relationships as Dataverse does all the heavy lifting.

If you want to use the relationships, you patch all the other fileds and then you use the Relate command to set up the relationship,

Relate(Record in Inspection Instruction, Record in Inspection)

You can then use the dot(.) notation to refer to the related record,

Inspection1.InspectionInstruction

This will give all the Inspection Instructions for this Inspection.

Hope this helps!

[Dycons]

@CNT , thanks for your reply but i'm afraid i didn't got it.

I tried following code

ForAll(
    col_InspInstr,
    Patch(
        [@'Inspection Instructions'],
        Defaults([@'Inspection Instructions']),
        {Reference: GUID(),
        'Instruction Reference': Relate(col_InspInstr,Inspections.'Inspection Instructions')}
    )
);

But then i receive following errors :

[CNT]

@Dycons You can't have Relate and Patch nested. Patch is to Add/Modify records and Relate is to relate them into a relationship. Again, don't try to combine LookUp and Relate. You don't need both. Do the Patch first and then use the return value of the Patch to Relate.

ForAll(
    col_InspInstr,
    With({newInspectionInstructions=Patch(
        [@'Inspection Instructions'],
        Defaults([@'Inspection Instructions']),
        {Reference: GUID()}),
        Relate(col_InspInstr,newInspectionInstructions)
    )
);

[Dycons]

Dear @CNT ,

I really appreciate your suggestion but unfortunately i'm still struggling with your code above. I really don't get it. (pretty sure this is my bad 😉)

I understand that the combination of lookup and relate is absolutely not needed, but what i miss in your above suggestion is how can add (save) the lookup value (field instruction reference of the 'Inspection instruction' table), which is the same as the field reference in the col_InspInstr. (but the lookup in Inspection instruction table is towards instruction table)

So i don't understand why i have to use this code : 

        Relate(col_InspInstr,newInspectionInstructions)

As, col_InspInstr isn't the related table and newInspectionInstructions ony contains GUID() fields, not related to the inspection table, so i tried the following 

ForAll(
    col_InspInstr,
    With(
        {
            col_NewInspInstr: Patch(
                [@'Inspection Instructions'],
                Defaults([@'Inspection Instructions']),
                {Reference: GUID()}
            )
        },
        Relate(Instructions.'Inspection Instructions'.,col_NewInspInstr.'Instruction Reference');
    )
)

Which doesn't seems to work.

Sorry for my probably dumb questions 

Kind regards

Steve

[CNT]

@Dycons Ok, let leave Relate for another rainy day and just use LookUps. In your original code a bracket was missing. Maybe that could be the issue. So try this,

Patch(
    Inspections,
    Defaults(Inspections),
    {
        Reference: gbl_InspRef,
        'Internal Reference': gbl_InspIntRef,
        Date: dte_DateInsp.SelectedDate,
        'Executed By': drp_ExecutedByInsp.SelectedText,
        Remark: txt_RemarkInsp.Text
    }
);

ForAll(
    col_InspInstr,
    Patch(
        [@'Inspection Instructions'],
        Defaults([@'Inspection Instructions']),
        {Reference: GUID(),
        'Inspection Reference': LookUp([@Inspections],Reference in gbl_InspRef)
        'Instruction Reference': LookUp([@Instructions],Reference in col_InspInstr.Reference)}
    )
);

[Dycons]

Dear @CNT , thanks again for your quick reply.  Hopefully it will be a rainy day so i can further investigate the relate function.  🙂

As i try the above code still the instruction reference is always the first one in the collection col_InspInstr.

The result after patch and code above is always value C-VIK-019 

So it seems as col_InspInstr.Reference in the forall loop is not taken the next value. (or the lookup towards instruction table is not working)

Kind regards

Steve

[CNT]

@Dycons 

Try replacing the LookUp like this,

LookUp([@Instructions],Reference in ThisRecord.Reference)