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");
A helpful reminder and code when dealing with bpm’online
How would you do this in the front end JS?
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
I added that code here so it is more nicely formatted https://pastebin.com/vGyD9vbS
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
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
Very helpful. Thank you very much.