Data form collection fields for Constituent Attributes

David Wanless

Hi all,

How does one create a collection field for Constituent Attributes for a data form, respecting the type of the Value field?  So far I have the following draft code.

If someone also has the SQL code to use the resulting data and insert it into the relevant Attribute tables, that would also be handy.  I've not really thought about that yet, although I'm roughly familiar with the structure of Attribute tables. 

Thanks in advance for any help,

David.


        <FormField FieldID="ATTRIBUTES" DataType="XML" Caption="Attributes" CaptionResourceKey="$$attributes">

          <Collection>

            <Fields>

              <FormField FieldID="CATEGORY" DataType="Guid" Caption="Category" CaptionResourceKey="$$category">

                <SimpleDataList SimpleDataListID="958132a3-762a-4844-b7a1-f3a3098da95a">

                  <Params>

                    <Param ID="RECORDTYPE">

                      <Value>CAEDEB25-69A4-47BF-A8A3-9D6DD1577129</Value> <!-- Constituent -->

                    </Param>

                  </Params>

                </SimpleDataList>

              </FormField>

              <FormField FieldID="VALUE" DataType="String" Caption="Value" CaptionResourceKey="$$value" />

              <FormField FieldID="STARTDATE" DataType="Date" Caption="Start Date" CaptionResourceKey="$$start_date" />

              <FormField FieldID="ENDDATE" DataType="Date" Caption="End Date" CaptionResourceKey="$$end_date" />

              <FormField FieldID="COMMENT" DataType="String" Caption="Comment" CaptionResourceKey="$$comment" />

            </Fields>

          </Collection>

        </FormField>

 

Steven Finnell

Hey David,


I would suggest that you keep going with what you currently have and keep the VALUE set as a string as you can convert the values based off the CATEGORY value in your stored procedure that goes to insert the value into the table.


Here's how you'd work with it in sql:


SELECT

     CATEGORY = Node.Data.value('(CATEGORY)[1]', 'uniqueidentifier')

,    VALUE = Node.Data.value('(VALUE)[1]','nvarchar')

,    STARTDATE = Node.Data.value('(STARTDATE)[1]','datetime')

,    ENDDATE = Node.Data.value('(ENDDATE)[1]','datetime')

,    COMMENT = Node.Data.value('(COMMENT)[1]','nvarchar')

FROM @ATTRIBUTES.nodes('/ATTRIBUTES/ITEM') Node(Data)


And @ATTRIBUTES would be a parameter that your stored procedure takes in and has a type of xml. Essentially once you construct the collection (which is nothing more than xml) you just need to find a way to take that xml and parse the values back out into a tabular format. 


One thing to note is that when you work with collections you're always going to have the ITEM in the Nodes structure because of the way CRM constructs the collections. Also you might need to play around with the sql datatypes and sizes in the query.