Reusable Function for Retrieving the ID of a Lookup Item from it’s Name in 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;
        
    using (var db = UserConnection.EnsureDBConnection())
    {
        using (var reader = select.ExecuteReader(db))
        {
            if (reader.Read())
                return reader.GetColumnValue<Guid>("Id");
            else
                return Guid.Empty;
        }
    }
}

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. He loves C#, Javascript, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

1 Comment

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

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

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

You have Successfully Subscribed!