Need help with setting Lookup Fields. Patch/Relate with ForAll (?) not working.

[SaWu]

Hello

Please be so kind as to read my full post before responding.
Thank you for your consideration.

In my current PowerApp I have problems with one custom entity to set LookUp fields to another custom entity.
The preview features "Relational data, option sets, and other new features for CDS" (default on) is turned on.
For reasons which I won't discuss here, I require this feature to remain turned on.
Because my PowerApp is intended for offline use as well, I don't directly use SubmitForm etc directly, but Collect update records into a table LocalToSubmit and have a manual upload button with something like

ForAll(LocalToSubmit, Patch(...))

I have one custom entity PowerFair-Lead (kk_messeapp_lead, 'PowerFair-Leads'  ) that has LookUp fields to two other custom entities PowerFair-Config (kk_messeapp_config, 'PowerFair-Configs'  ), and PowerFair-Standteam (kk_booth_team, 'PowerFair-Standteam'  ) with fields/columns:

• 'PowerFair-Leads'[@kk_messeapp_config_id]
• 'PowerFair-Leads'[@kk_booth_team01_id]
• 'PowerFair-Leads'[@kk_booth_team02_id]
• 'PowerFair-Leads'[@kk_booth_team03_id]

creating a many-to-one relationship between PowerFair-Lead and PowerFair-Config, and 3 many-to-one relationships between PowerFair-Lead and PowerFair-Standteam.



I don't know how to use Patch directly to set these LookUp fields.
My attempts in doing this for 'PowerFair-Leads'[@kk_messeapp_config_id] only produce errors:

Patch([@'PowerFair-Leads'],
    {
        kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank())
    },
    Patch({},
        LookUp(TmpSubmit, TmpSubmit[@kk_messeapp_leadid] = LocalToSubmit[@kk_messeapp_leadid]),
        {
            kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank()),
            kk_messeapp_config_id: LookUp([@'PowerFair-Configs'], kk_messeapp_configid=configId)
        }
    )
)

TmpSubmit is just LocalToSubmit without some offline-local-columns.
As a workaround, I don't set the LookUp field with Patch, but add a second loop using Relate instead.
This works, it produces no errors, and it produces records with a properly set LookUp field 'PowerFair-Leads'[@kk_messeapp_config_id] in Dynamics 365

ClearCollect(TmpPatched, AddColumns(LocalToSubmit, "real_id", Patch([@'PowerFair-Leads'],
    {
        kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank())
    },
    Patch({},
        LookUp(TmpSubmit, TmpSubmit[@kk_messeapp_leadid] = LocalToSubmit[@kk_messeapp_leadid]),
        {
            kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank())
        }
    )
).kk_messeapp_leadid));
ForAll(TmpPatched, Relate(LookUp([@'PowerFair-Configs'], kk_messeapp_configid=configId).'PowerFair-Leads', LookUp([@'PowerFair-Leads'], kk_messeapp_leadid=real_id)));

However, trying to do the exact same thing for the remaining 3 LookUp fields for PowerFair-Standteam simply doesn't work.
It seems to work for a single record.
It doesn't seem to work inside ForAll and it only some (for me) obscure error "The specified column is not accessible in this context".

Relate(LookUp([@'PowerFair-Standteam'], kk_booth_teamid=First(TmpPatched).kk_booth_team01_guid).'PowerFair-Leads', LookUp([@'PowerFair-Leads'], kk_messeapp_leadid=First(TmpPatched).real_id));
ForAll(TmpPatched, Relate(LookUp([@'PowerFair-Standteam'], kk_booth_teamid=kk_booth_team01_guid).'PowerFair-Leads', LookUp([@'PowerFair-Leads'], kk_messeapp_leadid=real_id)));

My questions:

1. How do I set all my LookUp fields?
2. What is happening here?
3. Is it a problem with 

Thank you,
    Sa Wu.

[SaWu]

Hello

I did some further testing with some trial error using a new lookup field connecting to the standard account entity.
The problem seems to be the use of a "loop variable" in the first argument of Relate.
Using a static LookUp in the first argument works.

Here some screenshots and a piece of code

ForAll(TmpPatched,
    Relate(LookUp([@Firmen], accountid=First(TmpPatched).kk_account_guid).kk_account_kk_messeapp_lead_account_id, LookUp([@'PowerFair-Leads'], kk_messeapp_leadid=real_id))
);
ForAll(TmpPatched,
    Relate(LookUp([@Firmen], accountid=kk_account_guid).kk_account_kk_messeapp_lead_account_id, LookUp([@'PowerFair-Leads'], kk_messeapp_leadid=real_id))
);

Still my question remains:
How do I properly set my lookup fields with the OnSelect event of my Upload button?

Thank you,
    Sa Wu.

[SaWu]

Hello

The documentation clearly says that it is supposed to be possible to set LookUp fields using Patch.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate

1. How does this work?
   
2. Do I have to somehow Patch the Related table (of argument1 of Relate)?

Thank you,
    Sa

[SaWu · Accepted solution]

Hello

Through even more trial and error I found a solution.
Apparently those LookUp fields don't work with Patch to create new records.
They work well enough with Patch to update a data source.
So moving the setting of LookUps to the level of patching the data source instead of first compounding a record to upload works.

ClearCollect(TmpPatched, AddColumns(LocalToSubmit, "real_id", Patch([@'PowerFair-Leads'],
    {
        kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank())
    },
    Patch({},
        LookUp(TmpSubmit, TmpSubmit[@kk_messeapp_leadid] = LocalToSubmit[@kk_messeapp_leadid]),
        {
            kk_messeapp_leadid: If(submitted, kk_messeapp_leadid, Blank())
        }
    ),
    {
        kk_messeapp_config_id: LookUp([@'PowerFair-Configs'], kk_messeapp_configid=configId),
        kk_account_id: LookUp([@Firmen], accountid=kk_account_guid),
        kk_contact_id: LookUp([@Kontakte], contactid=kk_contact_guid),
        kk_booth_team01_id: LookUp([@'PowerFair-Standteam'], kk_booth_teamid=kk_booth_team01_guid),
        kk_booth_team02_id: LookUp([@'PowerFair-Standteam'], kk_booth_teamid=kk_booth_team02_guid),
        kk_booth_team03_id: LookUp([@'PowerFair-Standteam'], kk_booth_teamid=kk_booth_team03_guid)
    }
).kk_messeapp_leadid));

Thanks anyway.
Maybe someone else will find this useful.
    Sa Wu.