Reusable Function for Retrieving the ID of a Lookup Item from it’s Name in Creatio (formerly bpm’online)

Lookup columns in bpm’online store an ID value, not the actual text. For example, the City lookup doesn’t store the City name itself, it stores the ID value for the selected City from the City lookup table. If you’re working with ESQ queries, this isn’t exactly apparent, since you’ll reference the “City” column and get back the related object with a name and ID, even though the real database column is CityId and stores only the guid of the selected City.

When need to set a lookup column programmatically, you’ll need this ID value. However, often, you’ll only have the name value. The function below is a reusable C# function that can be used in script tasks of processes or configuration services to retrieve the ID of a value from a lookup given it’s name.

// Usage: 
// var paidId = GetLookupValue("InvoicePaymentStatus", "Paid");

private Guid GetLookupValue(string Lookup, string Text)
{
    var select = new Select(UserConnection)
        .Column("Id")
        .From(Lookup)
        .Where("Name").IsEqual(Column.Const(Text)) as Select;

    return select.ExecuteScalar<Guid>();
}

To use this, simply pass the name of the Lookup table and the Name value and you’ll get back it’s ID. For example:

var cityId = GetLookupValue("City", "Phoenix");
ABOUT THE AUTHOR

Ryan Farley

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix, now Infor CRM, since 2001 and believes in sharing with the community. His new passion for CRM is Creatio, formerly bpm'online. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

7 Comments

  1. A helpful reminder and code when dealing with bpm’online

    Reply
  2. How would you do this in the front end JS?

    Reply
    • Hello Keith,

      In the client side code, you would use an EntitySchemaQuery. See this article for more details https://customerfx.com/article/an-introduction-to-performing-client-side-queries-using-entityschemaquery-in-creatio-formerly-bpmonline/

      As an example this would get the Id of the “Customer” value from the ContactType lookup:

      var esq = Ext.create(“Terrasoft.EntitySchemaQuery”, {
      rootSchemaName: “ContactType”
      });

      esq.addColumn(“Id”);
      esq.filters.addItem(esq.createColumnFilterWithParameter(Terrasoft.ComparisonType.EQUAL, “Name”, “Customer”));

      esq.getEntityCollection(function(result) {
      if (result.success) {
      var item = result.collection.first();
      var id = item.values.Id;
      console.log(“The Id for Customer on ContactType is ” + id);
      }
      }, this);

      Ryan

  3. Thank you very much.

    how would I add it to the insert. It is not accessible outside the function.
    insert.setParameterValue(“ActivityCategory”, id, Terrasoft.DataValueType.GUID);

    Having a JS block….
    Thank you

    Reply
    • That is the nature of javascript. The EntitySchemaQuery uses a callback when the data is retrieved. You’d have to either:
      1) Use it inside of the callback function
      2) Call a function which retrieves the Id value and then passes that to another function that performs the InsertQuery and uses the value
      Ryan

  4. Very helpful. Thank you very much.

    Reply

Leave a Reply to Keith Schmitt Cancel reply

Your email address will not be published.

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!