Still struggling with patching a dropdown to a lookup column

[StefWS]

Hi,

After weeks of I'm still really struggling with patching to a lookup column.

So I have a very simple SPList, called Lst_Persons. There are only 2 columns in the list: cln_Name and cln_Level.

Name is just a text column but level is a lookup column reffering to a second SPList called lst_Levels. This one also just has 2 columns:

So I made a simple screen:

Level is populated directly from the lst_Levels, because I can't sort lookup columns.

When I try to collect this: 

Collect(
    Lst_Persons,
    {
        cln_Name: txt_Name.Text,
        cln_Level: {
            Id: drp_Level.Selected.ID,
            Value: drp_Level.Selected.cln_Levels}
    }
)

 It doesn't error but only name appears in the correct column, level stays blank.

You can see however, it does retrieve the ID and Value:

But when you try to patch it,

Patch(
    Lst_Persons,Defaults('Lst_Persons'),
    {
        cln_Name: txt_Name.Text,
        cln_Level: {
            Id: drp_Level.Selected.ID,
            Value: drp_Level.Selected.cln_Levels}
    }
)

it totally errors out.

[v-xiaochen-msft]

Hi @StefWS ,

According to the list and formula you provided, I tested but did not get a similar error, can you provide more detailed information?

Below is the test I did:

Create a list, called lst_Levels.

Create second list, called Lst_Persons, and column ‘cln_Level’ is a lookup column.

Create a screen.

The items of drp_Level are as follows.

The button 'Collect' uses the formula you provided:

Collect(

    Lst_Persons,

    {

        cln_Name: txt_Name.Text,

        cln_Level: {

            Id: drp_Level.Selected.ID,

            Value: drp_Level.Selected.cln_Levels}

    }

)

Click the button and save the data in the data source without problem.

The button 'Patch' uses the formula you provided:

Patch(
    Lst_Persons,Defaults('Lst_Persons'),
    {
        cln_Name: txt_Name.Text,
        cln_Level: {
            Id: drp_Level.Selected.ID,
            Value: drp_Level.Selected.cln_Levels}
    }
)

There were no errors.

Best Regards,

Wearsky

[StefWS]

Hi @v-xiaochen-msft ,

Thanks for helping. I build an entire new app from scratch and get the same results. Collect does not give an error, it collects the name, but the lookup column levels stays blank.

Patch generates the exact same problem.

What extra details can I provide you with?

Thank for the help!

Edit: today I tried getting the items via power automate. The scheme looks exactly the same:

I copy pasted and edited to manual values, like:

Collect(
    Lst_Persons,
    {cln_Name: "Jules",
    cln_Level: {
      '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      Id: 1,
      Value: "beginner"
    }
    }
)

and got the same result. "Jules" gets collected , beginner stays empty. Same with patch, where the formula errors out totally with same code as previous.

[WarrenBelz]

Hi @StefWS ,

Firstly, please ask yourself why you are using a Lookup column - I have never found them necessary and they simply add unwanted complication to your app (I have a blog that may be useful). However, to answer your question, your drop-down Items are not the Lookup column Choices (the Items are drp_Level.Selected.cln_Levels ), so you will not have the Id of the item in the other list looked up. I would personal just collect the Value and then get the Id when you patch the collection, but if you want a Table record with the data

Collect(
   Lst_Persons,
   {
      cln_Name: "Jules",
      cln_Level: 
      {
         Value: drp_Level.Selected.cln_levels,
         Id:
         LookUp(
            YourLookedUpListName,
            YourLookedUpFieldName = drp_Level.Selected.cln_levels
         ).ID
      }
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Drrickryp]

@StefWS 

Welcome to PowerApps h3ll.  https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-... 

See #1 and 2

[StefWS]

Hi @WarrenBelz ,

That formula also does not work. I'm wondering if there is something wrong on the sharepoint side.

I was directed to this post and hoped this would the answer, but alas also not.

I guess I just wanted it to work, because I feel it is the way it should work and I just wanted to get the hang of the basics, patching and collection any kind of control into sharepoint and have a solid foundation on the that, since my first real large project will depend alot on mainly putting a lot of things into sharepoint.

I guess I'll have to start and find a decent demo on how to make related sharepoint lists withoud lookup.

Thanks for the help and the very informative blog!

[StefWS]

@Drrickryp very funy but also very informative read! Should have read much sooner I guess.

[Drrickryp]

@StefWS 

It's never too late to learn something new. The best teacher is your last mistake.

[WarrenBelz]

Hi @StefWS ,

Firstly can you please post your code (in Text) that does not work. Also, you might have a read of this blog of mine on data structure. It is very easy not to use Lookup columns, just link to the other List in Power Apps and use the column (you would normally use in your Lookup column) in your drop-downs, then write back to a Text field in your "main" list. If however you want multi-choice, you need to use a multi-choice field in the "Main" list, but with none of the hassles of getting the Id back.

[StefWS]

Collect(
Lst_Persons,
{
cln_Name: "Jules",
cln_Level:
{
Value: drp_Level.Selected.cln_levels,
Id:
LookUp(
lst_Levels,
cln_Levels = drp_Level.Selected.cln_levels
).ID
}
}
)

I guess I am going to have to do it the way you describe. I just wanted to prevent users using the sharepoint backend making faulty choices.