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# and 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 (Dynamics 365)

Get a lookup column

var companyEntityRef = formContext.getAttribute("cpl_companylookup");
var companyid = companyEntityRef.getValue()[0].id; //e.g. {CB11AAA1-9CA1-EA11-A111-000A1A1A0B00}
var companyname = companyEntityRef.getValue()[0].name; //e.g. Contoso Pharmaceuticals Limited
var companytype = companyEntityRef.getValue()[0].entityType; //e.g. 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 = [
   {id: formContext.getCompanyId(), 
    name: formContext.getCompanyName(),
    entityType: 'account'}];
formContext.getAttribute("cpl_companylookup").setValue(companyEntityRef);
//or
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);

(See the common function in the appendix.)

In JavaScript (Power Pages)

Get a lookup column

var companyGUID = $("#cpl_companyid").val(); //e.g. CB11AAA1-9CA1-EA11-A111-000A1A1A0B00
var companyName = $("#cpl_companyid_name").val(); //e.g. Contoso Pharmaceuticals Limited
var companyEntityName = $("#cpl_companyid_entityname").val(); //e.g. Account

Set a lookup column

$("#cpl_companyid").val("CB11AAA1-9CA1-EA11-A111-000A1A1A0B00");
$("#cpl_companyid_name").val("Contoso Pharmaceuticals Limited");
$("#cpl_companyid_entityname").val("account");

Using liquid

$("#cpl_company_userid").val("{{ user.id }}");
$("#cpl_company_userid_name").val("{{ user.fullname }}");
$("#cpl_company_userid_entityname").val("systemuser");

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: 1,003

Related Posts