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
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
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:
(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"
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
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.
Thanks for your help.
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.
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:
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.
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
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.
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
Thanks so much for all your help! The database file you created answered all my questions...for now :-) Have a great day!
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!
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.