Default values and Automatic append

[JetLi]

hope someone can help me on this, I'm working on a simple patient monitoring and billing, if a patient is admitted, the date/time admitted is encoded and the patient is immediately billed with room rates, initial doctors orders , lab fees, doctors fee etc, how will i automatically do in alpha five a process wherein fixed fees will be automatically added daily to a table with patient id/name, room rates, doctors fee, the room rate is billed daily and doctors fee is charged daily also,until the patient is discharged, a logical field will identify if the patient is still not discharged before an automatic entry of billing is recorded in a table probably called billing.thanks so much in advance.

Date Patient ID Room Rate Lab Fees Drugs Prof. Fee
12/10/2011 P001 400 500 350 150
12/11/2011 P001 400 150
12/12/2011 P001 400 150
12/13/2011 P001 400 100 200 150

How will i automatically bill the patient in alpha five so that billing of room rate and prof fee's which are fixed billing rates until the patient is discharged?

[Stan Mathews]

Re: Default values and Automatic append

You have nothing to append from so you have to create a temporary table for that.

First empty the temporary table.
Copy all records from from your example table to the temp table, filter for not discharged.
Append from the temp table to your example table appending only the patient id field from the temp table. The values for the other fields "appended" to your example table can be expressions.


pat.gif

[JetLi]

Re: Default values and Automatic append

Thanks Stan, Great help for beginners like me, how do i do this automatically daily until the patient is discharged? I mean, if i will not open the computer or application for two or more days, alpha five will just append on it's own from the last append date to the current date if i will open the application later

[JetLi]

Re: Default values and Automatic append

In addition to the automatic append and default values, what could be my table design if there are deductions for each rates? e.g. for the 450 room rate , a deduction of 200 will be deducted daily, same thing with prof fee which is 100 daily will be deducted, and for drugs a fixed deduction is deducted say it is 1000, so if the patient is billed for drugs with 2500, the patients bill for drugs would be 1500, same thing with lab fees, a fixed amount is deducted, e.g. for lab fees a deduction of 100 is deducted to whatever the lab amount , regardless of the number of days of a patients stay in the hospital. the deductions for the drug and lab fees are fixed while the deductions of Prof fee and room rates are multiplied on the number of days of a patients stay in the hospital.

[Stan Mathews]

Re: Default values and Automatic append

No, Alpha won't do anything on its own.

I suggest you create a "dummy" table, say named default. This table would have one field. Name, type, etc of the default table doesn't matter. You use this table to serve as the basis for a form.

Then you create a form based on this table. You define a date variable (global scope) for the form and place it on the form. You modify the operation I suggested and have the date field append expression be the variable. That way you can set the date on the form and the value you set will be used in the operation. When you arrive on Monday and process for the weekend you would do it once for Saturday's date and once for Sundays (or you could do Friday, Saturday, Sunday on Friday ahead if no one will be discharged).

[CALocklin]

Re: Default values and Automatic append

This table would have one field.

This is a side issue but I think it's important to understand - especially for getting new developers "started off on the right foot".

I almost NEVER create a table now that doesn't have fields for the Create date and Change date. I used to - but not anymore. There have been far too many times when someone has reported a problem and tried to tell me that a record has "been there forever" or "just added" or "not changed since it was created" or "just changed yesterday". The result was that I spent time trying to solve a problem that didn't exist because the "timing" on their record wasn't what they said it was. I'm sure of this because I've had similar issues reported with tables that have these dates and been able to prove that the record was added/edited at a different time than reported by the user. And when I've nailed it down to a specific date, they've almost always remembered or discovered that, yes, it was likely that somebody changed it on that day.

The real key issue for me is that it keeps me from wasting time trying to fix something that doesn't need to be fixed. It may also be used to confirm that something really does to be fixed and I'm not wasting my time doing it.

This goes for "one field" lookup tables as well as other, more complicated tables. One example was when someone complained that "the database" lost a particular lookup value that he had been using since the beginning. After looking at the data I asked if he had added any new lookup values recently. He said he had. I said, "No you didn't. You changed the old value to the new value. That's why the old value isn't there anymore." (There were no new Create dates but there was a new Change date.) I might have guessed that anyway in this case but now I had proof because the actual date was in the record. The customer couldn't argue with the data - but they often will argue if you can't prove your suspicions.

And, for what it's worth, here are the field rules I use for the two fields: (mine are actually named Create_dtf and Change_dtf)

Create_dtf:
Simple default of "date()" with skip set to .T. so nobody can change it.

Change_dtf:
Set the CanSave event of the table as follows: (change the field name as necessary for your table)
tc = table.current()
tc.change_dtf = date()
IF eval_valid( "tc.change_tmf" )
tc.change_tmf = left(time(),5)
END IF

