AnsweredAssumed Answered

Automatic summation between tables

Question asked by JohnDrexler on Mar 26, 2015
Latest reply on Mar 31, 2015 by philmodjunk


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.