To display columns of an object as a lookup

Hello Sivaranjani,



unfortunately this is not possible to do with out of the box functionality.



Lookups must be pointing to a specific existing object in the database. In the first case with the "Data Entity" lookup this is possible to do because it is pointing to the "Section" lookup which already exists and has concrete values.

However, this second lookup "Data Entity Field" is dynamic and it has nowhere to be pointed because it's values will depend on something that is not selected by default. This lookup is empty until you select something from the "Data entity" lookup. But it's not possible in the system to create an empty lookup where the values will be passed  depending on the field that you select in other lookup. 



Lookups in general store already defined values, it is not possible to dinamically pass to them values from an object that is inside another object.



I will create a request for our R&D department and attach this case to it, so in the future they can take this into account while developing new functionalities.



Best regards,

Dariy

Dariy Pavlyk,

Thanks for the response!



Let us assume that the second Lookup is Static.

Can you please guide this situation on how to populate a lookup with columns of a section?



Scenario:

Lookup field --> "Opportunity Fields" 

This field should display all the columns available in the opportunity section.



Best regards,

Bhoobalan Palanivelu.

Hello,

We apologise for the delay in the response. Here is a solution that you could implement:

To begin with you could create a lookup based on the object VwEntityObjects. This lookup will contain the name of all the objects/tables in the system, and you can easily add it to your page.

Then, we need to find a way on how to create a table which contains all the column names from all the tables in the system, because by default there is no such table.  But there is a way in which we could implement this functionality.



To begin with, this query provides us all the columns in the database, along with the names of the tables where they are located:

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS;



So we can create a view based on this select query:

CREATE VIEW column_names_view

AS   

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS; 

GO  



Then we can go to the configuration section in Creatio and create an object, for example UsrTableColumns, where we will store all this information. The basic fields that we will need to create in this object are "UsrColumnName" and "UsrTableName". Then we need to save the object and publish it, and after that the table will be automatically created in the database.



After that we can insert the information provided by the view into our newly created table:

INSERT INTO UsrTableColumns (UsrTableColumn, UsrTableName)

SELECT  "COLUMN_NAME", "TABLE_NAME"

FROM column_names_view;



Then we can create a lookup in the system based on this table UsrTableColumns , and add it to the page where we want to use it. Then, using a business rule, we can filter the results of this field depending on the one that we have selected in the VwEntityObjects. We will need to verify whether the name of the object of the VwEntityObjects equals to the UsrTableColumns.UsrTableName.

Best regards,

Dariy