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