By using this website, you agree to our Terms of Use (click here)
Here are some notes about User-Defined Fields (UDF) vs. Attributes.
I created an Opportunity where the same Test Attribute was used as both a UDF and an Attribute:
Neither one gets picked up by the Universal Search:
When it comes to adding the fields to a Generic Inquiry or Report, it’s basically the same thing:
- My UDF could be found at CROpportunity.AttributeA01.
- My Attribute could be found at CROpportunity.A01_Attributes.
I added both the UDF and Attribute to my Generic Inquiry, then ran a trace on the Generic Inquiry and noted the following SQL code that got generated:
- UDF: ((SELECT [CROpportunityKvExt].[FieldName] as '@type', COALESCE(CONVERT(VARCHAR(30), ValueNumeric), CONVERT(VARCHAR(23), ValueDate, 121), ValueString, ValueText, N'') as '*' FROM [CROpportunityKvExt] [CROpportunityKvExt] WHERE ( [CROpportunityKvExt].[CompanyID] = 3) AND ( [CROpportunityKvExt].[CompanyID] = 3) AND [CROpportunityKvExt].[RecordID] = [CROpportunity].[NoteID] FOR XML PATH('v')) ) AS [CROpportunity_AttributeA01],
- Attribute: ((SELECT [AttributeID] as '@type', [value] as '*' FROM [CSAnswers] [CSAnswers] WHERE ( [CSAnswers].[CompanyID] = 3) AND ( [CSAnswers].[CompanyID] = 3) AND ( [CSAnswers].[RefNoteID] = [CROpportunity].[NoteID]) FOR XML PATH('v')) ) AS [CROpportunity_Attributes],
You can see in the SQL code above that the UDF gets physically stored in the CROpportunityKvExt table while the Attribute gets stored in the CSAnswers table.
The CROpportunityKvExt table only stores fields related to Opportunities. The CSAnswers table stores fields related to anything that can track an Attribute (Opportunities, Items, etc.). Based on this, I would suspect that it takes a little longer to retrieve an Attribute from CSAnswers than a UDF from CROpportunityKvExt since there is more data in CSAnswers to sift through.
Also, I’m not an expert in SQL, but I would expect the FOR XML PATH statement to return a comma delimited list of fields that would then need to be parsed. This is another reason why I would suspect that it takes a little longer to retrieve an Attribute from CSAnswers than a UDF from CROpportunityKvExt.
Otherwise, it seems to me that User-Defined Fields (UDF) and Attributes are pretty similar. Most of the screens that I’ve noticed only allow you to choose one or the other. The Opportunities (CR304000) screen is kind of unique in that it allows you to use both User-Defined Fields and Attributes.
Also, I’m not an expert in SQL, but I would expect the FOR XML PATH statement to return a comma delimited list of fields that would then need to be parsed. This is another reason why I would suspect that it takes a little longer to retrieve an Attribute from CSAnswers than a UDF from CROpportunityKvExt.
Hi Tim , In case of a “simple” attribute,If I dont joint the table in a GI and I attempt to filter on the attribute directly i get the error “Cannot perform an aggregate function on an expression containing an aggregate or a subquery.” So I need to join the CSAnswer table for a guiven Appointment by example.
In case of a UDF, how to join the UDF table for a given Appointment in order to filter on this column? I read that there is a “KvExt table “ but i cannot find it in the “Table tab” of GI.
Thanks for all !
Raphael
@raphael-fournier Hmmm, I haven't looked at this in a while. I think we'd have to run a trace on a Generic Inquiry to see the SQL code that gets generated when joining to CROpportunityKvExt. Maybe it's clean. If not, since CROpportunityKvExt is not available in the Generic Inquiries TABLES tab, I think we'd need to create a SQL View to do the join.
Hello - I had heard that attributes are being replaced by UDF over time. Just curious if others had heard/understood this as well?
Online Members
