junaid_vengadan
Level 7

How to track custom added UI fields at Databse tables

Jump to solution

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
Level 15

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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

2 Replies
mfranz
Level 15

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

Softline Group is Europe's leading independent expert in Software Asset Management.
junaid_vengadan
Level 7

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

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

 

Regards,

Junaid Vengadan  

0 Kudos