13 Replies Latest reply on Jul 3, 2013 3:36 PM by WayneMuelver

    I would like to be able to add a field to an existing table?

    wjpatterson937

      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?

        • 1. Re: I would like to be able to add a field to an existing table?
          sporobolus

          on 2013-06-18 14:26 wjpatterson937 wrote

          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").

           

          you don't describe why condition information is stored in a series of fields,

          or why you you would sometimes need more of them, but there are two approaches

          which might fit — one is to use a single repeating field, the other is to use a

          separate condition table; the latter would be better if condition is sort of a

          running history, or if condition is an "attribute" and some motors have the

          same attributes; but again, it's hard to tell from your description

          • 2. Re: I would like to be able to add a field to an existing table?
            wjpatterson937

            Steve,

             

            First and foremost thanks for your input, your questions, and ideas.

             

            The condition information is stored in each "Motor" field in the "Motor" table.

             

             

            For example in the First record of the" Motor" table;

            Field Names

            MotorID     MotorSerial     Motor01     Motor02     Motor03     Inspection_ID

            MTID_1234     2750080          0               1               0               INS_0002

            MTID_2345     8857428          0               0               1               INS_0003

             

             

             

            The "Designations" Table

            FieldNames

            Field_ID     Title                  DBTable

            Motor01     Power Supply    Motor          

            Motor02     Mounting           Motor              

            Motor03     Control Panel     Motor              

             

            I think I understand your concept of a "Condition" table,

            Together with the Designations table I could abstract the "condition fields" completely away from the individual modules or subassemblies.

             

            If I create 50 Fields in a "Condition" table each of which is assigned on a record by record basis to a module.

             

            I can support a dozen modules with 50 fields each. Instead of continuously adding to the module definitions only to end up creating 600 fields to do the same job as the 50 abstaracted fields.

             

            I am sure I will have some further thoughts and questions

            • 3. Re: I would like to be able to add a field to an existing table?
              DavidJondreau

              I'm a little lost in your description, but generally speaking, whenever a solution has a table has fields like Motor1, Motor2, Motor3, it likely should be split into two tables. One table for the Motor, one for the conditions of a specific motor. You'd get rid of the Motor1, Motor2 fields and instead have each of those pieces of data in separate records in a new table.

              • 4. Re: I would like to be able to add a field to an existing table?
                sporobolus

                on 2013-06-19 7:47 wjpatterson937 wrote

                 

                For example in the First record of the" Motor" table;

                Field Names

                MotorID     MotorSerial     Motor01     Motor02     Motor03     Inspection_ID

                MTID_1234     2750080          0               1               0               INS_0002

                MTID_2345     8857428          0               0               1               INS_0003

                 

                >

                 

                The "Designations" Table

                FieldNames

                Field_ID     Title                  DBTable

                Motor01     Power Supply    Motor

                Motor02     Mounting           Motor

                Motor03     Control Panel     Motor

                 

                I think I understand your concept of a "Condition" table,

                Together with the Designations table I could abstract the "condition fields" completely away from the individual modules or subassemblies.

                 

                If I create 50 Fields in a "Condition" table each of which is assigned on a record by record basis to a module.

                 

                that's a good start, but i'm with David: break it down a little further

                 

                it looks like one motor (or other module) can have multiple conditions

                (represented by the Motor01, Motor02 … fields); so rather than a a single

                Conditions record per module, holding multiple condition codes, i would make

                each condition a record, and link it back to the module it affects via the

                module's id

                 

                the fact that you have multiple tables for different types of modules makes the

                relational design a bit funky, but i infer that you aren't ready to refactor

                that part of the design, so i would include foreign keys in Condition for each

                of the possible module tables, and just leave them blank when they don't apply;

                suppose your other modules are something like this:

                 

                Conditions table:

                 

                condition_id (primary key)

                designation_id

                motor_id

                other_module_id

                still_other_module_id

                condition_value (0 or 1) (saw this in your example, but not sure what it is)

                 

                and Designations:

                 

                designation_id (primary key)

                designation_title

                applicable_modules (optional value list to filter designations by module, for

                selection purposes)

                 

                then if you have a Motor MTID_1234 with three conditions, you might have these

                records (simplified):

                 

                Conditions::
                motor_id      designation_id   value
                MTID_1234     1                0
                MTID_1234     2                1
                MTID_1234     3                0
                
                Designations::
                designation_id   title         applicable_modules
                1                Power Supply  Motor
                2                Mounting      Motor
                3                Control Panel Motor
                
                

                 

                (sorry i won't be able to return to this topic until the weekend)

                • 5. Re: I would like to be able to add a field to an existing table?
                  WayneMuelver

                  I agree with Steve and David . Instead of adding fields, I would also recomend that you create another table where recods can take the place of the fields. That way you can later add an infinate number of these as needed.

                  • 6. Re: I would like to be able to add a field to an existing table?
                    wjpatterson937

                    Thank you once again for you time and your input. The Design process for me is not so far along to prevent me from rethinking the approach.

                     

                    So I will read through your second post and consider that along with some other "structural" testing I am doing.

                     

                    When I have a more considered opinion to the entire set of answers, I will post back to you and the others.

                    • 7. Re: I would like to be able to add a field to an existing table?
                      wjpatterson937

                      Thank you for your input and ideas.

                       

                       

                      As in the above post I am actively considering all the input and have seen distinct advantages in the methodology you have recommended.

                       

                      I am actively working along those lines and will respond via this forum with results and ...more questions.

                       

                      Thanks

                      • 8. Re: I would like to be able to add a field to an existing table?
                        wjpatterson937

                        Thanks for your response and your questions.

                         

                        Based on the posts by you Steve and Wayne, I am rethinking the structure.

                         

                        I will get back to you via this forum with any further questions I may have and any additional bottlenecks I encounter.

                         

                        Thanks again,

                         

                        Bill

                        • 9. Re: I would like to be able to add a field to an existing table?
                          wjpatterson937

                          Here is where I am now.

                          I have created a table called modules which replaces all of my modules tables.

                          I have linked  Modules to Field Designations (through a join table)  and linked Modules to a table called "Conditions" (through a join table).

                           

                           

                          In doing this I can create what I would call a "Condition set" (actually it is a single record with "N" Condition fields (right now the largest number of conditions for any single module is 18).

                           

                           

                          To accomplish this the Field Designation table is very narrow (9 fields) and will ultimately be somewhat deep (1 record for each Field Designation, but ultimately I think that will be the case).  Each combination of Module and Designation will require the ability to be stored in a table somewhere. Right?

                           

                           

                          So I have tightened up and reduced the table structure and ultimately reduced the number of fields considerably. I am able to move the test data from the previous design and I get consistent results. I have made the leap to; a single "Conditions Table", a single "Designations Table", a single "Modules Table".

                           

                           

                          But I have this nagging sense that I am still missing something.

                           

                           

                          Is there a technique I can use to tighten the design further?

                          • 10. Re: I would like to be able to add a field to an existing table?
                            wjpatterson937

                            Here is where I am now.

                            I have created a table called modules which replaces all of my modules tables.

                            I have linked  Modules to Field Designations (through a join table)  and linked Modules to a table called "Conditions" (through a join table).

                             

                             

                            In doing this I can create what I would call a "Condition set" (actually it is a single record with "N" Condition fields (right now the largest number of conditions for any single module is 18).

                             

                             

                            To accomplish this the Field Designation table is very narrow (9 fields) and will ultimately be somewhat deep (1 record for each Field Designation, but ultimately I think that will be the case).  Each combination of Module and Designation will require the ability to be stored in a table somewhere. Right?

                             

                             

                            So I have tightened up and reduced the table structure and ultimately reduced the number of fields considerably. I am able to move the test data from the previous design and I get consistent results. I have made the leap to; a single "Conditions Table", a single "Designations Table", a single "Modules Table".

                             

                             

                            But I have this nagging sense that I am still missing something.

                             

                             

                            Is there a technique I can use to tighten the design further?

                            • 11. Re: I would like to be able to add a field to an existing table?
                              wjpatterson937

                              Here is where I am now.

                              I have created a table called modules which replaces all of my modules tables.

                              I have linked  Modules to Field Designations (through a join table)  and linked Modules to a table called "Conditions" (through a join table).

                               

                               

                              In doing this I can create what I would call a "Condition set" (actually it is a single record with "N" Condition fields (right now the largest number of conditions for any single module is 18).

                               

                               

                              To accomplish this the Field Designation table is very narrow (9 fields) and will ultimately be somewhat deep (1 record for each Field Designation, but ultimately I think that will be the case).  Each combination of Module and Designation will require the ability to be stored in a table somewhere. Right?

                               

                               

                              So I have tightened up and reduced the table structure and ultimately reduced the number of fields considerably. I am able to move the test data from the previous design and I get consistent results. I have made the leap to; a single "Conditions Table", a single "Designations Table", a single "Modules Table".

                               

                               

                              But I have this nagging sense that I am still missing something.

                               

                               

                              Is there a technique I can use to tighten the design further?

                              • 12. Re: I would like to be able to add a field to an existing table?
                                wjpatterson937

                                Can you shed more light on how I create a table where records take the place of fields.

                                 

                                I am sure I do not understand the relationship required.

                                • 13. Re: I would like to be able to add a field to an existing table?
                                  WayneMuelver

                                  Here is the way I see it...

                                   

                                  Your solution needs simplification and re-organization. You have a lot of calculations going on to produce meaningful record id fields that just don't need to be that meaningful. All they have to do is match and if the relationship is set to allow the creation of new records, and you create those new records with a portal, then you never have to worry about them matching, or meaning anything, much less even look at them. Don't use any of the items details as match fields. let Filemaker create separate IDs for each record either with the auto enter serial setting or as an auto enter calculation with the "Get (UUID)" function.

                                   

                                  For this I am assuming your users will be accessing the system with either a computer or an iPad. If they are using an iPhone or iPod, or Web Browser, you might want to do this a little different because of the screen size and other limitations, using list views to display both the equivalent portal data as well as the equivalent form data. All of that requires a lot more scripting though. So, to keep it simple... What you need is a Table for the whole piece of equipment. The primary key for that table should be related to a foreign key in another table for the subassembly. The primary key for the subassembly table should then be related to a foreign key in a component table. All relationships should be set to create records via that relationship. The Component table should have fields for Type and Status. Each of these fields should be setup as drop down value lists and select the option to allow them to be editable. This will allow the Manager to easily create new component types and component statuses as the system matures. For the layouts, the Equipment layout should have a portal that displays related records from the Subassembly table. The Subassembly layout should have a portal that displays related records from the component table and gives access to the drop down value list for the Type and Status fields. The Component layout should show all the details you want to track about any component wether it is a motor, a bearing, a chassis, whatever. Give access to the value lists for the Type and Status fields in the Component layout as well. Each layout with a portal should include buttons inline with the portal records with a navigation script to load the related record in the layout for that record type.

                                   

                                  To use this you will start with the Equipment layout and create a new record. Then click in the portal to create a new subassembly record and enter some of it's details. then you will click your 'go to related record' button and see the subassembly detail. There you will click in the component portal and create as many components as you need for that Subassembly. Each component can have it's own status and be marked with whatever type of component it is, motor or otherwise using the drop down value lists.

                                   

                                  These are all simple one-to-many relationships. If you want to extend this system further to track service records, that is when you would use a join table to create a many to many relationship between either the component and the service record, the subassembly and the service record, the equipment and the service records, or all of the above.

                                   

                                  Hope that helps explain it better.