Lookup field refering to a Virtual Object
It's quite common practice in OOP languages to create a pointer typed as to a base class, and assign an address of an object of a derived class.
If we have objects' hierarchy (inheritance) in bpm`online, it seems logical to have also an ability to create lookup fields in 3rd objects which would refer to virtual base objects.
Sample: we have an "abstract" Object A containing some common properties ("columns"), this Object is virtual. We create several derivatives (B, C) which are "real" Objects based onto A, they are represented by tables in DB. Now we would like to create another Object D which would have a field a_id of type Lookup which would point to the "abstract" Object A. So, actually we want the D to point to B/C depending of a certain row.
I surely understand why it is impossible currently: foreign keys which correspond lookup fields obviously cannot be created for non-existing tables (virtual objects).
Technically, such "foreign keys" might be emulated with SQL triggers.
Obviously such a_id will not be joinable via ESQ (again, because there is no table to which a_id "refers"). It does mean a query like:
var esqQuery = new EntitySchemaQuery(/*...*/, "D"); esqQuery.AddColumn("a_id.Name");
would not make any sense. Although a query like:
var esqQuery = new EntitySchemaQuery(/*...*/, "D"); esqQuery.AddColumn("[B:Id:a_id].Name");
makes sense.
Please consider this idea as a proposal to make bpm`online be closer to developers who came from OOP. Thank you.
Do you like this idea?
It sounds like applying "boxing" to the entity framework.
Please let us know if you have ideas on handling the following difficulties with it:
1. Indeed. It won't be possible to create a foreign key on the a_id lookup in the database. At the same time, it's not possible to let the a_id field to be without a key in the database due to data integrity.
2. Usability. It will be hard to explain "boxing" to the users who didn't read the base code. For example, if the boxing is in a dev package and a client works in Custom. Users don't have an open "visual studio" solution and it will be very hard for them to find out what objects are linked to the a_id field.
At the same time, please check how the "Customer" field works on the "Opportunity" page. It is linked to the "Contact" and "Account" tables at the same time.
Eugene Podkovka,
Thanks for your pushing me to Opportunity page, it perfectly fits the "appearance" requirement. However there present "low-level purity" issues.
In Opportunity, there is sorta magical "multiLookupField" component used in the page. In the object Opportunity there are still 2 different lookup columns (Contact and Account). The whole logic of keeping opportunities bound to mutually exclusive objects is maintained only by client-side code. Seems little not perfect, I'd say.
Let me answer to difficulties you've seen.
At the same time, it's not possible to let the a_id field to be without a key in the database due to data integrity.
Why can't we rely onto SQL triggers? Yes, it certainly involves some complexity of the whole system for Terrasoft's developers, I understand. But talking hypothetically, wouldn't it be better to implement this once on Creatio side instead of making every 3rd-party developer to dig & create such workarounds?.. Just a proposal of an idea, you know.
It will be hard to explain "boxing" to the users who didn't read the base code
Looks not much harder than to explain such "boxing" of Contact/Account in Opportunity page. Of course I'm not going to create completely insane hierarchy of not kindred B/C.
Please don't misunderstand me, but it seems really dirty to create N columns only one of which shall be non-null, and maintain this integrity solely on client-side.
Thank you for the answers. I'll let the development team know about the Idea.