cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to track custom added UI fields at Databse tables

i have recently added few UI fields in the FNMS on-premise version , most of them are either Integer or text fields .

 

But how can i see them in respective tables ?

For example if added few fields in contracts , which db tables i need to refer to see and manipulate this values.

 

My other intention is to bring this fields in custom query written in business adapter ( connected mode )

 

Regards,

Junaid Vengadan

(1) Solution
mfranz
By Level 17 Champion
Level 17 Champion

Hi,

In most cases there are 2 tables involved per object. One "mapping" table and one "value" table. As you might know, for some objects custom fields can be limited to specific object types, like in this case to a specific contract type. This is stored in the "mapping" table. Values are stored in the "values" table. 🙂

SELECT *
FROM ContractProperty

SELECT *
FROM ContractPropertyValue

And here is an example how they go together:

SELECT
	c.ContractName
	,cp.PropertyName
	,cpv.PropertyValue
FROM Contract c
JOIN ContractProperty cp
	ON c.ContractTypeID = cp.ContractTypeID
LEFT JOIN ContractPropertyValue cpv
	ON c.ContractID = cpv.ContractID
		AND cp.ContractPropertyID = cpv.ContractPropertyID

The same or similar schema applies to most objects, e.g. ComplianceComputerTypePropertyComplianceComputerPropertyValue.

In productive scenarios this can be annoying as you have to join each single property, at least if you want to have them in distinct columns.  Or pivot the data. I've also seen the query performance being suboptimal in large environments with heavy customizing. Temp tables can help to mitigate this.

Best regards,

Markward

View solution in original post

(2) Replies
mfranz
By Level 17 Champion
Level 17 Champion

Hi,

In most cases there are 2 tables involved per object. One "mapping" table and one "value" table. As you might know, for some objects custom fields can be limited to specific object types, like in this case to a specific contract type. This is stored in the "mapping" table. Values are stored in the "values" table. 🙂

SELECT *
FROM ContractProperty

SELECT *
FROM ContractPropertyValue

And here is an example how they go together:

SELECT
	c.ContractName
	,cp.PropertyName
	,cpv.PropertyValue
FROM Contract c
JOIN ContractProperty cp
	ON c.ContractTypeID = cp.ContractTypeID
LEFT JOIN ContractPropertyValue cpv
	ON c.ContractID = cpv.ContractID
		AND cp.ContractPropertyID = cpv.ContractPropertyID

The same or similar schema applies to most objects, e.g. ComplianceComputerTypePropertyComplianceComputerPropertyValue.

In productive scenarios this can be annoying as you have to join each single property, at least if you want to have them in distinct columns.  Or pivot the data. I've also seen the query performance being suboptimal in large environments with heavy customizing. Temp tables can help to mitigate this.

Best regards,

Markward

thanks a lot @mfranz  for the note , really appreciated.

Now the time for me to work on the custom scripting 🙂

 

Regards,

Junaid Vengadan