8 Replies Latest reply on Jan 13, 2017 6:58 AM by user28177

    Is it possible to have multiple fields linking to the same table, without using portals?

    user28177

      Hello,

       

      My name is Paulo and I work for a production company. Here we produce Feature Films, TV Shows and also TV Ads.

       

      I’m working on a FileMaker solution to better control the information of each job. (I'm new to FM)

       

      I have a table called CONTACTS, where I store information about all the people that have worked for us. In each job, a person can have a different role, so I didn’t create a role field, but a join-table called CAST_N_CREW. (ex. A person can work as an assistant director in several movies and then became a diretor, so there will be some job records where the person had a role of an assistant director, and some more recent jobs where the person has a director role).

      The ROLES table could be replaced by a value list, since it has around 20 role records (Director, Producer, Editor, etc).

       

      Screen Shot 2017-01-11 at 7.22.05 PM.png

       

      All that works fine.

       

      What I need to do is to connect the roles (Director, Director of Photography, Executive Producer, Editor, etc) to CONTACTS, where when committed, will create a CAST_N_CREW record for each field, with that contact, and that role.

       

       

      Ex.

       

      I've two Contact Records:

       

      Contact Record 01: "Steven Spielberg"

      Contact Record 02: "Bill Butler"

      I've one Job Record: "JAWS"

       

      In the cast panel, the user clicks in the director popup menu field and it shows all contact records (in the example case, Steven Spielberg and Bill Butler).

      The user then selects one of the contact name for the director role, Steven Spielberg.

      Next, the user selects Bill Butler as a Director of Photography.

       

      FileMaker then creates two new records for CAST_N_CREW, where one has Steven Spielberg as a director for JAWS and another with Bill Butler as a Director of Photography for JAWS.

       

      Anyone has any idea on how can I do this?

       

      Thank you!

       

      Best Regards,

       

      Paulo

       

      Screen Shot 2017-01-11 at 7.12.11 PM.png

        • 1. Re: Is it possible to have multiple fields linking to the same table, without using portals?
          philmodjunk

          Use new table occurrences and relationships to set up these relationships:

           

          Jobs::anyField X Roles|Assignment::AnyField

           

          Roles|Assignment::gJobID = Cast_N_Crew|Assignment::Jobs_ID_FK AND

          Roles|Assignment::Roles_ID_PK = Cast_N_Crew|Assignment::Roles_ID_FK

           

          Enable the "Create" option for Cast_N_Crew|Assignment in this relationshihp.

           

          gJobID is a field of the same data type as Jobs_ID_FK, but with global storage specified. Use the OnRecordLoad trigger on the Jobs layout to perform this one line script:

           

          Set Field [Roles::Assignment::gJobID ; Jobs::Jobs_ID_PK ]

           

          This will keep the relationship to Cast_N_Crew|Assignment correctly up to date.

           

          Now put a portal to Roles_Assignment on your Jobs layout. Include the Role field so that this portal lists all possible roles in the portal. Add the Cast_N_Crew|Assignment::Contacts_ID_FK field to this portal row and format it with a value list of contact IDs and names from the Contacts table.

           

          With this set up, each time you select a contact in one of the portal rows, you create a new record in Cast_N_Crew for that Role and Job unless a record for that combination of ID's has already been created. If such a record exists, this action will simply update that record to link it to the new contact.

          1 of 1 people found this helpful
          • 2. Re: Is it possible to have multiple fields linking to the same table, without using portals?
            user28177

            Dear philmodjunk,

             

            I can't thank you enough!! It worked perfectly!!!

             

            Thank you!!!!

             

            All the Best,

             

            Paulo

            • 3. Re: Is it possible to have multiple fields linking to the same table, without using portals?
              user28177

              Dear philmodjunk or anybody willing to help,

               

              After the successful answer to the problem I had, I'd like give one step ahead and ask if it's possible to create an even more specific roles relationship. Since we work with both advertising and entertainment projects, there are certain roles only applicable to advertising work, such as creative director and RTV (Radio and TV Specialist). I've made two different jobs layouts, one with fields there are specific to entertainment jobs, and another specific to advertising jobs. Since both kinds of jobs share the vast majority of roles, such as director, producer, editor, etc.  The ideal solution would be to store all roles in the same roles table, entertainment and advertising, but it makes no sense to see an empty field for creative director in a feature film record. So I'd like to know if it's possible to somehow create another table occurrence and/or relationship where the type of role would be filtered and advertising roles would only appear in advertising jobs.

               

              Remembering that all entertainment roles are also present in advertising work, so the best way is to hide advertising roles from entertainment jobs, and show all roles for advertising jobs.

               

              Note that there is only one job table that's been used for both advertising and entertainment jobs, but they have different layouts with their appropriate fields. Contacts are the same, and the notion that contacts can have different roles in different jobs also work for advertising (we even had an advertising art director became a director, so the roles should be very flexible).

               

              Thank you all!!

               

              Best,

               

              Paulo

               

              • 4. Re: Is it possible to have multiple fields linking to the same table, without using portals?
                philmodjunk

                Add Job Type text fields to Roles and Jobs. In Roles, format the field with check boxes so you can select more than one job type for a role. Change the relationship from Jobs to Roles to use these two new fields And use the standard = operator instead of X.

                 

                Note that if you select RTV and Film for a given Role, it will appear in the portal for Jobs of type RTV and also for Jobs of type Film.

                1 of 1 people found this helpful
                • 5. Re: Is it possible to have multiple fields linking to the same table, without using portals?
                  user28177

                  Dear philmodjunk,

                   

                  Thanks again for helping me out. This time however, I have a question.

                   

                  Adding the job type text field to Roles and Jobs went fine. I've also created a value list for the checkbox containing only two options: Entertainment and Advertising. I've also linked the table occurrence Roles_Assignment to Jobs using the recently created JobType text fields, and changed the operator to = instead of X. However, now the portal does not show anything. So then I switched back the operator to X, created a filter in the portal:

                   

                  Roles_Assignment::Department = "ADVERTISING"

                   

                  The portal began showing all advertising roles, but when a role contains both advertising and entertainment checked, the portal doesn't show the record at all.

                   

                  So I went back to the = relationship for Roles and Jobs, and deleted the filter.

                   

                  Can you help to further clarify this question?

                   

                  Thank you again!

                   

                  Best,

                   

                  Paulo

                  • 6. Re: Is it possible to have multiple fields linking to the same table, without using portals?
                    erolst

                    user28177 wrote:

                    So then I switched back the operator to X, created a filter in the portal:

                     

                    Roles_Assignment::Department = "ADVERTISING"

                     

                    The portal began showing all advertising roles, but when a role contains both advertising and entertainment checked, the portal doesn't show the record at all.

                    To test for inclusion, use

                     

                    not IsEmpty ( FilterValues ( "Advertising" ; Roles_Assignment::Department ) )

                    • 7. Re: Is it possible to have multiple fields linking to the same table, without using portals?
                      philmodjunk

                      What I described will work. Make sure that you select at least one check box value in every role record first. You may need to set up a layout for managing your Role records.

                       

                      Make sure that you have a job type selected on every Job record before you try

                       

                      Make sure that both fields are of type text. Make sure that the values exactly match.

                      1 of 1 people found this helpful
                      • 8. Re: Is it possible to have multiple fields linking to the same table, without using portals?
                        user28177

                        And it did!!

                         

                        I've added the Job type field in the job layout as a radio button. That what was missing in my first attempt.

                        Now when I select advertising in the job layout, the portal shows only Roles records containing advertising.

                         

                        Perfect!

                         

                        Thanks so much!!

                         

                        Best,

                         

                        Paulo