Which is kind of how I've had it structured. The problem arose when a kid moved out, got their own family, so they no longer popped up in queries on the original family, so I couldn't see their older transactions without going into the kid's file directly, and the old ones didn't show up there either, since they were billed to a (now) foreign family.
My solution was to create a view that only uses the transaction level bill-to I.D. rather than family level I.D.s for the searches. Seems to work, although it's still in testing. On Thu, May 31, 2012 at 9:35 AM, J BLAUSTEIN <[email protected]> wrote: > ** > My suggestion is have two different billing level both including the > family id and patient id - you then can query either the family or patient > - I do that in a law application where we bill either on a client or > matter level > j > > > On 5/30/2012 1:21 PM, William Stacy wrote: > > > > On Wed, May 30, 2012 at 9:38 AM, Ben Petersen <[email protected]>wrote: > >> I've noticed that I'm getting back two copies of my post. Is that >> happening to others? >> >> > Family Table (holds address, home phones, and primary addressee link) >> >> Did I understand that there was a bill-to column here also? >> > > no, although I think Jay is suggesting that. I do have a primary adressee > linking column for sending mail to families, but don't send much postal > mail any more. Usually its the same ID as the bill to person, and often > the same as the patient themself. > > >> >> > Patient Table (holds name, personal phones, e-mail, date of birth, >> family >> > and "bill to" links) >> >> > Transaction table (holds date, time, patient link, etc) >> >> > Transaction detail table ( links to transaction table, holds line item, >> > price, quantity, patient share, and payments/credits) >> >> My reflexive inclination here would be to not have the patient share >> as a separate column, but a separate line item. Each line item with >> it's own bill-to, using the patient id for their share. Eliminate >> bill-to's from other tables. >> >> > Hmm. So by way of example, right now I have a trans. detail table where a > single line item and it's payment look like this: > (sorry if they don't line up) This forms the basis of patient statements > (parens indicate computed values): > > Item# Desc Amt. Quan. (Amt*Quan) Pamt > 10 Exam 150.00 1 (150.00) 10.00 > 1 Cash 10.00 1 ( -10.00) -10.00 > > The remaining $140 is handled with one or more more lines such as: > > 99 Disc. 60.00 1 (-60.00) > > 9 Ins Pmt 80.00 1 (-80.00) > > and kept separate from the patient statement. > > In your suggestion, it would look something like: > > 10 Exam 150.00 1 (150.00) Pt. Share: 10.00 > 1 Cash 10.00 1 (-10.00) Pt. Paid: -10.00 > 10 Exam VSP Share: 80.00 > 99 Agrmt Disc: > -70.00 > 9 Ins . Pmt 80.00 1 (-80.00) VSP paid: -80.00 > > (obviously each entry would need it's own posting date) > > My statements are gonna look pretty long when you do this for every line > item (lens, frame, contact lens, coating, you name it). but it does make > sense to me. > > > I'm moving toward changing this hierarchy, putting the Patient table >> at the >> > top, with the family table just being an incidental (and temporary) >> location >> > link. >> >> Yep. >> >> >> > I kind of like the idea of a person having several different >> > addresses so maybe a separate linking table is actually needed as Bill >> D. >> > suggests. >> >> It works well in the app I mentioned. Same for Phone/Fax >> >> >> > One last thing. I've always had charges and credits in the same column >> in >> > the trans. detail table, with charges being positive currencies and >> credits >> > being negative, simple summing them establishing running balances. >> Anyone >> > object to that method, or should I separate out credits into their own >> > table? >> >> Like so many things it depends, but if it haven't gotten in your way >> so far, I don't think there is anything inherently wrong with it. >> >> Ben >> >> >> > > > -- > William Stacy, O.D. > > Please visit my website by clicking on : > > http://www.folsomeye.net > > > > > > -- > ** **** **** ** > > *J. Blaustein***** > > *J Blaustein Associates, Inc.* > > * 12 Herrick Drive* > > * Lawrence, NY 11559* > > *516-371-3445 FAX 516-345-8009***** > ************ > -- William Stacy, O.D. Please visit my website by clicking on : http://www.folsomeye.net
