3 Replies Latest reply on Aug 5, 2013 7:20 AM by philmodjunk

    Linking SOME field spaces to a database, but not ALL



      Linking SOME field spaces to a database, but not ALL


           Hi, I am new to FileMaker, teaching it to myself the last few days after our company wanted to start using it to track our residents and their case notes, etc. The databases, layouts, etc. were being used at a sister site, and so some of the already entered databases and layouts are useful, but some of it is not. I've taught myself a lot just playing around here the last couple of days, trying to edit it all to our specific needs...however, I am completely stuck on something...and I hope this makes sense to everyone...

           Each resident has their own social worker, probation officer, guardian ad litem, and/or mental health worker. Some have all 4 of the above, some have 2 or 3, and some don't have any assigned to them yet. I need to show on each resident's individual main layout page their appropriate corresponding worker's and their info.

           I have all the residents in a database named Clients, and all the workers entered in a database named Agents. For each agent record, I have their name, email, phone, address, etc. entered for them. On the main layout of the resident page, I have 4 groups of dropdown menu field spaces (one bunch for the PO, one bunch for the SW, and so on) to match the data I entered for each worker (so the Address field is setup to display data from Agents Address, etc).

           What I'm trying to accomplish is that if I choose John Doe from the dropdown list as Resident 1's PO in the first name field, that his address, phone, etc. will automatically fill in for the PO info boxes, and then if I choose Jill Smith as his SW in the next group of field spaces, her info automatically enters in the SW group of field boxes, leaving the PO/lawyer/etc's boxes/info alone. I have accomplished the part where John Doe's info falls into place when I choose him, as it does for all the other workers..my trouble lies in that he and his info show up in ALL 4 of the groups, showing that he is that resident's PO, SW, lawyer, GAL, AND MH worker. I can't get him to JUST be a PO in one group, and to have the other groups separate to be able to show their respective info.

           What am I not linking correctly? Help, this is driving me nuts with frustration!


        • 1. Re: Linking SOME field spaces to a database, but not ALL

               How are these tables related?  What does your relationship graph look like? 

          • 2. Re: Linking SOME field spaces to a database, but not ALL

                 Um...those are good questions...I guess I don't know much about the relationships part...this is what my agent database looks like with the relationship tab open...

            • 3. Re: Linking SOME field spaces to a database, but not ALL

                   Yep, no offense meant, but you don't know much about relationships and understanding how to set up the correct relationships is key to successful database design.

                   You have a single relationship between the two tables that does not distinguish between a lawyer or a probation officer or other type of agent. And the relationship that you have in place matches records by name--which is also not a good idea as people's names are not unique and people change their names.

                   It is also very odd that you have two tables named agent in two different files. I can tell that the agents table is from another file by the fact that the table occurrence name is in italics.

                   What you need are relationships like this:


                   POs::__pkAgentID = Residents::_fkPOID
                   Lawyers::__pkAgentID = Residents::_fkLawyerID

                   You'd then do the same for the other two types of agents. In my example, POs and Lawyers are two Tutorial: What are Table Occurrences? of the Agents table.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   The key difference here is that you set up in the table of Residents, one match field for each type of agent. you can refer to fields from POs to get contact info for the resident's probation officer and can refer to Lawyers to get info on the resident's lawyer. You'd add two more occurrences and two more match fields to link to the other two types of agents.

                   There is also another method possible that uses a join table for this, but separate key fields appears to me to be an easier way for a "newbie" to set this up and get it working. However, a join table offers the chance for a bit more flexibility as it will support changes in your system that might require more than 4 agents for a given resident without needing to add more match fields and relationships.