3 Replies Latest reply on Oct 3, 2014 6:21 AM by philmodjunk

    Filemaker Pro 11 - Month Report Help



      Filemaker Pro 11 - Month Report Help



      I am currently working on a database which holds information regarding inpatients.

      Some of the fields included are "admission" (a date) and "discharge" (also a date).

      A calculation field calculates the amount of days each patient is admitted ("Length of Stay")

      Further fields calculate the correct cost of each patient depending on the type of patient as shown below:

      Admission Rate = Case ( Account Class = "Serving Member";Case (Length of Stay<15;681;390); Account Class = "Veteran";0;740)

      Admission Revenue = Case ( Discharge = "";0;(Case ( Account Class = "Serving Member";Case (Length of Stay < 15;Length of Stay * Admission Rate;(14 * 681) + (390 * (Length of Stay - 14)));Account Class = "Veteran";0;Length of Stay * Admission Rate)))

      These calculations work perfectly and produce the total expense of each inpatient over their stay.

      The problem I'm having is that I'm not sure how to produce a month report (for any given month) which separates the cost into the correct month. At the moment, I use this calculation field to sort my records into a report

      MonthYear = MonthName ( Admission )  & ", "  & Year (Admission)

      which produces something like "July, 2014", etc. for each record. This is based off the admission field however patients can span across multiple months, e.g. July to August

      The cost for each day in the first month needs to be included in the first month (e.g. July) and the rest of the cost put into the summary of August. I'm not sure how to do this.

      For example, if they stay from July 15th to August 15th, and they are a serving member, then the first 14 days are charged at $681 and then the rest at $390. However, all the days charged in July should be added to the July total summary and all the August days to August total. (there is nothing to stop a patient spanning more than 2 months either)

      At the current time, I only have a summary field of the total of "Admission Revenue" and then a sub summary report layout sorting records by "MonthYear" to create month totals but this is counting cost from the entire length of stay to the month of admission only)

      Any help in splitting this cost to the correct month would be appreciated.

      If it helps, costing works as follows:

      As in the admission rate calculation above, its free for veterans, $681 for first 14 days of serving member and then 390 for the rest of their stay and 740 for all other types. (All per day)

      Thanks in Advanced


      (If you require more information, let me know)

        • 1. Re: Filemaker Pro 11 - Month Report Help

          Sounds like you need a related record for each day that the patient is admitted that logs the patientID, the cost for that day and the date.

          Then you can get a total for all such records over any specified date range--such as a specific month.

          Some "month, year" tricks you might also find helpful:

          You can use data formatting to reduce any date to the name of the month and the year.

          DateField - Day ( DateField ) + 1

          will calculate a value for the first day of the month for all dates in DateFIeld that fall in the same month. Sorting on a calculation field with this expression would group your records by month but also in correct chronological order.

          • 2. Re: Filemaker Pro 11 - Month Report Help

            Hi PhilModJunk,

            First, I have amended the 'MonthYear' field and it now orders in chronological order. (Thank you)

            I'm having trouble however understanding how to link each record to another field and have Filemaker create a record with a cost for each day. I have setup another table with ID, Date and Daily Cost. I am now unsure how to get Filemaker to create these cost records for each day. I understand relationships but am not sure how to use them for this task.

            Any help would be appreciated.

            If it helps, i have put the database with personal client information removed on dropbox for you to see what I mean


            (https://www.dropbox.com/s/r19gcrwumf0k459/Inpatient%20Database.fp7?dl=0)........Inpatient Database.fp7

            • 3. Re: Filemaker Pro 11 - Month Report Help

              FileMaker would not create those records, the user does when logging the costs in the first place. And on further thought, this would not be a table with one record per day for a given patient, it'd be a table where every time you need to log an additional cost item, you create a new record, identify the patient and enter the cost. A date field can auto-enter the creation date to provide the date info. This not only allows you to produce monthly cost totals but also logs an itemized list of every detail that was used to produce that cost.

              This lets FileMaker do all the number crunching and several methods are possible that can then produce a report where you see the total costs incurred for each given month. The individual entries producting that sub total can be included or excluded from that report.