WARNINGS/COMMENTS:
- Make sure the above script is in the CanSave event - not the OnSave event.
- DO NOT make either field a calculated field!!!! Many people do this then find out that it creates a major disaster when they run certain operations and EVERY Create Date and Change Date field gets updated to "today". I repeat - DO NOT DO THIS!!!
- Some of my tables also have a Change_tmf field so code for that is included in my standard script because I don't type the actual code - I just type the "short key" combination "jjchgdt" that I've defined in MacroExpress. (If I'm going to do this in every table, why would I want to retype it every time?)
- If a record is created or edited using xbasic, those fields must be updated as part of the xbasic routine. These field rules will not be triggered when editing the record with xbasic.
- Do not combine the first two lines of the CanSave event into "table.current().change_dtf = date()". It seems to work sometimes but is not reliable. BTDT.

EDIT - While most of what I said is true, I really should have read more carefully first. Stan is completely correct in this case. A dummy table should not be used to hold any data other than one dummy record just to keep the attached forms from going into Enter mode every time they are opened. Therefore, date fields are not necesssary for those tables.

[JetLi]

Re: Default values and Automatic append

No, Alpha won't do anything on its own.

I suggest you create a "dummy" table, say named default. This table would have one field. Name, type, etc of the default table doesn't matter. You use this table to serve as the basis for a form.

Then you create a form based on this table. You define a date variable (global scope) for the form and place it on the form. You modify the operation I suggested and have the date field append expression be the variable. That way you can set the date on the form and the value you set will be used in the operation. When you arrive on Monday and process for the weekend you would do it once for Saturday's date and once for Sundays (or you could do Friday, Saturday, Sunday on Friday ahead if no one will be discharged).

Stan, please do share a sample that i can download by incorporating your idea and suggestion? this would be of great help for beginners like me, i can do it in VB6 but i can't figure out what your suggesting in Alpha. Thanks so much in advance.

[Stan Mathews]

Re: Default values and Automatic append

Ok.

mypatient.zip

Set a date on the form, use all four buttons, examine the patient table. Suggest you start with 12/14/2011. The records in your earlier post are in the table and one record for a discharged patient to illustrate the filter.

[JetLi]

Re: Default values and Automatic append

The problem that I'm trying to solve is a billing for hospital.
e.g. a patient is admitted Today 12/11/2011 so immediately there is a bill on room and prof fee and some initial doctors orders, so the user/encoder will now enter the following data.
Date Drug Prof Fee Surgical Supplies Room/Meals Lab Fees Other Fees
12/14/2011 250 150 50 400 75
Then the next day 12/12/2011 the patients family had bought some supplies in the hospital, so no problem the encoder will find the patient name, fill in the supplies bill
and of course fill in also the daily bills such as room rates and doctors fee because everyday doctors visits the patients and patients are charged daily for room rates.
the data will now be:
Date Drugs Prof Fee Surgical Supplies Room/Meals Lab Fees Other Fees
12/11/2011 250 150 50 400 75
12/12/2011 150 100 400

Here is now my problem: What if the patient/family member didn't buy anything from the hospital, for the past 3 days, so the bill will now be the data above which is not updated, supposedly there is a bill last 12/13/2011 with Prof Fee of 150 and Room/Meals 400 and also last 12/14/2011.The patient will now be discharged today and asked for his/her bill,How can i Update the total the bills in alpha with this situation, the application should determine the last day that was not billed which is 12/13/2011 and Automatically add the data mentioned above, so the final bill would now look like the one below:

Date Drug Prof Fee Surgical Supplies Room/Meals Lab Fees Other Fees
12/11/2011 250 150 50 400 75
12/12/2011 150 100 400
12/13/2011 150 400
12/14/2011 150 400

And in addition to where i'm stucked in alpha is, what if there are insurance deductions wherein every bill there is a corresponding deduction, example. for the Prof fee above which is 600, the insurance will pay 400 so the patient will now just pay 200, also with drugs,the insurance will pay 100 so the patient will pay 150, again with room rates
the patient is suppose to pay 1600 the insurance will pay half of it and 800 will now be paid by the patient, and so on , so the patient bill will now be totaled minus all the insurance discounts.

Can someone please help me on this. Thanks very very much in advance.

[MoGrace]

Re: Default values and Automatic append

This must be a pretty small hospital...how many beds is it? Because I would think most of your charges would be coming from various depts as they are incurred: Pharmacy, Surgery, etc. not just Admin. Not to mention that what insurance companies may allow, may not be what they actually pay. So to get an accurate bill paid at the time of discharge seems a bit unlikely.

[Stan Mathews]

Re: Default values and Automatic append

You have progressed from a simple question about how to perform a set of actions to a more complex question about how to design the table and relationship structure of a complete application.

I doubt if anyone with complete answers will be inclined to answer all of your questions. See post 10 of this thread for a link to database design modeling suggestions. If you have specific procedural questions after implementing the proper design you will get lots of help.

By the way, your example of

Date Drug Prof Fee Surgical Supplies Room/Meals Lab Fees Other Fees
12/11/2011 250 150 50 400 75
12/12/2011 150 100 400
12/13/2011 150 400
12/14/2011 150 400

is not a

normalized

database record structure.

Your records should look something like

Date Patient_id Type Fee
12/11/2011 P001 Drug 75
12/11/2011 P001 Prof 200
12/11/2011 P001 Room 400
12/11/2011 P001 Surgical 487

That way if you decide to add another fee type you can easily do it. Your way would require adding another field to the table.

[MoGrace]

Re: Default values and Automatic append

Hi Stan,
Simple doesn't begin to describe it! I would think he needs to develop some sort of rule based system given all the different reasons people frequent a hospital: emergency, inpatient, outpatient, labwork, etc.

[JetLi]

Re: Default values and Automatic append

Thank you very much for all the answers and suggestions, i appreciate the effort of members of this forum, I never found something like this on other development platforms, even if i'm still evaluating alpha five i get immediate replies compared to others which you must have a licensed copy of the product before questions can be entertained/answered.Nice forum.

[JetLi]

Re: Default values and Automatic append

Ok.

[ATTACH=CONFIG]30264[/ATTACH]

Set a date on the form, use all four buttons, examine the patient table. Suggest you start with 12/14/2011. The records in your earlier post are in the table and one record for a discharged patient to illustrate the filter.

Stan,
I tried your sample and it is working just find,hope you can still help me on this, Can you pls expand the solution that you made, the date is just limited to 1 append, how can i append records so that records will be appended from the last transaction date until the current date that i will provide? the solution that you gave is just for a single day transaction.Thanks much

[Ted Giles]

Re: Default values and Automatic append

CAL. Do you ever use the Audit Trail feature?