Acumatica User-Defined Fields (UDF) vs. Attributes  

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.