Hi,
I am trying to load an Excel Table into a Sharepoint List with Contacts. The contact records have a Lookup field to link to a Company name from another SP list. In the Excel table there is a column with company name, and now I am trying to load contacts from Excel and link all contacts to their respective companies. The company names already exists in the Company SP list. (I have preloaded them).
I am importing the data from Excel to a Collection and then I am trying to use Patch to load the data. But I can't figure out how to store the company name into the lookup field.
I am using the following code:
ClearCollect(colContacts;
Filter(
Filter(
FirstN(PAImport; 5);
IsMatch(Organisationsnummer;"\d{6}-\d{4}";MatchOptions.Complete ) ||
IsMatch(Organisationsnummer;"\d{6}-XXXX";MatchOptions.Complete)
);
!StartsWith(Organisationsnummer;"2")
)
);;
/* Save to Sharepoint */
ForAll(colContacts;
Patch(Kontakter; Defaults(Kontakter);
{
'Namn (field_Column2)': Kontaktperson ;
Epost:Mailadress;
Organisation:
Table(
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
Id:1;
Value:'Bolag/Aktör'
}
)
}
)
)
Any help would be appreciated! 🙂
Found the problem:
I needed to LookUp the ID of the Company in the Company database.
{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference";
Id: LookUp(Verksamheter; Verksamhetsnamn = 'Bolag/Aktör').ID;
Value:LookUp(Verksamheter; Verksamhetsnamn = 'Bolag/Aktör').Verksamhetsnamn
}