9 Replies Latest reply on Mar 31, 2017 9:30 AM by philmodjunk

    How Does One Connect Records?

    rlyons115

      Hi Everyone....

       

      Quite the novice with Filemaker thus far. Did some essential training over at Lynda.com and in many aspects I'm pretty comfortable with using Filemaker. We're using it to develop a survey tool for our customers. We have two tables in the database set up as a one to many relationship. One project will have many location entries. The problem I am facing is that using a list view, I can see all of the location entries regardless of the selected project. What I don't know is if this is a sorting issue or if there were steps I needed to take to associate a given project with the entries for said project. I'm sure the reason I can't find an answer online is that this is so elementary that I should know already or I'm asking the wrong question or a combination of both. Either way, I'm a bit lost, and could really use some help.

       

      I need multiple entries to be associated with one project and a way to display the entries for that project.

       

      Hopefully this makes sense. Really didn't want to have to ask, but any help would be greatly appreciated.

        • 1. Re: How Does One Connect Records?
          philmodjunk

          If you specify an occurrence of the location table in Layout Set Up | Show records from, your layout will list all records from that table unless you do something more to produce the desired found set of just the locations related to a specific project.

           

          I suggest that you set up a portal to locations on your Projects layout. This will only list the locations for a given project and this is the simplest way to get such a list. It can be done with your list view, but it takes some additional scripting that you as a "newbie" might not yet be ready to do.

           

          And you might consider this issue as the next thing to study:

           

          Could more than one project have the same location? If so, a one to many relationship won't work for you in the long run. You'd need a many to many relationship--which is usually set up using a join table between projects and locations.

          • 2. Re: How Does One Connect Records?
            rlyons115

            Thank you for your help/suggestions. I'll give the portal suggestion a try.

             

            As for your question... no, more than one project would not have the same location. In this instance, location is usually referring to the location of an electrical panel and/or the designation of that panel. So one building (project) with multiple panels or switchgear (location).

            • 3. Re: How Does One Connect Records?
              rlyons115

              The portal is blank in browse mode no matter what I do.

               

              I think I am missing a fundamental step. There doesn't seem to be any connection between my project (parent) records and the survey (child) records.

               

              Screen Shot 2017-03-28 at 3.48.44 PM.png

              I suppose I don't understand whatever is needed to associate Parent Record A with Child Records 1-10. They seem to be two separate sets of data that aren't interacting. When using a button to go from the Project layout page to go to the first Survey Layout, Location, what is needed to link the parent record to the child? I imagine this is really basic, but I can't find the answer. Really frustrated right now.

              • 4. Re: How Does One Connect Records?
                keywords

                At first glance that that relationship looks correct, but a couple of things come to mind that might be at issue:

                1.     Each table should have its own primary key field, and this should be meaningless, unique data that is automatically assigned when each record is created—usually a serial number or a UUID.

                2.     The foreign key field in your Surveys table should be of the same field type, but should not be auto-filled, as it needs to hold the primary key value from its parent record.

                It is not clear from your screenshot that you actually have a primary key field in the Survey table, and that makes me wonder whether you may have inadvertently set the foreign key field up as an auto enter, as for a primary key—if so, data will not match.

                If you think of relationships as parent–child, each parent has a name, say Alice, and each child has a name: Betty, Chris, Dana and Edna. But each child also needs to have their parent's name in order to connect them to their parent. If you substitute in the foregoing primary key for name, and foreign key for parent's name you should see the logic.

                • 5. Re: How Does One Connect Records?
                  philmodjunk

                  What I have described does not use the surveys layout at all.

                   

                  The _fkProjectID field is not correctly defined. Open field options for this field and remove both of these settings if found:

                  unique values validation

                  auto-entered serial number

                   

                  Presumably, you have done the following:

                  a) used the portal tool to draw a rectangle on the projects layout.

                  b) selected Surveys from the "Show Related Records" drop down

                  c) added fields from surveys to the portal row

                   

                  If so, your portal should work, but records from surveys will only appear in the portal if the value of _fkProjectID equals the value of __pkProjectID on the layout's current project record. You may have to update existing survey records by finding them on the survey layout and giving them the correct ID value.

                   

                  To add new records to Surveys, you can double click the relationship line linking these two tables and selecting Allow Creation.... for Surveys. This will cause a special "Add" row to appear in the portal below the last survey record. If you enter data into this blank row, FileMaker creates a new survey record and copies over the current project record's ID into the new record's matching ID field.

                   

                  There are many options here. I've just spelled out the most simple, direct way to do this. Learn all you can about portals as you will use them a lot.

                  • 6. Re: How Does One Connect Records?
                    rlyons115

                    Thank you both for your help so far, it has been invaluable. Still having some issues, which I may post about in a bit. Wanted to see if everything looked correct as far as the database is concerned.

                     

                    Screen Shot 2017-03-29 at 2.22.39 PM.pngScreen Shot 2017-03-29 at 2.22.57 PM.pngScreen Shot 2017-03-29 at 2.23.19 PM.png

                    • 7. Re: How Does One Connect Records?
                      philmodjunk

                      Remove the "can't modify" option on the _fkProjectID field. It must be a modifiable field--even though the user generally is not allowed to directly edit it.

                      • 8. Re: How Does One Connect Records?
                        rlyons115

                        A portal is probably the way to go, though admittedly I tried to set up a list for quite a while with no real luck.

                         

                        I'm a bit frustrated right now.... as the portal will only let me input data there instead of displaying data. I don't want to input anything on this layout, just display and have any easy way to get back to the layouts where they input the associated data.

                        • 9. Re: How Does One Connect Records?
                          philmodjunk

                          The portal can display data. But the match fields have to match. If you have records that you created before working out the needed relationship and field changes, you will need to go in and modify the match field values to correctly match records. Then they will appear on your layout. You might do this:

                          Go to a record on the Projects layout. Copy the value of _pkProjectID to the clip board. Then switch over to the surveys layout. Find a survey record for that project that does not appear in your portal. Paste the copied ID into the _fkProjectID field. When You return to the Projects layout, this record should now appear in your portal. You can repeat this process until all survey records appear as they should in the portals of the correct project records. Note that if you enter find mode and perform a find using just = in the _fkProjectID field, you'll find all survey records that are not linked to a project record.

                           

                          If you want, we can take a swing at a list view approach, but there's no point in doing so until your portal is fully working and displays all the records that it should. Any use of the list view approach requires that you have the same records matching on those match field values so if the portal isn't working, trying to set up a list view will also not work.