3 Replies Latest reply on Apr 25, 2012 4:57 AM by Mike_Mitchell

    How to link two tables to one portal?

    annettermt

      I am slowly learning how to work this program.

      My question is:

      I have a CLIENT LIST which is related to my CLIENT FILE layout with a portal in the CLIENT LIST lay out. I now want to add a RECEIPTS table which will pull the information from the PORTAL in the CLIENT LIST layout to the RECEIPTS layout. Is that possible?

       

      THIS IS WHAT I WANT TO BE ABLE TO DO:

       

      I see a MASSAGE CLIENT, input their data into the CLIENT LIST and the PORTAL (on the client list layout), which then transfers the info via the PORTAL to the CLIENT FILE layout (where I can add the rest of the visit's notes). I then want the info that i have put into the PORTAL to also be inserted into an new layout called RECEIPTS. (so first name; last name; time of appointment; date of appointment; length of appointment........this is what is in my PORTAL that I want to be transferred to the RECEIPTS layout for eventually prining a receipt)

       

      Any help would be greatly appreciated.

      Thanks!!

       

      I've attached a copy of what I have done so far so you can see what I mean. I have to clean up the layout for receipts still.

        • 1. Re: How to link two tables to one portal?
          Mike_Mitchell

          Hello, annettermt (Annette?). Welcome to FileMaker.

           

          I think the best way to clean up your database is to clean up your data model first. What do I mean by that?

           

          Your confusion is coming from, I believe, a jumbling of your data entities. (Data what?) Data entities are, put simply, the things about which we want to track data. You've actually done a good job in the initial phases of your design; we just need to clean up your terminology a bit.

           

          When you name your tables in FileMaker, it's usually best to name them according to the "thing" (entity) they're tracking. So, for example, you have a layout (and table) called "Client List". Well, you're not tracking lists of clients in this table; you're tracking the clients themselves. So it would be clearer to call that table "Client" or "Clients" (some prefer singular, others plural).

           

          Another, better example, is the table you've named "Client File practice". Obviously, this is the default name given by FileMaker; you called the file that, so FileMaker simply named the first table the same thing. But it's really tracking client appointments, so renaming the table "Appointments" would make it clearer what you're dealing with.

           

          Then you have Receipts, which is great, provided you are leaving room for more than one receipt per appointment. (Which is probably a good idea.)

           

          Now, you can name your layouts anything you like. For example, if you have a layout that's a list of Client records, by all means, call it Client List. It's just that each record represents a client, not a list.

           

          Make sense?

           

          Now, let's take a look at how we move data around - or not - in your relationships. Here's your current relationships graph:

           

          graph.png

           

          Not bad at all! You've correctly used an ID for the client (instead of something mutable, like Name - yikes!). You can connect a client to his receipts (which is good), and you can connect a client to his (now renamed) appointments. All very, very good. But we're missing a couple of things.

           

          1) Why do we have the clients' names in both Client and Appointment? One of the basic rules of databases is: DRY. Or, Don't Repeat Yourself. There's no reason to put the client's name data on the appointment, because you can see it on the related Client record through the relationship. So only have one place to put any given piece of data. Otherwise, you risk data inaccuracies because someone changed the data one place, but didn't change it someplace else.

           

          2) There's no good place to attach a receipt back to the appointment that generated the service. So, you have the appointment date duplicated. Again, we have a problem.

           

          So let's see if we can't make this a little easier. Take a look at a quick Rev. 2:

           

          graph.png

           

          Now we're in a position to create new client records directly from an appointment (as you wanted), but we can also create receipts from there. Pretty cool. And we don't have to worry about redundant data entry.

           

          This could be further modified, depending on your needs.For example, if you can always guarantee that every receipt will have at least one appointment attached, you can do away with the Receipt_Appt table occurrence and just put Receipt where Receipt_Appt is. Reason is, you can still get at the Client data if there's an Appointment record in between. Cool, huh?    

           

          Hope that gets you started. I've attached the modified database for your review.

           

          Mike

          • 2. Re: How to link two tables to one portal?
            annettermt

            Oh My Goodness Mike,

            THANK YOU for all this information!  You have cleared up a lot of other questions I had.  I really appreciate your help!  Excited to get this up and running.

            Thank you so much for your time.

            Annette

            • 3. Re: How to link two tables to one portal?
              Mike_Mitchell

              You're welcome. Glad it helped. Exciting, isn't it?   

               

              Mike