I would like to be able to add a field to an existing table.
I am working on an application which tracks information about specific types of equipment. By splitting the equipment information into individual modules or sub-assemblies I am able to track items on an individual piece of equipment, for instance I can track an electric motor used to move a piece of equipment through a work space and track the electric motor used to lift material, once the equipment is in the proper location of the factory.
I did this by naming the tracking fields in each module and creating duplicate of result as the result of a calculated field in a table I created to assign certain properties to the fields in the module table.
For example; I have a table named "Motors" with a field for the motor's make, model, power specification, and serial number.
I also have a set of fields containing a boolean field to indicate if the particular component of the motor is functional or in need of repair. These fields are titled: "Motor01", "Motor02", "Motor03", and so on. Finally I create a unique record ID field, which is generated automatically upon creation of a new record in the table. By tracking the Record ID for each module in a table I am able to track the history of each component, as well as the all of the components aggregated into a "Condition Report". I store the specific identification information unique to each motor in the "Motor" table. The information about the condition of the motor is also stored in the table in the fields: "Motor01", "Motor02", "Motor03", and so on. But the definition of the condition, and any applicable regulations reference is stored in a separate table called "Designation".
The "Designation" table has fields titled: "FieldID"; a calculated field, which yields a matching name to field names in a module;
For example; I concatenate the table name and a serial number to produce contents in the Field ID Records of "Motor01", "Motor02", "Motor03", and so on.
The table also includes a field for "Designation Text", a separate unique ID field (generated upon creation), a primary table reference (pointing back to specific module table of the same name), a secondary table reference point back to a second module (if needed), and a serialized sequence number used to calculate the field "Field ID".
When storing the designation text in a separate table I can build a relationship and use the designation text as a label in reports. I also keep the number of fields in each module table to an absolute minimum and the number of fields in the designation table to a minimum as well. By cross referencing table I can report on the condition of an entire piece of equipment or specific components.
Now here is the rub.
I can script a process to add new records to the Designation Table, maintaining the file and field naming discipline. I can present an interface which will look up and fill-in the information to correctly crete the next record for any module table.
Is there a methodology which would permit me to add new fields, one at a time, to a module table?
For example; The maintenance supervisor wants to add a couple of more items to the "Motors" table ("Motor04", "Motor05", and "Motor06").
With 12 modules and more on the way I would like to be able to add fields to the tables while I create a related record with the correct "Designation" information?