Today I needed to add 25 fields to Data Collector.  Where as it's totally doable using the correct application, it is the slow way. And since we work on development servers, the same changes need made on the production servers, so the time is duplicated.  There is a quick and safe (mostly safe) way to update ProForm Data Collector, from SQL Server Management Studio

First off this is not a supported method of updating the ProForm Standard or Enterprise search tables.  It works just fine as long as you respect the data types and lengths.  Bad use of this technique may be one of the reasons the "Refresh column lengths" button was added to Data Collector.

This method is for adding what I would call Duplicate Fields, or a list of fields to which we know have the same data format.  In this case I am adding all the Notes fields from the Tracking Items

First we add a few fields to Data Collector. All fields are not created equally, this only works for like type fields, be sure to add as many fields as you have data types for.  In this case we have one base field type which is identical for 25 entries TX##CMT fields.

Then Open SQL Server Management Studio (SSMS) and navigate to the ProForm Database, then to the UserTable you were updating.   In my case I updated UserTable03, you will probably be on UserTable01.

Right click on the UserTable you want to update and select Design.

 

 

 

 

Note the fields that you added, in this case I want add all Comment Fields to the table, So just add the fields, and type them as the examples. (This is why it was so important to get all field types, some text fields are 30 characters, some 50, some 10, that is critical to the proper operation of DataCollector)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Save the changes in SSMS, and fire up Data Collector you will see the new fields, and can update the database with for the date range you need.

 

 

 

 

 

 

 

If you add a field that has no match in ProForm, the bad field will show in Data Collector on the right side, ProForm will ignore the bad field on updates. But DON'T DO THAT.  Make sure all the field names are valid.  In past versions an error would be (and should be) thrown if the database is maligned like this.  IMHO people inaccurately doing what this post is about is why those errors went away.  Creating issues using these techniques could put your ProForm report tables at risk.  As long as you respect the data types, and ProForm uses a number of them, with various lengths, this technique saves time on mass updates.