9 Replies Latest reply on Sep 27, 2012 11:06 AM by philmodjunk

    Setup of Client management and treatment history



      Setup of Client management and treatment history


      Hi guys, I hope you are all well. I'm fairly new to the forum and this is my 1st post.

      I am using FM12 to set up a Customer Management system. I have recently gone back to being a self employed massage therapist having been unemployed for a time and going bankrupt due to the property/financial "problems" we currently have.

      I've used FM before (FM10 I think) so this basic set up is really frustrating me, I've got info overload and worst still cant find the copy Filemaker Bible I brought last time i created a database the way i wanted it.

      I'll try and give as much info as I can (sorry if its to much) so that any other issues you guys can see may be flagged up now.

      So...... I want a system where when I see a client for the first time I can record all there data (Contact/emergency contact/medical history) from the consultation form........ so that will be one layout

      I also need to record details of each visit/appointment and treatment given (date, type (value list) and a notes field (it will also have a timestamp field)

      Then i need to record sales/billing, now most of the time these will be related to a treatment/appointment ^^ (so would be a 1-2-1? to treatments but a many-2-1 for clients?) but sometimes people may also pay in advance for a block of treatment or purchase gift vouchers.

      I'm thinking I'd like 2 main layouts.... One for the Initial consultation and then an "Appointments" screen (layout) which would be the main used screen where when i select a client (via value list?) it displays just the main data: Name, age, post code.
      Then a portal? with scrollable previous treatment notes plus any relevant medical history.
      --- This way I can have a quick look before i see them any info I need to know plus helps with customer service ---

      I need to then add details from the current treatment once complete that would enter all the data into the "Treatment" table.
      Lastly i would like to be able to input the sales/payment details from that screen or if its easier just create a button that takes me to a/the "payments/sales" screen but with the details pre populated

      Ultimately I love to be able to set up a interactive calendar and schedule (was looking at SeeCo's free solution) but thats just too much for now without getting somewhere with the basic setup

      At the moment I can't get FM to pull up a clients details into the "Appointment Screen" when I select them from the value list.
      and am lost with the same for the sales
      Hope that all makes sense.

      I've attached some screen shots to what I have that will hopefully help you visualise what I'm thinking and where Im going wrong.

      I know there are plenty of similar questions out there but if you would be good enough to reply to my questions I may then be able to connect the dots from the other threads, like I said I'm having knowledge overload and brain block, I'm unable to relate examples to my setup.

      Many, many thanks in advance!


        • 1. Re: Setup of Client management and treatment history

               Sorry, pics didnt post, maybe this will work better?


               Thanks again.



          • 2. Re: Setup of Client management and treatment history

                 I would suggest browsing these links

                 Especially, the first four database design links and the free open database from yzysoft

            Home > Designing and creating databases > Creating a database > About planning a database
                      A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.           
                           Follow these general steps to plan a database:

            Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

            Relational Database Design 101 (part 2 of 3)

            Relational Database Design 101 (part 3 of 3)


            The White Paper for FMP Novices is useful  - 
                                                    Filemaker Free - Listing of free resources  - 

            Free unlocked templates
                                                    By Richard Carlton Consulting
                                                    Starting Point -
                                                    Donations -
                                                    Recruiter -
                                                    Data4Life -

            RCC Blog  - 
                                                    By yzysoft.com  Contacts | Products | Invoices | Letters
                                                    Sample Database -

                                                    A free calendar is available and can be integrated into your database
                                                    Seedcode Calendar Free -
                                                    Filemaker's  Online Help Pages
                                                    FMP 12  http://www.filemaker.com/12help/index.html
                                                    FMP 11  http://www.filemaker.com/11help/index.html
                                                    FMP 9    http://www.filemaker.com/9help/index.html

            FMP business database demos - some are fully useable
                                                    The Excelisys Business Tracker V3.0  - 
                                                    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"    https://www.google.com/search?q="Campus+Productivity+Kit"

            WIN -  http://download.cnet.com/FileMaker-Campus-Productivity-Kit/3000-2124_4-10577579.html/

                                                    You can rename the database from a .usr file to a .fp7 file and open in FMP 7 thru 11

            Lynda dot com has Filemaker Videos (portions are free)
                                                    YouTube - Filemaker Videos

            FMTutorials Channel
                                                    UK FileMaker Channel
                                                    FileMaker, Inc. Channel
                                                    Lynda.com Channel - Filemaker videos
                                                    Dwayn Wright's Channel

                                                    Soliant Consulting Channel

            Filemaker Academy Channel
                                                    Skeleton Key Channel

                                                    Accelerate Computer Training Channel
                                                    Filemaker News

            Scoop.it is an information curation site - free option available.
                                                    Filemaker Info  -  http://scoop.it/t/filemaker-info
                                                    Filemaker News  - http://www.scoop.it/t/fm-news

            Filemaker Wikipedia  - 
                                                    Filemaker - Version By Version  - 

            Search specific Filemaker sites with this Custom Google Search


            • 3. Re: Setup of Client management and treatment history

                   Thats for info, i have read most if all of that previous. Like i said I cant seem to connect the dots from the examples given to my solution.

                   Just dealing with the Clients and treatments.. My "appointment" screen (layout) is based on the Contacts table, I have the treatment fields kind of working except they only save in the Treatmants table they disapear from the appointments layout plus I still cant quite figue out how to use a clints name to "pull up" and populate the fields.

                   Please believe me I have done my research, im not just asking and being lazy. i know its simple but I'm getting really frustrated at not making it click in my brain, once it does I know I'll be fine and then more forward to the slightly more complex tasks and the usabilty.


                   Thanks again for any help you can give

              • 4. Re: Setup of Client management and treatment history

                     Here is the appointment screen in layout view if that helps?


                     Am i missing a field(s) or have I got my relationship wrong.





                • 5. Re: Setup of Client management and treatment history

                       Also, I have drawn up a blueprint, this is me working it out practically (not the finished article) so I can make more sense of my blueprint and get it get it right for the larger vision to come.

                  • 6. Re: Setup of Client management and treatment history

                         Why does your relationship match a field named ClientID to a field named "TreatmentNote". That suggests that your relationship is matching to the wrong field. Clients::ClientID should be an auto-entered serial number field. It should match to a number field in Treatments and normally is also named ClientID.

                         Your layout only links to the "first" related treatment record. If you plan to create a new record in Treatment for each new appointment, you'll need either a portal or a more sophisticated relationship to allow you to show the client's most recent treatment record on your appointments layout.

                         And one suggestion for getting more help from the forum: Try to ask specific questions. Your first post was very general and I read it much like DavidAnders probably did: "Here's someone who needs a lot of help in many many areas from the basic design all the way through to the nuts and bolts of how to make the design work." That will make many people relucant to reply as they don't want to get sucked into a discussion of many 100's of comments back and forth.

                    • 7. Re: Setup of Client management and treatment history

                           Thanks Phil. Yes I understand re the long post, I just thought if I gave the overall plan the basic questions would make more sense.

                           Yea I want to have some of the clients details from the Cients table... to populate the "appiontment" screen/layout
                           With a portal that shows previous treatmeant notes. 
                           May I ask what you mean by a "more sophisticated relationship"?

                           The thing is, I have created a similar FM DB before and I think thats why is perplexing me so much, to be stupidly stuck on such a basic point!

                      • 8. Re: Setup of Client management and treatment history

                             The screen shot of the notes field is from the FM starting point template but I can't decypher the layout to use it.

                        • 9. Re: Setup of Client management and treatment history

                               I don't think that's a very good example file for making and managing appointments. The fact that in inserts a date and time into the top of the text field is a method I particularly do not recommend you use. You want dates  and times in separate fields with the appropriate type.

                               I was refering to the right hand section of your layout titled "This Treatment". Putting fields from a related table instead of the layout's table on your layout automatically matches to the first related record. Unless you specify some specific settings in the relationship such as a sort order, the first and oldest treatment record will display data in those fields.

                               There are a number of options for handling this if you don't use a sorted relationship. one is to base the layout on treatments instead of clients. Then you can find all records for a given client, sort them to make the first record the most recent and go to the first record. Then your "This Treatment" section of your layout will work, but your portal to past treatments will need a different relationship if you chose to go that route.