5 Replies Latest reply on Jun 30, 2011 10:21 AM by philmodjunk

    Help with a report design

    JoshSmith

      Title

      Help with a report design

      Post

      Hi Everyone,

      I'm so new at filemaker it's not even funny yet how much trouble I am having.

      I am working on a database at an optometry office. Here is what I am trying to accomplish.......

      I need to write a report that allows me to see how many times "pocedure code" and "diagnosis" (tied together) have been used over a peroid of time. I understand that I can find that easily with the find option. However, I need to be able to sum all of the charges for a specific procedure code that are associated with each bill. Example, I found 133 records that have "procedure code" 92314" however the charge is different on each bill.

      How would i set that up if I also wanted to see the number of time "procedure code" was used in a given time period and then sum the amount of the "itemized charge plus tax". I have to run this with several different "procedure codes" so would it be possbile to just have them on a "report" layout and run everytime I need it with changing the date?

      I am using filemaker pro 6 connected to filemaker server. Thanks for your help.

      screenshot.JPG

        • 1. Re: Help with a report design
          philmodjunk

          We need to know more than what the layout looks like. I see a section below "Superbill" with a "services & procedures" column. Assuming this is where you record each procedure, is this yellow section a portal or a set of repeating fields? If it's a portal to a related file where each row is a different record, your report will be very easy to set up. If these are repeating fields, this will be very difficult to set up as a given procedure could be listed in any number of different repetitions of the same field.

          • 2. Re: Help with a report design
            JoshSmith

            Thanks for the reply.

            These are  repeating fields. In the yellow section I will be doing the search under CPT Code. If it makes any difference the codes I'm looking for are almost always in the first or second box of the field. The "diagnosis" is in the box above the yellow box that says "ICD-9 Codes". These codes are imported from the "exam" database when it is done they get transfered to this "superbill".

            Thanks again for your help.

            • 3. Re: Help with a report design
              philmodjunk

              Repeating fields make your entire data structure more difficult to work with. If you create a new file where each row in this section is a separate record, you can define a relationship linking your current table to this new table and replace the yellow section with a portal. You can use Import Records to import the data from your repeating fields into this new table using an import option that splits the repetitions into separate records. Then you can develop a summary report in this new file that can group these records by CPT code. Your report can then give you counts and sub totals for each CPT code or you can perform a find to pull up just the records for a specific code to see totals for it.

              "These codes are imported from the "exam" database. When it is doen, they get transferred to this "superbill""

              Perhaps you have this information split up in the exam database already. If so, you can produce your report in the exam database. That's just a guess on my part. This exam database might be a table where you have only one record for each code.

               

              • 4. Re: Help with a report design
                JoshSmith

                Since I am a novice at this, is this how I would do it?

                I would make a new layout in the same database? Then  create a new table and place all of the fields as portals in which tie back to say field "procedure code" and "diagnosis"? Then I would just import those record into the new layout and then I can filter by date and code?

                The information in the exam database is also in a repeating field. I have attached what the exam database looks like. The area circled in red is where it is coming from. I'm sure the picture does nothing, but who knows.

                http://img861.imageshack.us/i/examu.jpg/

                • 5. Re: Help with a report design
                  philmodjunk

                  You are using a very old version of FileMaker that is limited to one table for each file. You'll need to create a new database file and define your table and report layout in there.

                  You'd then use Import records to import the records from your current file into this new file. You'd import only data from the repeating fields and one additional field that you would have set up to link these related records to the correct record in your original file. Usually, this would be a number field that you would use during import to import a serial number field from your original file. If your original file does not have such a field, you'd need to add one and use Replace Field Contents to give current records a serial number.

                  Once that's all done, you can add a portal to your layout and use it in place of your current set of repeating fields. Making a matching change to the structure of your Exams database would be a good idea also. (Seems odd that you would import data like this when you could just set up a relationship and link to the data already present in Exams.)

                  It'd be a good idea to do some research in the FileMaker help system on defining relationships, importing records and using portals before you try to do this.