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.
