11 Replies Latest reply on May 7, 2013 12:51 PM by Stephen Huston

    Is it possible to automatically create a new field?

    BillisSaved

      Hi, my name is Bill and I used to use Access.

       

      I'm beginning development of my first database in FileMaker Pro 12, so this is probably a very basic question. Here's what I've done so far... I've create a table (Required Training) which contains 26 possible training courses. I've created a second table (Job Titles) which will contain records for specific job titles and their associated training. I've added a drop-down list to the Job Titles table which displays all of the training courses contained in the Required Training table.

       

      Now here's where I'm stuck...Each job title, as you may have guessed, has a unique combination of training requirements. When I'm creating a new record for the Job Titles table (typically in form view), I need to enter between 1 and 26 required training courses. Is there a way to have FileMaker Pro 12 automatically create an additional field for entering required training each time I select one from the drop-down list? In other words, I would like the Job Title form to always display one blank field for an additional required training entry, regardless of how many total existing entries there are. For example, if I create a record for a job which requires 3 training courses, the form would display three fields containing those entries and one blank field. Now let's say there is a new training course added to the requirements for this job title, I would like to be able to return to that record, enter the new course and have a new blank field available (i.e. 4 completed fields and 1 new blank field).

       

      I hope my description make sense. Thanks in advance for any help or advice you offer. Have a great day!

       

      God bless,

      Bill

        • 1. Re: Is it possible to automatically create a new field?
          taylorsharpe

          Make sure youre relationship has the box checked to allow creation of records in the related table. 

          Untitled-1.jpg

          • 2. Re: Is it possible to automatically create a new field?
            sporobolus

            on 2013-05-03 15:28 theitquest wrote

            I'm beginning development of my first database in FileMaker Pro 12, so this is probably a very basic question. Here's what I've done so far... I've create a table (Required Training) which contains 26 possible training courses. I've created a second table (Job Titles) which will contain records for specific job titles and their associated training. I've added a drop-down list to the Job Titles table which displays all of the training courses contained in the Required Training table.

             

            Now here's where I'm stuck...Each job title, as you may have guessed, has a unique combination of training requirements. When I'm creating a new record for the Job Titles table (typically in form view), I need to enter between 1 and 26 required training courses. Is there a way to have FileMaker Pro 12 automatically create an additional field for entering required training each time I select one from the drop-down list? In other words, I would like the Job Title form to always display one blank field for an additional required training entry, regardless of how many total existing entries there are. For example, if I create a record for a job which requires 3 training courses, the form would display three fields containing those entries and one blank field. Now let's say there is a new training course added to the requirements for this job title, I would like to be able to return to that record, enter the new course and have a new blank field available (i.e. 4 completed field

            s and 1

            n

            ew blank field).

             

            to answer your direct question, no, it is not possible without heroics to

            dynamically create fields, but i don't think you need to

             

            underneath your question is a classic relational design problem; the standard

            solution would be to use a join table to associate trainings with job titles on

            a many-to-many basis; to help clarify the purposes of the tables, i would name

            what you called "Required Trainings" simply Trainings, and call the join table

            "Training Requirements"

             

            Job Titles <- Training Requirements -> Trainings

             

            to form the join, Training Requirements should contain foreign keys to the main

            tables, e.g. Job Titles id and Trainings id

             

            if this stretches your understanding, here is FileMaker's brief on the topic:

             

            <http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro>

             

            (it is also possible to use a repeating field in Job Titles, with each

            repetition relating to the Trainings table; easier to set up, but not "pure"

            relational design)

             

            then there is the question of building an interface for setting up these

            records; the typical approach is a portal which displays records from Training

            Requirements (the join table), with the allow-creation flag on the relation,

            and a pop-up menu based on the primary key of the Trainings table, but

            displaying the Training name; i tried to find a pre-written example of this but

            ran out of time so i'll leave it for a follow-up or perhaps someone can link to

            an example

            • 3. Re: Is it possible to automatically create a new field?
              BillisSaved

              Hey Steve,

               

              Thanks so much for taking the time to provide such a thorough answer; it makes sense to me. If I have time this weekend I'll attempt to implement your solution and, assuming it works (I don't see why it wouldn't), I'll come back and mark your answer as correct. Thanks again for your help! Have a great day.

               

              God bless,

              Bill

              • 4. Re: Is it possible to automatically create a new field?
                BillisSaved

                Hey Taylor,

                 

                Thanks for your help.

                 

                God Bless,

                Bill

                • 5. Re: Is it possible to automatically create a new field?
                  taylorsharpe

                  I know you this isn't exactly what you need, but your question also poses the question of whether it is possible in FileMaker to dynamically make fields and alter the schema.  No, FileMaker doesn't do that natively.  But there is a pretty cool plugin I use called MMQuery from CNS Plugins that does this pretty well and it is a really slick plugin.  Of course it can be quite dangerous too, but it will dynamically create new fields in the schema from a script or calculation engine. 

                  • 6. Re: Is it possible to automatically create a new field?
                    BruceRobertson

                    There are other data models for handling this sort of thing.

                     

                    Though it is possible that in fact new standard FileMaker fields need to be defined, it may really be that new ATTRIBUTES need to be defined; and these attributes belong in a related record set.

                     

                    This brings up EAV - Entity Attribute Value data model.

                     

                    There is a good description here:

                    http://en.wikipedia.org/wiki/Entity–attribute–value_model

                     

                    There are a few people doing things with EAV in FileMaker.

                     

                    I suspect Jeremy Bante may have some good comments on this if he spots the thread and chooses to reply.

                    • 7. Re: Is it possible to automatically create a new field?
                      BillisSaved

                      Hey Steve,

                       

                      I've implemented the join table and associated relationships you suggested, which seems to work perfectly, but I'm having a bit of trouble populating the records in the Required Training table (the join table) the way that I would like. My goal is to add a drop-down box for both the Job Titles and the Training Courses, which the user can use to populate the Required Training table. The drop-down boxes work as they should, but I would like to be able to add all the training courses required by a specific job title to one record, as opposed to having say 3 records with the same job title and different training courses. For example:

                       

                      Job Title01     Training Course01     Training Course02     Training Course03

                       

                      Instead of...

                       

                      Job Title01     Training Course01

                      Job Title01     Training Course02

                      Job Title01     Training Course03

                       

                      My problem is, I can't seem to figure out how to automatically create the additional Training CourseXX fields as needed. Any suggestions? Thanks for your help.

                       

                      God bless,

                      Bill

                      • 8. Re: Is it possible to automatically create a new field?
                        sporobolus

                        on 2013-05-06 10:22 theitquest wrote

                         

                         

                        I've implemented the join table and associated relationships you suggested, which seems to work perfectly, but I'm having a bit of trouble populating the records in the Required Training table (the join table) the way that I would like. My goal is to add a drop-down box for both the Job Titles and the Training Courses, which the user can use to populate the Required Training table. The drop-down boxes work as they should, but I would like to be able to add all the training courses required by a specific job title to one record, as opposed to having say 3 records with the same job title and different training courses. r help.

                         

                        with a join table, you don't establish all the connections in one record;

                        instead, you use one record for each additional connection; it may seem

                        redundant, but it keeps the data model clean, and then you just have to make

                        the interface intuitive for the end user

                         

                        to build a simple but intuitive interface for specifying the requirements,

                        start with a layout based on Job Title; add a portal based on *Required

                        Training*; i will attach a minimal example file following this message (i use

                        email for the forum, so i have to go to the web to add files)

                         

                        in the example file, first take a look at the relations, specifically the

                        foreign keys in the join table and the allow creation setting

                         

                        the main interface would be like the job titles layout, see how the pop-up

                        menu in the portal adds a training requirement to a job; note that the pop-up

                        is based on the id field, and the value list is set to displays the training

                        name for that id

                         

                        try adding a new job and specifying trainings for the job; by switching

                        layouts, you can see that records are automatically added to the *Training

                        Requirements* table; the portal is not the only way to construct the interface,

                        but it's easy to set up and good enough for many simple cases

                         

                        add new trainings in the training courses layout and note that they become

                        available to be required for jobs

                         

                        a layout like training requirements (based on the join table) may be useful

                        for maintenance purposes, but typically one would not let the end users access

                        this layout

                        • 10. Re: Is it possible to automatically create a new field?
                          BillisSaved

                          Hey Steve,

                           

                          Thanks so much for all your help! The database file you created answered all my questions...for now :-) Have a great day!

                           

                          God bless,

                          Bill

                          • 11. Re: Is it possible to automatically create a new field?
                            Stephen Huston

                            Coming late to this discussion. Not really on the point of how to create the related fields you need, but addressing the basic question about creating fields via the interface:

                             

                            When in list view, if the user has database design permission for the table of that layout, buttons to create new fields (Create Field, or "+") do appear along the top row while in Table View. Clicking one will create a new field with a default "field1, field2" name without leaving the record-browse mode!

                             

                            createField1.png

                            and

                            createField2.png

                            This particular feature can create problems if you have any non-developer users who use the Full Access account or enable Define Database permissions for them.