2 Replies Latest reply on Dec 12, 2014 9:58 AM by philmodjunk

    How to deal with Multiple Similar fields



      How to deal with Multiple Similar fields


      I am, admittedly, pretty new to FileMaker (I have created a few simple Databases, scripts, etc.) but I am now creating something a bit more complex.  I have a need to track the information below for each individual visit to a Clinic (up to 15 visits) for each person in the Database.  Each visit could contain up to 6 treatments and each treatment will require all the information (listed below) to be tracked for that particular visit date.  The 5 fields below need to maintain a relationship with each other so if a report is run against them the correct information is gathered (Give me the amount billed and paid for CPT code "5" for all clients on their first visit, or give me the amount billed and paid for each CPT code "7" for Client "John Smith" for all 12 of his visits).  

      1. Treatment Date
      3. Treatment CPT Code (this is populated from a Value List)
      5. Treatment Description
      7. Amount Billed
      9. Amount Paid

      I could create First Treatment CPT Code 1-6, First Treatment Description 1-6, then move to Second Treatment Code 1-6, Second Treatment Description 1-6 for all 5 fields listed above for all 15 potential visits (Creating a total of 450 fields), but this just doesn't feel like the right way to do it not to mention it will make generating scripts tedious and cumbersome.  Is there a more efficient way to get this information in FileMaker that will still allow me to gather the information I need?


      As a bonus question - is there a way to prompt for additional visit information (and the corresponding potential 6 treatments) rather than display all 15 potential visits in a Layout.  In other words, when someone begins entering information for the first treatment date they don't see blank fields below it for visits 2-15, but are somehow prompted after all fields in Visit 1 are tabbed through (with info or left blank) if there is a second visit, etc.?  


      I hope my questions and requirements above are clear, but I would be happy to provide further clarification if needed and I'm sorry if I asked too much in a single post.  I am trying (and will continue) to search through FileMaker's Help, but I am struggling to properly define what I need so I get pertinent results.  

      I have attached a picture of what I created for the first visit so you can visualize what I would like.  

      Thank you in advance!



        • 1. Re: How to deal with Multiple Similar fields

          I would strongly recommend creating a new table for treatments. Not only will it reduce the file size of your database it will also give you more flexibility such as removing the upper limit of 6 treatments. You can then use a portal to easily manage the related records.

          This is what is called "database normalization". The basic concept of normalization is to reduce the amount of wasted space in a database by using the relational structure of databases. I would recommend reading a little bit about it because it will help you design your database structure.

          • 2. Re: How to deal with Multiple Similar fields

            You need to think in terms of multiple related records instead of multiple fields. This makes it much easier to flexibly link visit data to treatment data and there won't be any built in limits on how many visits nor how many treatments.


            Patients::__pkPatientID = Visits::_fkPatientID
            Visits::__pkVisitID = Treatments::_fkVisitID

            with the right design and scripting, portals to both Visits and Treatments can be managed from a layout based on Patients.