Removing a field from a Pivotal form

There are times when we want to remove a field from a table. This usually is no problem, except when the field has already been added to a client form. If we remove the field from the table but we don’t remove it first from the form then we get an error saying the client form is wrong. Removing a field from a table doesn’t automatically remove it from the client form. We should always remove the field from the form first before removing it from the table. But what if we forget to do this? (or if we miss some forms, because there is no easy way to know in which forms the field is used).

Before I explain how to remove the field from the form I will give a little background on how fields are used in a client form. Client forms represent a view for data segments. Every client form has a primary data segment and zero or many secondary data segments. The primary data segment stores all the fields for the underlying table. Secondary data segments are used for secondary tables, tables with foreign keys to the primary table. When a field is added to a form, the form designer automatically adds the field to the corresponding data segment, and also adds it to the form definition (the view). So if we remove the field from the form, the form designer only removes it from the view, but it doesn’t remove it from the data segment. There are two ways to solve this problem.

The first way is to use the form editor. Just go to any field in the form (on the primary or secondary segment), then go to properties and edit the PivotalBindings property. Click in the DataField property and you will see all the fields associated with the data segment. From here you just use the “Delete data field” link to remove the unwanted fields. This will remove the field from the data segment. You can check that the field is actually removed by looking at the data segment.

Data segments can be found in the toolkit under the menu “Data Templates, Active & Device Forms”. Primary segments will always have the 1 ordinal. The problem is that the toolkit doesn’t allow you to edit the data template, since this could make client forms corrupted.

However, sometimes you can’t even access the form using the form designer. In this case we need to solve it using the second way. You can work around this by editing the data template associated to the form using SQL on the BM database. You can use this query to remove the field from the corresponding segment.

delete from ng_form_field f where f.ng_form_field_id in (
select ff.ng_form_field_id 
from 
  sc_form cf inner join 
  ng_form af on cf.active_form_id = af.ng_form_id inner join
  ng_form_segment fs on fs.ng_form_id = af.ng_form_id inner join
  ng_form_field ff on ff.form_segment_id = fs.ng_form_segment_id inner join
  table_fields tf on tf.table_fields_id = ff.field_id
where 
  cf.form_name = '<formName>' and
  fs.form_segment_name = '<segmentName>' and
  tf.field_name = '<fieldname>'
)

In the above SQL, you need to know the name of the form, the name of the segment, and the field name. The form and field name are easily known, but the segment name might be difficult to know. If this is the case, use the toolkit to find out the name of the segment by going to the menu “Data Tempaltes, Active & Device Forms” and look at the data segments that conform the client form and write down the name of the segment that has the field you want to remove.

All this could be avoided of course if we remember to remove the field from the form (using the first way described above) before removing the field from the table.