Page 1 of 1

Cameron Custom Report using Custom Fields

Posted: Thu May 07, 2015 6:03 pm
by cgriffin
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.

Re: Cameron Custom Report using Custom Fields

Posted: Mon May 11, 2015 3:08 pm
by Lisa Ennis
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.

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'