5 Replies Latest reply on Mar 31, 2015 10:30 AM by philmodjunk

    Automatic summation between tables



      Automatic summation between tables



      I have a table, "CaseLog", used to track every patient I manage, including billing units (ASAunits). I have another table, "TimeSheet", used to track my daily units billed (units), and my scheduled position (call_position), among others. At the end of each day, I complete a daily record in "TimeSheet" kind of summarizing the important data from my day. Currently, when I reach the field (units), I switch over to the table "CaseLog" and manually add the (ASAunits) 1-10 patients I might have seen. I then enter this (ASAunits) value in "TimeSheet" in the field (units). Is there a formula I can use for the "TimeSheet" (units) field that would first, lookup (date) from TimeSheet, then find all of the records in "CaseLog" that have the same date, and then add the (ASAunits) field from each of the records in "CaseLog" and finally input them as a sum into the (units) field on "TimeSheet"? As an additional kink, when I am on call, I work from 7am until 7am the following day. On "TimeSheet" I log this call shift as one date, even though the 7hr period from midnight until 7am takes place on the following calendar date. On "CaseLog", however, the dates are entered according to the date the medical service actually took place (for billing accuracy); eg. if I see a patient at 0230am, this is obviously on the next day for billing purposes. This would seem to create a conundrum during the lookup process on "TimeSheet" that is attempting to match the date from "CaseLog" to the entry on "TimeSheet" and summate the units accordingly. There is an additional field on "TimeSheet" (call_position) which denotes when I am on call, and accordingly spending the overnight in the hospital. If my initial question about creating a formula, or lookup, were possible, would it be possible to also add some sort of situational variance whereby if the "TimeSheet" record in question has a (call_position) denoting my being on call, it would tally the (ASAunits) from the "CaseLog" for both the (date) on "TimeSheet" and the (date)+1day...but only in that situation?

      I have searched for this every way I can think of, without much success. I realize this is a lengthy background for my question, but I do appreciate any help you can offer. Thank you for your time.

      Oh, and if the answer is simply to continue to manually add the totals, that is fine as well, as that is how I have been doing this for 4yrs now. Just looking to create a better mousetrap, if possible, and it's fun to tinker with FMP.


        • 1. Re: Automatic summation between tables

          Simply manually add the values up? Heaven forbid! That's why we have a computer and database do do this stuff for us!

          What you need are two things: a) a relationship from TimeSheet to CaseLog that matches to the correct set of CaseLog records and then b) a way to compute a total of that set of related records.

          First, the relationship:

          In TimeSheet, you need two fields of type TimeStamp that record the beginning and End of your shift. You want TimeStamp fields as they record both date and time so you don't have any issues with shirts that span the midnight hour. For easier data entry, you can define a pair of date and a pair of time fields for recording this start and stop and then your two TimeStamp fields can be calculation fields that use the TimeStamp function to return the needed values, just be sure to select "TimeStamp" as the result type if you choose to set it up this way. You'll need a corresponding single TimeStamp in CaseLog. Again, a calculation field that combines the values of a date and a time field probably makes for simplest data entry for this.

          You can then set up this relationship:

          TimeSheet::cShiftStart > CaseLog::cTimeStampField AND
          TimeSheet::cShiftEnd < CaseLog::cTimeStampField

          Now there are two ways to get the total that you want. In the TimeSheet table or from a layout based on TimeSheet, Sum (CaseLog::ASAUnits) will compute the needed total. You can also define a field of type Summary in CaseLog that totals ASAUnits and then this reference: CaseLog::SummaryField from the same context of a the TimeSheet table or layout will return the same total.

          Note: The ExecuteSQL() function could also be used to compute this total.

          • 2. Re: Automatic summation between tables

            First of all, thank you for your time and clearly for your expertise in FMP. In "TimeSheet" I changed TimeSheet::timein and TimeSheet::timeout to type timestamp. As you suggested, in "CaseLog" I created what will ultimately be a hidden calculation field CaseLog::timestamp to create a timestamp based on the date of service (DOS) and the surgical procedure start time (ProcedureStart). I additionally created the relationships you directed. On "TimeSheet" the summation field did calculate correctly on some of the records, but not all; and, on some of the records it recorded the sum as a multiple of the actual total units from that shift (eg. 2x, 3x, 4x, and a 24x). I have looked at the affected records to compare their data to the ones that displayed correctly, and I have not found an obvious source for the error. I also attempted the same comparison between the correct records and the records which displayed no value at all in the summation field.  Any ideas on what the possible source of error might be?

            • 3. Re: Automatic summation between tables

              Not without a much more detailed description of your database. This is a pretty straight forward set up. Only thing that comes to mind is to make sure that you selected "timestamp" as the result type for your calculations.

              • 4. Re: Automatic summation between tables

                Thank you so much for your time and expertise! I did go back and tinker some more; actually starting with a blank database and entering records anew seemed to be part of the problem. It wasn't running the calculations on records that were already in the database. No big deal. I never did figure out the multiples thing, but that problem has not reappeared. I am loving the database now! It actually is better than I imagined it could be. Thank you, again.

                • 5. Re: Automatic summation between tables

                  Adding or changing an auto-enter calculation does not update existing records. You have to take extra steps to update them. See this thread for a trick that can be used much of the time (But not always) to update such fields: Updating values in auto-enter calc fields without using Replace Field Contents