7 Replies Latest reply on May 27, 2012 2:56 PM by GuyStevens

    relationships

    DONGERSHBERG

      Title

      relationships

      Post

      i have three tables in my database which are clients ; progress notes and billing. there are many progress note records for each client. there is one billing record for every progress note. i have a unique serial number for each client and a unique serial number for each note record.  i still dont understand how to create relationships correctly. as of now i have all three tables linked on the last name field. this is allowing me to display fields from clients on both of the other tables. i would like to also display fields from progress notes on billing but cant seem to do that without destroying the relationship between clients and billing. i would like to be able to enter new records into the billing table from the notes form. when i try to display the note id on the billing table with the current relational strucuture i dont get the unique note id , instead i get the first note id for that client and it repeats for every entry for that client. likewise if i try to display the correspoding billing record for the session note i can only get the first date. not the corresponding record. i am sure i dont have the relational structure correct. when i was using ms access i could get this stuff with this its not so intuitive. 

       

      thanks

        • 1. Re: relationships
          davidanders

          The White Paper for FMP Novices is useful
          http://www.foundationdbs.com/downloads.html

          Lynda dot com has Filemaker Videos (portions are free)
          http://www.lynda.com/FileMaker-training-tutorials/116-0.html

          Free unlocked templates are useful for examining design
          Starting Point -
          http://www.fmstartingpoint.com/
          Donations -
          http://filemakerdonations.com/
          Recruiter -
          http://rccrecruiter.com/alt-index.html/
          Data4Life -
          http://www.data4life.net/

          A free calendar is available to be bolted onto your database
          http://www.seedcode.com/cp-app/prod/calendarfree10/

          You can search specific Filemaker sites on this Custom Google Search
          http://www.google.com/cse/home?cx=001044389222327874554:vi8it1bulm8/

          Looking at FMP business database demos is useful - some are fully useable
          The Excelisys Business Tracker V3.0
          http://www.excelisys.com/exbiztracker3.php/

          Filemaker released a FMP runtime database in 2007 for students called "Campus Productivity Kit" and is available online, notably at CNet
          Google "Campus Productivity Kit"
          WIN http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/
          Mac http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577567.html/

          • 2. Re: relationships
            GuyStevens

            i have a unique serial number for each client and a unique serial number for each note record.  

            Do me a favor and also add a unique serial field for your billing table.

            as of now i have all three tables linked on the last name field.

            No you don't, or at least you shouldnt  :)

            That's a pretty bad idea, what if a last name changes? Or you have two people with the same last name? What if you create a typo? For instance, you create a client called Johnson. And you create a few progress notes and a few bills.

            Then it turns out the name is Johnston. So you change it in the client table. All of a sudden all of his progress notes are gone. They are still there, but they are related to Mr. Johnson. And he no longer exists.

            You see what I mean?

            i would like to also display fields from progress notes on billing

            That's not really a problem, you said that there is one billing record for every Progress Notes record. We just need to get the relationship right.

            i would like to be able to enter new records into the billing table from the notes form.

            No problem there. We'll fix that right away.

            when i try to display the note id on the billing table with the current relational strucuture i dont get the unique note id , instead i get the first note id for that client and it repeats for every entry for that client.

            That's actually correct, because you made a relationship based on the last name, so you should beseing all the records for that client. No that's now what you  need, so we'll change that. But the way you created the relationships this is what you would expect to see.

            when i was using ms access i could get this stuff with this its not so intuitive.

            Don't worry, We'll try and go trough the basics and you'll get there. I have found Filemaker to be far simpler and more intuitive then MsAccess, but it does take a little getting used to.

            Here's what you need:

            Clients
            ID  -  A number field with autoenter serial number.
            FirstName
            LastName
            and other client information
            .....

            ProgressNotes
            ID  -  A number field with autoenter serial number.
            Date  -  A date field  (could be autoentered on creation)
            ClientIdFk  -  A number field  -  later on this field will contain the ID of the Client record it is related to

            Billing
            ID  -  A number field with autoenter serial number.
            ProgressNotesIdFk  -  A number field  -  later on this field will contain the ID of the ProgressNotes record it is related to

             

            This way:
            - A client can have multiple progress notes
            - A billing note can be linked to one progress note.

            If this is not what you want we need to add a join table.

            Then for the relationships you need to create these:

            Clients::ID---------[=]--------ProgressNotes::ClientsIdFk
            ProgressNotes::ID--------[=]-----------Billing::ProgressNotesIdFk

            If you want to be able to create new billing records from the note form you need to double click the [=] sign between ProgressNotes and Billing and select the "allow creation of records via this relationship" on the side of the Billing table.

            You also have to think about the other option "delete record when e record is deleted in the other table."

            For isntance: If you delete a client, I'm assuming you would also want his progress records deleted.

            When you delete a Progress Note you also want the related Billing record deleted.

            Set this up and let me know if it's starting to make sense.

            • 3. Re: relationships
              DONGERSHBERG

              thank YOU DaSaint: so i did that and it is beginning to make sense however. still in a quandry; i have some fields from the client table on the progress note form which i was hoping would automatically show up with data from the clients table ; how do i get that data to show up on the progress notes table i added the fields and specified them as from the clients table. likewise how do i get the matched iD's to show up in the fields for the table links. in my old incorrect system the client data showed up on both the progress notes and billing tables automaically - it was just 'there' now how do i get that to happen now. do i need the portal thing for that now for individual fields. i think i will need a portal to display the billing event for each progress note and to add new records to billing from new progress notes. i really apprciate your help. what is the next stelp.

              • 4. Re: relationships
                GuyStevens

                The next step is to actually relate these records.

                Before they were related using the Last Name. Now we need to relate them using the ID. Records are related when the IDFK field contains the ID of the record it is related to:

                If you have a ProgressNotes record that has an ID of let's say "4" and a ClientIdFk of "1" then this will be linked to the client that has the ID "1"

                But you do need to have a value in this ClientIdFk field, otherwise the record is not related to anything else.

                The question is: How do you determine for wich client a progress note is?

                Are rogress notes created in a portal on the Clients layout?

                Or on a seperate ProgreessNotes layout? And if so, how do you select the client?

                You should probably use a dropdown list that sets the clients ID in the ClientIdFk field.

                 

                To relate the Billing records you might want to enter them in a portal on the progressNotes layout (like you said) , that way the Billing records are automatically related to the ProgressNotes record you are on.

                 

                One crucial thing we might have forgotten, is if you had data in there already, you might have to fill in the IDFK fields that you created.

                This is best done by keeping your old relationship in place and creating a script that loops trough all the records and sets the ID field of the related record from the other table in the correct IDFK field.

                 

                O.k. this is maybe all a bit much, but let me know what you want to start with.

                • 5. Re: relationships
                  DONGERSHBERG

                  got it. i went back and redefined the client id field as the clientidfk and put the id's in the field and then the other data showed up through the linkage. i do have a separate progress note form and in the old system i was putting in the client name from a dopdown list that was linked to the name field in clients so i see need to change that but if im looking at a list of client ids i really dont know what client that is so i guess i can specify the name as a hidden secondary field in the dropdown list? i wil try that. and also attempt to create the portal on the progress notes form. ill keep you posted if that's ok. really appreciate the help.

                  thanks!

                  • 6. Re: relationships
                    GuyStevens

                    This example show you two ways to show names in a dropdown.

                    It's pretty basic:

                    http://dl.dropbox.com/u/18099008/Demo_Files/TwoTablesLinked.fp7

                    • 7. Re: relationships
                      GuyStevens

                      For the names: check out the c_Full name field in my example.

                      Even if you have a seperate FirstName and LastName field you can just combine them together in a calculation, and then use that full name field as your second field in your dropdown.