06 February 2008

Controlling the Result Type for Calculate Fields

I recently was faced with the problem of rendering a calculated field using a feature. The feature creates and provisions a set of columns and content-types available for use in the site. However, one of the problems I faced was creating a calculated field that needed to render it's output as currency. Since SharePoint users the defaults when defining new fields, it was performing the calculations correctly, but displaying them as raw, unformatted text. When looking through the SDK, there doesn't appear to be any way to control the content (or it has not yet been documented). So, I decided I'd let SharePoint help me out. I created a new list and added several currency columns to the list. I then created a calculated column that would subtract the last currency number from the first currency number. Next, I saved the list as a list definition and downloaded it to my local computer. The .stp file that is created is nothing more than a CAB file. I appended the filename with .cab and then opened it up. Inside is a manifest.xml file that contains the definition of the list (if you're ambitious enough, you could create your own list definitions and upload them by reversing the process). Opening the manifest.xml file, I then locate all of the field definitions for the list. This is where I found the payload. There is an attribute that can be added to a calculated column definition to control the result of the calculation. It is the ResultType attribute. So, here's my definition:

          <Field Type="Currency" DisplayName="Val1" Required="FALSE" 
                Decimals="2" LCID="1033" ID="{66df31d6-dd22-4a17-8d4f-5f4b9a1be790}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" 
                StaticName="_x0056_al1" Name="_x0056_al1" ColName="float1" 
                RowOrdinal="0" Version="1"/>
          <Field Type="Currency" DisplayName="Val2" Required="FALSE" 
                Decimals="2" LCID="1033" ID="{66c6624c-88fb-4d26-9c5b-3615548b76db}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" 
                StaticName="_x0056_al2" Name="_x0056_al2" ColName="float2" 
                RowOrdinal="0" Version="1"/>
          <Field Type="Calculated" DisplayName="Var" Format="DateOnly" 
                Decimals="2" LCID="1033" ResultType="Currency" ReadOnly="TRUE" 
                ID="{a415e859-b8f9-4168-a63d-e5fbcbbfba7b}" 
                SourceID="{02d65904-8cbb-407d-9668-67441581ed38}" StaticName="Var" 
                Name="Var" ColName="sql_variant1" RowOrdinal="0">
                <Formula>=_x0056_al2-_x0056_al1</Formula>
                <FieldRefs>
                   <FieldRef Name="_x0056_al1"/>
                   <FieldRef Name="_x0056_al2"/>
                </FieldRefs>
          </Field>
As you can see, the calculated field's result type is controlled by the ResultType attribute. I took this new found knowledge back into my project, plugged it in and everything worked just as expected!

No comments: