1 2 Previous Next 24 Replies Latest reply on Apr 5, 2012 10:58 AM by aewerdt

    New to filemaker having some difficulty with relationships



      As i said i am new to filemaker....I am working with the tutorials and I have created 4 tables which i have linked with primary and foreign keys.

      We have a business that works with companies that manage multiple buildings. so each one client will have 100's of buildings I need to link.

      I have done this with a one-many relationship using a primary key for the company table and a foreign key in the building site table.

      I am creating a layout that displays both tables which i can get working to a certain degree...the main problem i have is I can only retrieve one record...i have gone into find mode typed in some details of other records i have entered but it still only retrieve the same record over and over again....

      is there anyone that can tell me what i have done wrong????

      i am getting very frustrated i know it can be done as i used to work with a file maker database before and it could do it....i just dont know how to make it do it..

      any help, advise would be appreciated.


        • 1. Re: New to filemaker having some difficulty with relationships

          Are you using portals to display the related records? You should....

          • 2. Re: New to filemaker having some difficulty with relationships

            Thank you Vaughan for your prompt reply....... ok well i did start to use the portals however what I need to to display one record at a time. For instance as the layout stands at the moment on the left of the layout displays the company details what we want is to be able to on the right pull up an individual building (managed by the company). When i used the portal all the buildings linked to the company appeared in a list. But this not what my hubby wants we wants to have the company and 1 building but i can not recall just one..well i can but it wont recall any other buiding just the same one...


            I have another issue now..I must have changed a setting somewhere...but i have gone thru it and i cant find what i have done wrong...I am following the tutorials and adding a portal...I have followed the instructions to a Tee...and it worked for my once...ok so what i have done is created the portal pulled thru the records from another table all good...however now for some reason it wont let me change the records in the portal...I have ticked the box that says allow creation of records in this table via this relation..but still it wont let me...Like i said it did the first time but then i realised i had left off a field so i started from scratch ...but now nothing...I know it must be something simple..but i cant see it..



            • 3. Re: New to filemaker having some difficulty with relationships



              Portals are used to display related records: for instance, on the Companies record you could have a portal that displays just the Buildings related to that company. A button on the portal row would then display the full record detail for that Building.


              I suggest you take a look at one of the sample templates that come with FMP to get a feelong forhow the portals work. They are very powerful and essential for creating a useful interface.

              • 4. Re: New to filemaker having some difficulty with relationships

                Vaughan Thanks again for your awesome response...ok so what your saying is to have the layout with the portal that displays all the buildings that a particular company manages...which is how i had it to start with then create a button inside the portal that take it to the actual record??which can i be honest is how i had it in the beginning..but my hubby (who i am building it for) requested that he only see one building at a time so i had to delete what I had designed and start again..but i  have been pulling my hair out to try and organise it the way he wants it..I just dont understand why I can not retrieve my records????

                Although i love the use of the portals I am a bit annoyed that you can not retrieve just 1 record at a time nor does it bring the record you ask for to the top of the portal when found....oh well...i love filemaker it is an awesome database and I know I will get my head around it in time.

                do you happen to know why I can not find my records?????? have i missed a step or done something wrong???

                is it possible to retrieve alternate data from a different layout...i am sure it is but its just not working...

                thanks again


                I really appreciate your help I have no one else to talk to about this no one in my family know what I am talking about nice to chat to someone who can help.


                • 5. Re: New to filemaker having some difficulty with relationships

                  Create a layout for companies with a portal for buildings on it.


                  Create a layout for buildings.


                  Switch between the two layouts, depending on what you want to see.

                  • 6. Re: New to filemaker having some difficulty with relationships
                    Mike Duncan

                    It might be helpful to think of what you are displaying in terms of the related data matching up. It sounds like you want to be able to pick one of the related (child) records from a list and then display more of that selected record, if I intuit your request correctly.


                    So one relationship would show a portal (list) of related records like parent_primary_key to child_foriegn_key... which shows all related records, then you click on one to select it... which could either jump to a different layout based on that table occurence or set the key field for a second relationship to display the data on the original layout. You could have a global that then becomes the left side for a second relationship parent__foriegn_key to child_primary_key to display just the one to one record.


                    Hopefully this isn't too confusing, it sounds a lot more complicated than it actually is once you get the concept.


                    Hope this helps

                    • 7. Re: New to filemaker having some difficulty with relationships

                      Hi Tina,


                      Follow the method provided by Vaughan and you will learn the mechanics of the display. Once you understand how FileMaker pulls the tables together and accesses the data, then the interface part can be achieved many different ways. Mechanics and graphic (look-n-feel) are two different things. Concentrate on getting the basics down before you try to beat the display into submission.




                      My 2cents,



                      • 8. Re: New to filemaker having some difficulty with relationships

                        looks like you understand what I am trying to create however it all sounds very confusing...I have done as Vaughan suggested and created a portal and its working just not as I hoped....hopefully with the help here and tutorials i should get this down ...

                        thanks for your help


                        • 9. Re: New to filemaker having some difficulty with relationships

                          Hi Tim thanks for your 2 cents....I have done what Vaughan has suggested however it is not what I was trying to achieve but it will do for now.... I am getting a bit confused but hopefully it will all come clear to me soon..



                          • 10. Re: New to filemaker having some difficulty with relationships

                            Tina -


                            In addition to the excellent suggestions you've already received, I've attached a (VERY) simple demonstration of something called a "list-detail" view. This is a technique for displaying both a portal and details about each related record on the same layout. Can be useful in situations where you want to see minimal detail about all the records, plus all the detail about one record, at the same time.





                            • 11. Re: New to filemaker having some difficulty with relationships

                              thanks guys...I am looking into both files sent ...wow you guys are amazing..I think i need to continue the tutorials for a bit also...both your points are fantastic I see how they are done....all i need to do is work out how to implement them into my database...I have attached 2 files these are the databases i have created...there are 2 because i got so far thru it and my husband decided he wanted to have the indidual file appear on the right of the layout so i started again from scratch.

                              The second is the new one i am creating whist using the tutorials if any one has the chance to look at the files and tell me where i am going wrong i would appreciate it....

                              so bearing in mind what my husband wants is for the left side of the layout to hold and add all new companies clients....these companies manage many buildings he wants only one record to appear here showing the address details of the building...now also it has to work in find mode so for example if i am looking up company ABC which manages building 1, 2, 3 ,4, 5 and 6 he wants the left side to have all data for company ABC and the right side to be able to search for all related buildings so do a search for building 6 and all the details for 6 come up on right.

                              I have a portal here that does not allow this type of find....and i am not sure how to do it..... I do not know if this is covered in the tutorials....if it is I would be happy to be directed to the right one...

                              Anyway I dont want to be a pain i just thought it would be easier if you could see what I have done so far and advise from there...

                              I hope this is ok

                              Thanks you so much for all your help so far...




                              • 12. Re: New to filemaker having some difficulty with relationships

                                Tina -


                                I'm not entirely sure what it is you're after. When you say "it has to work in Find mode", what is the goal? Do you have more than one company managing the same building - is that the problem? He wants to be able to locate all the companies that manage bulding 6?


                                I think maybe we need to cover the topic of "context". In FileMaker, each layout is based on a Table Occurrence. This determines what records are viewed and the path through the relationships graph that the layout will "see".


                                Why is this relevant? Well, because if I do a Find in a portal, I don't find related records that match the Find request. Instead I find all the parent records that have related records that match the Find request.




                                So, for example, if I do a Find in a portal on my Company layout, I'll find, not buildings, but companies. If I search for building 6 in my portal, I'll find all the companies that are parent records to building 6.


                                Hence, if you only have 1 company managing each building, Find mode becomes almost irrelevant (except to locate that one company). A list-detail view will do almost the same thing, as I posted. You can spice it up a bit, if you like, by building an additional script to automatically execute the detail view when someone does a Find for a building record and attaching that script to the Find command using Custom Menus.


                                Hope that all makes sense.



                                • 13. Re: New to filemaker having some difficulty with relationships

                                  I am sorry Mike I am not very good at explaining myself...

                                  ok i will put it as simply as i can to ensure i am explaining it correctly..

                                  we have clients who are strata companies

                                  each strata company can manage up to 800 buildings which they identify using Strata Plan Numbers

                                  so company=1

                                  strata plan=many


                                  what my husband wants is a layout that will have on the left the managing company on the right he wants the details of the strata plans

                                  what he does not want is all 800 strata plans to show up and he have to negotiate through 800 records to find the strata plan he needs.


                                  when using a portal it displays all 800 records...there has to be a way where he can just retrieve the strata plan he wants to see and all its details (keeping in mind that the managing company will be on the left also) but i cant work it out...i think i am too new at this to understand it all.....you have been very helpful and i hope this has clarified what I am trying to explain...i did also include me file in my last post for you to look at to see if there is anything there that helps you to understand what I am trying to explain...i have never been very good at explaining things....


                                  thanks for all your help so far..


                                  • 14. Re: New to filemaker having some difficulty with relationships

                                    Okay, there are a couple of ways to work this. (I agree; a portal with 800 rows is cumbersome if you just have to scroll through the thing.)  


                                    Probably the easiest way around this is to add an additional filtering criterion to your portal. You can do this using filtered portals. Here's what you do:


                                    1) Create a new global field in the Company table, based on whatever filtering criteria your hubby wants to use. Let's say, just for argument, he wants to isolate the building number, so you call it "gBuildingNo".

                                    2) Double-click the portal in Layout mode.

                                    3) Click the "Filter portal records" checkbox. The Calculation dialog appears.

                                    4) Set your calculation equal to "Case ( not IsEmpty ( gBuildingNo ) ; gBuildingNo = Buildings::BuildingNo ; 1 )" or whatever happens to be appropriate. (The calculation has to be one that evaluates to True or False - a Boolean.)


                                    This will tell FileMaker to show the portal records where the global field matches the field in the targeted records if it's not empty. If it is, it'll show all the records. (You might want to create a button to clear it out, or maybe a pull-down list of all the available numbers, just to make it easier.)


                                    Combined with the list-detail view, this should get you what you need.


                                    Let me know if that doesn't make sense.



                                    1 2 Previous Next