13 Replies Latest reply on Feb 3, 2014 6:01 AM by john.s

    Problem with Relationship?

    john.s

      Title

      Problem with Relationship?

      Post

           Once again I'm stuck and can use some help.

      This relationship shows what is being used to create various photography Jobs.  Some jobs are done for Customers  of various types (families, corporations, leagues) and other Jobs are done for Schools.  The Join_Customer_Jobs table creates a record every time a Job is created and resolves several many to many relationships.  Once a Job is completed it is signed off as Complete by setting a Completed Indicator.   As part of that sign off I need to add the number of Images that were sent to each School, a Job can be for multiple schools. The Join table has the schools stored in the _fk_SchoolCode field based on a checkbox input. I’m not sure where or how to store the image total.  Any help would be greatly appreciated.

      Screen_Shot_2014-01-28_at_6.44.54_PM.png

        • 1. Re: Problem with Relationship?
          philmodjunk

               If a job is for two schools, do you have two records in Join_customers_Jobs or just one?

          • 2. Re: Problem with Relationship?
            john.s

                 Just one...that's where I'm running into trouble.  The relationship is creating the record and I don't know how to create two or more (one for each school)

            • 3. Re: Problem with Relationship?
              philmodjunk

                   You need one record for each school that is a customer of that job. This doesn't sound like something you can do with check boxes.

              • 4. Re: Problem with Relationship?
                john.s

                     Sounds like I just hit a limitation of FM. I can't have the photographers create a separate job for each school because than I have multiple jobs for that Photographer when in fact it is one Job with Multiple schools.  If I can't create those records from the checkbox input then maybe I have to continue to do that on paper...that reduces the value of this system a lot.

                • 5. Re: Problem with Relationship?
                  philmodjunk

                       I am not suggesting that you create a separate job for each school. You have one record for each record in Job for each Job. I am suggesting that you need one record for each school in the Join table--which is a different table from where you have one record for each job.

                       And this isn't really a FileMaker limitation. You'd be dealing with the same data model issue in any relational database.

                  • 6. Re: Problem with Relationship?
                    john.s

                         After being at this DB thing for almost 8 weeks  I'm finding the limitations are in by knowledge not the system.  I've taken all the online courses I can find but they don't teach everything and if they did it wouldn't replace experience.  I've searched this forum for ways to create multiple records but all the examples either don't apply or are above understanding. Any help in pointing me in the right direction would be greatly appreciated.

                         I'm assuming I need some sort of script that loops...that is something I don't have any experience with or even understand yet.  When a job is created I currently run a script to create folders on the desktop and also print a tracking sheet.  My guess is I need that script to also creates these records based on the number of checkboxes selected in the schoolcode field.  And that's where I get stuck.

                    • 7. Re: Problem with Relationship?
                      philmodjunk

                           I don't really see a need here for a separate table of Schools linked to your join table. They are simply more customers in most respects--though with some special details due to how you interact with them. I don't really see a need for the Schools 2 to join_customer_jobs relationship here. The link between customers and the join table should server for all customers linked to a given job without needing to link in that added table. I would link a table of school specific details to the customer table rather than this join table. Then, selecting a customer automatically selects the school info as well.

                           What I have described for you from the beginning is to use a portal to the join table with a drop down list or pop up menu for selecting a customer (which may or may not be a school). That does not require a script as it is a simple, straight forward data entry task.

                           Put a portal to join_customer_jobs on your job layout. For each customer, select them from a drop down set up on the _fk_CustomerID field. If "allow creation of records via this relationship" is enabled for join_customer_jobs in the jobs to join_customer_jobs relationship, this becomes fairly simple to do.

                      • 8. Re: Problem with Relationship?
                        john.s

                             I have a portal to join_customer_jobs on the job layout. The drop down is set on _fk_CustomerID.  When I select a customer it creates the record in the join table.  I can not figure out how to select more than one customer for the job using a drop down.  When I select the second customer the _fk_customerID field changes on the join table.

                             Also, I have a calculated field on the job layout that creates a job name used for folders on the desktop, that job name needs to include a code for each customer(school) so a job for a girls swim meet done for the yearbook would look like this

                             YB_20140129_gswim_school1_school2_school3

                             The only way I have been able to get that job name is by using check boxes and they don't work for creating multiple records in the join table...one for each customer(school) listed on the job.

                             What am I missing?

                        • 9. Re: Problem with Relationship?
                          philmodjunk
                               

                                    When I select a customer it creates the record in the join table.  I can not figure out how to select more than one customer for the job using a drop down.

                               This field should be inside the portal row. To select a second customer select them on the second row of the portal to your join table.

                               

                                    Also, I have a calculated field on the job layout that creates a job name used for folders on the desktop

                               And this will be a different name for each customer for each job, correct? If so, then this should be set up via a calculation field in the join table, not the Job table as you will have one such folder for each customer for each job.

                          • 10. Re: Problem with Relationship?
                            john.s

                                 Thanks again Phil,

                                 I've gotten this working now…it took some time because I went back and redesigned some portions of the underlying structure to correct some of my newbie mistakes.

                                 To answer your question, only one Jobs folder is created using the Job name.  The other folders that were going to be created would have been sub folders within that folder…one for each customer (school) and the folder would be named only with the customer name.  We have decided to scrap the subfolder requirement because it created some workflow problems that we had not anticipated.  But I still need the individual records for each customer so I can record the number of images for each customer when I close the job.  And that now works.

                                 So I now have a layout set up to close a Job.  The layout is based on the Jobs Table with a portal to the Join_Customer_Jobs table.  The portal shows each customer for that Job and I have a field on the Join_Customer_Jobs table to store the number of images provided to that customer  Join_Customer_Jobs::ImagesProvided.  When the job is created the photographer clicks on a customer(school) and then clicks the next portal row to add another customer and it works just as you said it would.  What's causing a problem is if they change one of those customers it creates a blank record.  It would not be that big of a deal except now that blank record shows up in the portal.  In fact there is always one blank record in the portal.  I tried to filter the portal and only select records if the fk_CusomerID  not Is empty but that didn't work.  Is there a way to eliminate those records?  I thought I saw a post dealing with this but couldn't find it.

                                  

                            • 11. Re: Problem with Relationship?
                              john.s

                                   Correction to my previous post:  A new record is not created if they change a customer it is created if they clear or delete one.

                              • 12. Re: Problem with Relationship?
                                philmodjunk

                                     It doesn't sound like any new record is being created from your description. If "allow creation..." is enabled for the portal's table in its relationship, FileMaker automatically provides what looks like one extra blank portal row in the portal. That's the "add" row where a use can create a new record in the portal row by entering/selecting data in any editable field of that row.

                                     Please note that deleting a customer from this portal should not be done by clearing the individual fields. Instead the portal row should be deleted. If you click a noneditable portion of the portal row to select the entire row, delete record from the records menu will delete the portal row record instead of the parent record from the layout's table. Since this is a bit less than obvious, it's usually best to put a button in the portal row that uses either Delete Portal Row or a more sophisticated script for deleting the portal row. (This assumes that you allow deleting portal row records in Portal Setup...).

                                     For those interested, there's a script in the Known Bugs List database for deleting portal rows that can be imported into any FIleMaker file and which will delete the portal row of any portal where you correctly set up a button in the portal row to perform the script. Please note that it requires specific data be passed to it via a script parameter in order to work.

                                • 13. Re: Problem with Relationship?
                                  john.s

                                       Thanks Phil,

                                       I'll try to set up this Delete Portal Row button or script.  I do have "allow creation…." set and all records are being created.  My problem was having too many records and from your response I can see why…I was just clearing the field not deleting the portal row...so your solution should solve that problem.

                                       Thanks again