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

Option 1 – Optional chaining

var company = formContext.getAttribute("cpl_companylookup")?.getValue()?.[0];
if (company) {
    var companyId = company.id; //e.g. {CB11AAA1-9CA1-EA11-A111-000A1A1A0B00}
    var companyName = company.name; //e.g. Contoso Pharmaceuticals Limited
    var companyType = company.entityType; //e.g. Account
}

Option 2

var companyEntityRef = formContext.getAttribute("cpl_companylookup");
if (companyEntityRef && companyEntityRef.getValue() && companyEntityRef.getValue().length > 0) {
    var companyId = companyEntityRef.getValue()[0].id;
    var companyName = companyEntityRef.getValue()[0].name;
    var companyType = companyEntityRef.getValue()[0].entityType;
}

(See the common function in the appendix.)

Set a lookup column

Option 1 – An array literal which is the modern way

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

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"]}];

Option 2 – Constructor syntax which is the verbose, older way

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");

In a HTML Web Template using Liquid (Power Pages)

Get a lookup column

{% for licenceApplication in getCompanyInLicenceApplication.results.entities %}
{
    "CompanyName": "{{ licenceApplication.cpl_companyid.Name | default: 'No Company' }}",
    "CompanyId": "{{ licenceApplication.cpl_companyid.Id }}",
    "CompanyLogicalName": "{{ licenceApplication.cpl_companyid.LogicalName }}"
}{% unless forloop.last %}, {% endunless %}

Set a lookup column

tbd

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,280

Related Posts