Cameron Custom Report using Custom Fields

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Cameron Custom Report using Custom Fields

Post by cgriffin » Thu May 07, 2015 6:03 pm

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.

Lisa Ennis
Posts: 80
Joined: Thu Sep 11, 2008 1:57 pm

Re: Cameron Custom Report using Custom Fields

Post by Lisa Ennis » Mon May 11, 2015 3:08 pm

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'
Lisa Ennis
Report Developer
SoftPro

Post Reply