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
‎Oct 01, 2021 11:24 AM
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. ComplianceComputerTypeProperty & ComplianceComputerPropertyValue.
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
‎Oct 04, 2021 04:41 AM
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. ComplianceComputerTypeProperty & ComplianceComputerPropertyValue.
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
‎Oct 04, 2021 04:41 AM
thanks a lot @mfranz for the note , really appreciated.
Now the time for me to work on the custom scripting 🙂
Regards,
Junaid Vengadan
‎Oct 04, 2021 04:49 AM