Moderator: Lisa Ennis
We have a Custom Field attached to the property page in Cameron. We are trying to retrieve that data. According to other posts, you need to use the pf,Order and pfm.Entity tables, and retrieve it from the column: Custom#.
However, this is XML. Are there views or another table, where it would just return the data?
use the pfm.Entity table and join to the pf.OrderItemType table to retrieve the custom fields.
Below is sample code for retrieving custom fields from the XML. Hope this helps.
Code: Select all
SELECT ISNULL(O.Custom#.value('data(//Field[@name="OrderCF##"])','VARCHAR (10)'),'') AS [OrderCF##] FROM pfm.Entity O INNER JOIN pf.OrderItemType AS OIT ON O.Type#=OIT.ID AND OIT.Name='Order' WHERE O.RootId# = -2147483647 AND OIT.Name='Order'