cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SaWu
Level 8

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

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.
image.pngimage.pngimage.png
image.png

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)
        }
    )
)

image.pngimage.pngimage.png
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)));

image.png


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)));

image.pngimage.png

 


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.

1 ACCEPTED SOLUTION

Accepted Solutions
SaWu
Level 8

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

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));

image.png

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

View solution in original post

3 REPLIES 3
SaWu
Level 8

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

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
image.pngimage.png

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.

Highlighted
SaWu
Level 8

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

Hello

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

image.png
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
Level 8

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

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));

image.png

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

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 328 members 6,855 guests
Please welcome our newest community members: