Get and set a Dataverse lookup column

Dustin Miner

The post provides sample code snippets for interacting with Dataverse’s lookup data type column in C# & JavaScript / TypeScript

Example scenario – multiple customer records associated to a company record

There is a N:1 relationship between the customer table and the company table. Within the customer table, there is a simple lookup data type column. This column contains three values which associate it to it’s parent record (a company record in this case).

  • the id of the company record
  • the entity type of the record e.g. account
  • the name of the record e.g. Contoso Pharmaceuticals Limited

In C#

Get a lookup column

EntityReference companyEntityRef = customerEntity.GetAttributeValue<EntityReference>("cpl_companylookup");
or
EntityReference companyEntityRef = (EntityReference)customerEntity.Attributes["cpl_companylookup"];

//Note: to ensure the lookup is populated, use
customerEntity.Contains("cpl_companylookup");

Set a lookup column

Guid companyId = Guid.NewGuid();
EntityReference companyEntityRef = new EntityReference(“cpl_company”, companyId);
customerEntity["cpl_companylookup"] = companyEntityRef;
or 
Entity companyEntity = crmSvc.RetrieveMultiple(new FetchExpression(enquiryXml)).Entities[0];
customerEntity["cpl_companylookup"] = companyEntity.ToEntityReference();

Microsoft.Xrm.Sdk.EntityReference object

Guid companyId = companyEntityRef.Id; //e.g.{CB11AAA1-9CA1-EA11-A111-000A1A1A0B00}
String companyLogicalName = companyEntityRef.LogicalName; //e.g.account
String companyName = companyEntityRef.Name; //e.g.Contoso Pharmaceuticals Limited

In JavaScript / TypeScript

Get a lookup column

var companyEntityRef = formContext.getAttribute(“cpl_companylookup”);
var companyid = companyEntityRef.getValue()[0].id; //{CB11AAA1-9CA1-EA11-A111-000A1A1A0B00}
var companyname = companyEntityRef.getValue()[0].name; //Contoso Pharmaceuticals Limited
var companytype = companyEntityRef.getValue()[0].entityType; //account

The companyEntityRef record (refer to the code below) is constructed from the results of a retrieveRecord Web API call (retrieve a customer record containing the lookup to company)

var companyEntityRef = [
{id: result["_cpl_companylookup_value"],
 name: result["_cpl_companylookup_value@OData.Community.Display.V1.FormattedValue"],
 entityType: result["_cpl_companylookup_value@Microsoft.Dynamics.CRM.lookuplogicalname"]}];

(See the common function in the appendix.)

Set a lookup column

var companyEntityRef = new Array();
    companyEntityRef[0] = new Object();
    companyEntityRef[0].id = companyid
    companyEntityRef[0].name = companyname
    companyEntityRef[0].entityType = 'account';
formContext.getAttribute("cpl_companylookup").setValue(companyEntityRef);

or

var companyEntityRef = [
   {id: formContext.getCompanyId(), 
    name: formContext.getCompanyName(),
    entityType: 'account'}];
formContext.getAttribute("cpl_companylookup").setValue(companyEntityRef);

(See the common function in the appendix.)

In Power Automate

Set a Dataverse lookup data type column in Power Automate

Appendix

TypeScript common functions

public static getLookupValue(executionContext: Xrm.Events.EventContext, field: string): any {
   const formContext = executionContext.getFormContext();
   const lookupValue = {
      id: formContext.getAttribute(field).getValue()[0].id,
      name: formContext.getAttribute(field).getValue()[0].name,
      entityType: formContext.getAttribute(field).getValue()[0].entityType,
   };
   return lookupValue;
}
public static setLookupValue(executionContext: Xrm.Events.EventContext, field: string, id: string, recordName: string, entityName: string): void {
   const formContext = executionContext.getFormContext();
   const lookupValue: any[] = [];
   lookupValue[0] = {
      id: id,
      entityType: entityName,
      name: recordName,
   };
   formContext.getAttribute(field).setValue(lookupValue);
}

References

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/types-of-fields

Post Views: 747

Related Posts