10 Replies Latest reply on Dec 5, 2012 10:56 AM by philmodjunk

    Updating Layouts After User Selection Questions



      Updating Layouts After User Selection Questions


           I'm having two basic layout form problems:

           Problem 1:
           I have three tables:

           1. Projects

           2. Tasks

           3. Projects-Tasks (M:M resolver)

           In the relationships graph, see below, I have the expected joins constructed.

           In FMP on my "Projects-Tasks" layout, I want to have two drop downs where I can first select the Project and then have the Tasks Drop down automatically populated with the available tasks for that selected project.

           Since I've modeled this M:M relationship in the database, I was thinking FMP would handle this population automatically based on the foreign key relationship, but it's not working.


           Problem 2:

           I want to be able, on the "Tasks" layout, to select a Project in a drop down and then have all the task fields for that project update.

           I tried creating a script with GoToRelated record and other things like that, but didn't want to have to resort to manually changing each field with a SET FIELD.

           It appears that FMP isn't even storing the foreign key values in the related tables.


           These situations have always been confusing. It's possible the solution is so simple, I'm just not seeing it.

           Thanks to any suggestions and replies.

           - m


        • 1. Re: Updating Layouts After User Selection Questions

               Problem 1: Do you know how to create a conditional value list? That's what you are describing here.

               If you know how, you can set up such a value list, but you'll need to add another table occurrence of tasks to link to Projects via a new occurrence of your projects_Tasks join table to refer to in your value list set up. Otherwise, you don't have the needed Relationship to use from the context of your Project_Tasks based layout.

               I can reply back with links on how to construct a conditional value list if you need them, so let me know.

               Problem 2:

               I don't undestand the issue. Why would any task field update if you assign that task to a different project? How would the link to a project reord change those fields? If these are fields that look up data from projects, you have a many to many relationship and thus you have an issue as to which project record out of potentially many such related project records is supposed to provide that data.

          • 2. Re: Updating Layouts After User Selection Questions

                 Hi Phil, 

                 For problem 1 I just created a portal on the projects view. That seems fine. I can create new Tasks in the portal for each project.


                 For problem 2, the issue is that in the M:M resolver table itself, I need to be able to add detail on what work was done. There could also be multiple records.

                 Say for project 1, task 1, you needed to record (in the M:M join table), what work you did on that project and task. Later, you might do more work on that project 1, and task 1 (and add more records for each unit of work done) or other projects and tasks (also additional records to the Projects-Tasks join table).

                 I don't see how to model the Portal so that I can easily pick the project ID and Task ID and then enter 1 or more child records with the join-table details. The problem is that the Project and Task are two different tables.

                 Don't see any examples of this in any of my FMP books.

                 So, how do you actually set up the Portal (or whatever) so you can enter 1 or more records for each occurance, in this case, of a project + task?



            • 3. Re: Updating Layouts After User Selection Questions

                   1) I usually use a portal to the join table for such purposes. WIth such a portal on the Project layout, you can select tasks for the current project. With such a portal on the tasks layout, you can select projects for the current task.

                   2) I would create a new join table each time a user needed to log more work done on that specific project_task. A summary field can then compute the totals for either the project and/or a specific project_task. The alternative that you can use is a portal to a new related record--giving you a one to many link from Project_Tasks to this new "workLog" table where you would create a new record each time in that table to log ongoing work details.

              • 4. Re: Updating Layouts After User Selection Questions

                     Hi Phil,

                     2) Since you can't actually mean I would have to create a new table in the database each time I needed to add a record (time to ditch FMP if that's the case), I was wondering if you have a simple example of, say, the Teachers, Students, Students-Teachers type database you implemented with a Portal (or however).

                     I can't find an example of how to setup the M:M situation in FMP in a single book, web page, or anything.

                     Thanks in advance,


                • 5. Re: Updating Layouts After User Selection Questions

                       Yeah, I meant "create a new join table record". My fingers couldn't keep up with my brain...

                       Here's a demo file that I share with interested parties trying to figure out many to many relationships. It models an "Event Manager" where you have multiple events linked to multiple contacts. It shows the simplest layout design first, then demos several alternative methods for working with a many to many relationship.


                       If you are using FileMaker 12, use Open from the file menu to open and convert this file to .fmp12 format.

                       But a many to many relationship may not be as foreign a concept as you first think. If you are familiar with the typical invoicing solution that links an invoices table to a line items table to a products table, you already have a working demo of a many to many relationship. There are many invoices that link to many products and the LineItems table (invoice data in the most recent invoices starter solution) serves as the Join table linking them.

                  • 6. Re: Updating Layouts After User Selection Questions

                         Thanks Phil.

                         I'm well versed with M:M relationships, just not in FMP. I was able to get a basic STUDENT, TEACHER, STUDENT-TEACHER thing going but not the exact thing in my application.

                         I need to noodle on this a bit more.

                         Thanks again!!!

                         -- m

                    • 7. Re: Updating Layouts After User Selection Questions

                           Ok, here's a revised ERD and associated form. I'm using the M:M relationship correctly here I think (just using it as two 1:Ms).

                           My question now is how do I include additional related information from, say, the People table with what the user's name is for this task or even what his role is?

                           These are related tables, but are more than one table away from the Projects-Tasks table.

                           Do I need to add table occurences to include the user and role information on the form shown here in this posting or does the ERD need keys assigned differently for the Projects layout (on form shown here) to get this information?

                           Thanks in advance!


                      • 8. Re: Updating Layouts After User Selection Questions

                             Your relationships indicate that for any given task there is only one record in People assigned to it. Thus, you can include fields from People inside the portal row of your portal to Projects_Tasks.

                             However, your Role information is a 1:M relationship from Pople to People_Role, so by your relationships, More than one Role could be shown for the same person assigned to a specific Task. There is no way with the current design to show more than data from the first Role record linked to the specified People record. You'd need to set up a different relationship or possibly a filtered portal to show a specific role for a specific task.

                        • 9. Re: Updating Layouts After User Selection Questions


                               Quick follow up question.

                               In the people table I populate that field with the ID itself after they enter the first name and last name manually.

                               In the tasks table, though, I have a drop down picker so you can pick the person's name. Now, even though this is a foreign key field, FMP is storing the actual person's name text in the tasks table. Thus, I don't think the inner join will work between people and tasks.

                               How do you set the name in the tasks table so it will store the numeric ID of that name and not the name text itself.

                               This is yet another one of those things I can't seem to see how to get around.

                               Thanks in advance.


                          • 10. Re: Updating Layouts After User Selection Questions

                                 The simplest solution (not necessarily the best) to set up is to define a value list with two fields. The first (Primary) field refers to the ID number in People. The secondary field refers to the person's name. You may need to define a field with an auto-enter calculation that combines first and last name if you do not have such a field to use as your secondary field. This value list enables your user to select a people record by name, but it enters the ID number into the field.

                                 This method can be cumbersome to use if you have a large number of records in People. There are script enabled methods that allow you to use a text field that auto-completes on the name, but the associated script looks up and enters the ID number to link your record to the selected People record.