Good afternoon,
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?
Thank you,
Colin.
Cameron Custom Report using Custom Fields
Moderator: Lisa Ennis
-
- Posts: 84
- Joined: Thu Sep 11, 2008 1:57 pm
Re: Cameron Custom Report using Custom Fields
There are not views or other tables that will display the custom fields, however you can create them. You do have to
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.
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##"])[1]','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'
Lisa Ennis
Senior Report Developer
SoftPro
Senior Report Developer
SoftPro