1 2 Previous Next 29 Replies Latest reply on May 28, 2014 5:22 PM by Mike_Mitchell

    Relationship / Query help

    chronister

      Hello all,

       

      I hope everyone enjoyed their weekend.

       

      Can anyone point me to a good, clear tutorial / learning resource that can help me understand how to query the database to get only specific records? I understand this is done with the relationship window and table occurences.

       

      I have a solution I am creating for my local animal shelter and I have a listing of rescue organizations that we work with. In a list of dozens, we regularly use specific ones, so I have a field to mark a rescue active / in-active.

       

      I am trying to create a relationship / query that shows only the active rescues. I am going to need this same type of thing for another part of the solution too so I am needing to find a good resource to help me understand better how to accomplish "queries" with relationship. I have gone through several tutorials but I don't seem to be able to execute it properly.

       

      In this specific example, I created a second instance of my RescueOrganizations table and in the table I had a field called StatusMatch as a calculation set to "Active". I then joined the tables on the PK and the match field, but all the resue organizaions came up instead of just the active ones.

       

      If you would like to see the actual solution I can post it here.

       

      Any advice / direction on this would be appreciated.

       

      Thanks,

       

      Nate

        • 1. Re: Relationship / Query help
          Malcolm

          Hi Nate,

           

          The basics of find/search are covered in the documentation. Under the Help menu, look down for “Product Documentation”. There are several documents and they’ll get you started.

           

          Malcolm

          • 2. Re: Relationship / Query help
            Mike_Mitchell

            Nate -

             

            What you're describing is fairly common, and should work. It sounds like you don't have your relationship structured correctly. What you should have is this:

             

            1) Your constant calculation field (e.g., "Active") should be on the parent side of the relationship (the side from which you want to view the related records).

             

            2) That field should join to the status field (not the constant field) on the child side of the relationship (the side with the related records you want to view).

             

            3) And, of course, any other match fields that define the further filtering criteria you want to see.

             

            Is that what you have?

             

            Mike

            • 3. Re: Relationship / Query help
              BruceRobertson

              Attached is an example file. It requires FileMaker 13.

               

              (Though you didn't mention what version you are using)

              • 4. Re: Relationship / Query help
                chronister

                Hello folks,

                 

                Thank you for your help. I have followed the suggestions and I am still coming up empty in getting my relationships to work.

                 

                I have posted my solution so you can see what I have . I will tell ya what I am trying to do, and hopefully you folks can tell me what I am doing wrong    I am donating my time to this too, so I appreciate you donating your time to answer my questions.

                 

                This is designed to track animals from entry into the shelter all the way through it's out take (?) as well. I have status and location to determine if the animal is available and where it is. I want to track all animals, people (both volunteers and applicants), rescues, dues, etc. etc anything else that comes up .... lol

                 

                I am doing fairly well on it and I would love your feedback on the overall design / UI / intuitive usablity etc. Feel free to rip me apart on my DB layout if it is bad... lol ..

                 

                My struggles here thus far is getting the Fosters relationship (Volunteers -> Fosters button) to show correctly. In the Medical section, I would rather have that as a list (like the fosters table) of upcoming med treatments, I am going to have other relationships like availableAnimals, AnimalsinFoster etc, so I really want to understand what I am doing wrong here on these because if I can't query this thing properly then I am trying to run a marathon with a broken leg.... lol

                 

                Thanks folks,

                 

                Nate

                • 5. Re: Relationship / Query help
                  chronister

                  It likely don't need to be said... but the solution is waaay in dev phase so there are may inconsistencies and overall things that are not fine tuned in the least. hehehe.

                   

                  This is my 2nd attempt (the 2 attempts are overlapping), the first had to be build out of necessity and begun using as soon as we can, but this one I am taking my time and really trying to learn the details while trying to fumble through the other....

                   

                  --edit--

                   

                  I guess I found something that may not be obvious in it, on the animals tab, the image is a button that takes you to the animals detail page.

                  • 6. Re: Relationship / Query help
                    BruceRobertson

                    "I have followed the suggestions and I am still coming up empty in getting my relationships to work."

                     

                    How?

                     

                    Where should we look?

                     

                    What exactly did you do that you that you think is an application of the suggestions?

                    • 7. Re: Relationship / Query help
                      BruceRobertson

                      Don't use script $variables in field calcs:

                       

                       

                       

                      Let ( [

                      $d1 = "1/1/"& Year ( Get

                      (CurrentDate) )-1 ;

                      $d2 = Get(CurrentDate)

                      -365

                      ]

                       

                      etc.

                      Should be:

                       

                      Let ( [

                      d1 = "1/1/"& Year ( Get

                      (CurrentDate) )-1 ;

                      d2 = Get(CurrentDate)

                      -365

                      ]

                       

                      • 8. Re: Relationship / Query help
                        chronister

                        I looked over Mike's items and believe I have the correct type of parent child relationship / calculation between fosters and Volunteers, but fosters is not showing me the subset of volunteers I need. As you can see, fosters and volunteers are 2 occurances of the same table.

                         

                        If I can see where that relationship is wrong then I can hopefully apply that to get my other relationships fixed. 

                         

                        @Bruce, thanks for that... thougth those were vars... thanks.

                         

                        Thanks you folks.

                         

                        Nate     

                        • 9. Re: Relationship / Query help
                          chronister

                          I apologize bruce, I had tried to apply what you sent me and could not quite make it work, so I moved to another piece of the system and ran smack into the same type of issue. So once I can figure out what I am doing wrong in my relationships here with the fosters / volunteers, then I can apply the same thing to the rescues / active or inactive that you had suggested on previously. I deleted the activeRescues table I had as I was going to come back to it in a day or so.

                           

                          In my current solution I am simply struggling to apply the proper relationship links and calculations to get my subset of data for several pieces of this puzzle.

                           

                          Thank you for your help and I will look into your solution more in depth to see if I can apply what you showed me there, to what I have here.

                           

                           

                           

                          Nate     

                          • 10. Re: Relationship / Query help
                            BruceRobertson

                            Still no answer though.

                             

                            Where EXACTLY should we look to see this?

                            • 11. Re: Relationship / Query help
                              erolst

                              BruceRobertson wrote:

                              Don't use script $variables in field calcs:

                              Unless you're up to something mightily clever …

                              • 12. Re: Relationship / Query help
                                chronister

                                Click on Volunteers and you will see 5 records.... then hit the Fosters button and you will see a list of 5 volunteers. The 2 fields on the right are the canFoster calc field, and the match field. As you will see, all 5 volunteers are showing up even though 2 of them have 0 set for the calc. Both of these layouts are based on the Volunteers table with fosters and volunteers occurances in the table graph.

                                 

                                Basically trying to "SELECT * FROM Volunteers WHERE canFoster = 1"....  the permissions are setup in a master list, and the id's of those permissions are stored in a list in the volunteers table. canFoster looks for permission ID 9 which is Fostering and returns 1 or 0.

                                 

                                I hope this is enough information this time    sorry about the lack of prior.

                                 

                                Thanks for the help     

                                • 13. Re: Relationship / Query help
                                  chronister

                                  I got no clever at this point.... just folly... lol

                                  • 14. Re: Relationship / Query help
                                    BruceRobertson

                                    It still isn't at all clear what you want to do.

                                     

                                    The current result is correct. It shows all the volunteers; and it shows a 1 in canFoster for those who can foster.

                                     

                                    If you want to constrain the list to those who can foster, then do a find on canFoster = 1.

                                    1 2 Previous Next