How to create records in Sharepoint Lists based tables having lookup fields, without enumerating all fields from looked-up table?

[LapisLazuli]

Hi,

EDIT - This solution summary will save the reading of the whole thread

Given these two Sharepoint Lists, where:

• PatchedList.LkpColumn is a Lookup column to LookedUpList.LookedUpKey,
• LookedUpKey has unique values:

In order to add a record to PatchedList, here is the syntax, specifically to handle the lookup field:

Patch(
    PatchedList,
    Defaults(PatchedList),
    {LkpColumn: 
        {
        Id: 1,
        Value: "Lorem"
        },
        Title:"A Title",
        AnyOtherMandatoryField: "A value"
    }
)

- Please note: "Id", not "ID" or "id".

- The "Value:" keyword refers to the LookedUp field in LookedUp list, whatever its actual name is.

Here is the result, assuming the original Title field has been renamed to PatchedListKey, and there are no other mandatory fields:

----------------------------------------------------------------------------------------------------------------

ORIGINAL POST

Summary:

Using Patch to create a record in a table from a Sharepoint list which has a Lookup column returns an error message.

Context:

- ParentTable: A parent (looked-up) table, with many fields. In this table, column 'Name' is the unique key. It is different from the Sharepoint provided auto-numbered ID column.

- ChildTable; a child table. It has a 'ParentName' lookup column, referring to ParentTable, Column 'Name'.

Both tables are Sharepoint Lists.

Goal:

Insert a record in ChildTable.

Avoid to enumerate all ParentTable fields: only 3 fields among many optional ones should be filled in when creating the record.

Attempt:

Patch(ChildTable,
Defaults(ChildTable),
{
ParentName: LookUp(ParentTable, Name = "AnyExistingName"),

OtherFields: OtherValues
}
)

Result:

Invalid argument type. Expecting a record value, but of a different schema.

Missing column. Your formula is missing a column 'Id' with type 'Number'

Same error when removing the OtherFields: OtherValues lines. Confirms the error comes from the first line.

Investigations:

1. True, [LookUp(ParentTable, Name = "AnyExistingName")] does not have all the fields of the Sharepoint ParentTable list, specifically not the ID field.

2. In Sharepoint, displaying the ParentTable ListSettings confirms that the ID field is not listed in the Columns list.

Many attempts to work it around, like Lookup(AddColumns(ParentTable, "ID", Blank()) or Lookup(AddColumns(ParentTable, "ID", 0) , etc. all return an error message.

Also found on the Web a lot of threads, which suggest to use a "ATSIGNodata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser" syntax.

For this syntax to work, all of these threads suggest to explicitly list all fields of ParentTable, like this:

{ParentName:

{OddDataTypeSyntax,

field1:value1,

- - etc. - - ,

fieldn:valuen}

}

This should preferably be avoided. One reason is that there are many fields in the Parent table. The second reason is that some more optional fields might be added in the future, and the code should still work with these extra fields, provided naturally that the data connector to ParentTable is refreshed in PowerApp.

The question:

So, long story short, how can we create a record in ChildTable, filling up the ParentName lookup column?

Thank you.

[LapisLazuli]

@SebS 

Well noted your suggestion to handle manually the lookup column. Thanks, the good ole way!

However, there would be many drawbacks in my context. So I'd still prefer, if possible, to rely on the Sharepoint Lists' standard Lookup mode. 

[SebS · Accepted solution]

@LapisLazuli 

The issue is intelisense it's changing id to ID and i never notice that till I build the app 🙂

Two Tables

TestApp = ParentTable

TestApp2 = ChildTable

ChildTable under Name have lookup table to ParentName column in TestApp

TestApp List

TestApp2 list

Now I created an app

Button Add Parent contain below Code what will relate Parent to Child table column Name base on ParentNames

Patch(
    TestApp2,
    Gallery1.Selected,
    {
        Name: {
            Id: Dropdown1.Selected.ID,
            Value: Dropdown1.Selected.ParentName
        }
    }
)

from Dropdown I picking "Gary"

I pointing in gallery to second record to select it and I'm pressing button

Result joe change to Gary

Now in the SharePoint List lookup Column Name second record change to Gary what is a parent in TestApp List

Hope this helps

[LapisLazuli · Accepted solution]

@SebS 

Congratulations! You found the reason: It should not be ID, or id, but Id !!!

Very helpful, thank you. I'll summarize the solution in the original post, to save the other readers the need to browse through all this discussion.

[SebS]

@LapisLazuli 

I got a moment of free time so I just did this hands on I do not like not resolved problems and also this helps myself to educate more so win for me and win for You.

Thank You for nice summary my writing is chaotic that's why I appreciate your time to write all more clearly for others.