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:
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:
Thank you,
Sa Wu.
Solved! Go to 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.
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.
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
Thank you,
Sa
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.
User | Count |
---|---|
257 | |
110 | |
97 | |
52 | |
39 |