cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

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
Highlighted
Impactful Individual
Impactful Individual

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
Highlighted
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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

Highlighted
Impactful Individual
Impactful Individual

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,446)