Newbie: 'staging area' for data prior to db 'upload'?

[drjgs200]

This post poses a very general question.

First, my background: I have been fiddling with a5v9(desktop)for about a week now. While I am not an experienced programmer, I did a vis.basic project a couple years ago.

Anyway, with that background, I am trying to contruct a database application that does the following: My group of physicians want to keep a db of their patient encounters. Each encounter is defined by..
-a patient name (or unique medical record number)
-physician name
-date of encounter (assume one encounter per day)
-billing code (describes the level of services, eg low, mod, or high)

So, I envision the physician filling out a grid on my alpha5 application. This grid would, more or less, have the info listed above. Then, after the info was entered and looked correct,the physician would click a button to send it to the master database. In other words, the data would NOT be 'saved' into the database upon entry - which typically occurs when putting data into a browse. We need a sort of 'staging area' for the data before it is sent to the database. Is there any way to program this into and alpha five application (without importing from excel sheets)? Thanks for your help.

jim

[Tom Cone Jr]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Sounds like extra work to me, but you could base the data entry form on a transaction table that would be periodically checked and copied over to a master table. The difficulty occurs when you need to see data in the master table but only present the transaction table to the user. Perhaps you could explain why a "staging area" is needed?

-- tom

ps. In Alpha Five the term "database" is not synonymous with "table". And, records are not stored in "databases". They're stored in "tables". You seem to be talking about tables, right?

[Keith Hubert]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Hi Jim,

Welcone to Alpha.

The simple way to do this is to open the form from a button in New Record mode. You will not need the staging area that you mentioned.

[drjgs200]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Thanks for your reply, Tom. Actually, using a transaction table was my initial thought as well.

Why do I feel that we need a data 'staging area'? It is because, I am trying to combine 2 work-flow processes into one process. The two processes are
1)construction of a 'rounding sheet' as described below (we use Excel now) and
2) capturing the patient-encounter data into a database (the excel sheets don't work for this).

Another reason we need a data staging area, is that the data sometimes goes through several iterations before we decide on what should be in the database. In this way, our current Excel sheets function like a scratch pad or worksheet. I would like to avoid the multiple concurrent physician users from making frequent changes to their entries in the table set. By 'table set' I mean the parent and its linked tables. The term 'table set', of course, would not refer to the non-linked transactional tables. (I'm trying to be more precise with my database terminology here.) By the way, I assume that the transactional tables could be partially purged, or 'trimmed' periodically. We would only need to keep very recent data on the transactional tables. For example, if I pick up a list of patients from my partner, I could just copy them from his transactional list into my browse layout. In this way, I would not have to re-generate an entire patient list by typing in 20 patient names and medical record numbers.

A 'rounding sheet' is a list of patients with billing information, patient info, presumptive diagnoses, notes to self, notes to other doctors (used for hand-off of patients), things to do, etc. Only some of information needs to go into the database. The other info on the sheet is 'volatile' and is needed just to guide me through the day or, at most, the week.

I guess we could continue to construct our rounding sheets with Excel, and then at the end of the day re-enter all of the patient names and medical record numbers again - this time into alpha five table-set browse. Of course, the day is busy and time is at a premium. For this reason, I really need to find a way to combine the processes into a slick, easy-to-use app. If I could incorporate a rounding-list generator into my alpha five app, then the final 'capture-worthy' data could just be appended to the master table (non-transactional, table set). Voila! rounding list plus database all in one work-flow process.

As you mentioned, there are some disadvantages to using transaction tables here. If I use transaction tables, it seems like I would have to construct a transaction table for each user (physician). We have 40 physicians in our group. That's a lot of tables. Furthermore, each transaction table would be associated with it's own separate browse layout (essentially a user-interface for that table). Unfortunately, I have found no way to make copies of a particular browse layout (forms, panels, buttons, fields, dialogs, etc.) and apply it (or associate it) to an analogous (but separate) transaction table. This limitation suggests that I would have to build 40 separate browse layouts essentially from scratch. I don't want to do that if I can possibly avoid it.

Now, I did figure out how to make a quick copy of a browse layout, but it stays associated with the table that it was based on (of course). I could easily make 40 copies of the layout in this way, but each browse layout would be a 'window' onto the same transaction table. Hmmmm... maybe I can just have the physicians draw from the same transaction table. They can see just their own patients if I add a different filter script (upon activation of the form) to each browse. I don't know, I'd have to think about the details of how it will be used. Any thoughts? Am I making any sense? Maybe I need to hire a consultant. Did I bite of more than I can chew. I still have 20days left on my alpha five trial. :)

jim

[drjgs200]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Hi Jim,

Welcone to Alpha.

The simple way to do this is to open the form from a button in New Record mode. You will not need the staging area that you mentioned.

New record mode? I haven't come across that mode yet. It sounds too easy. I will certainly give a try right now. Thanks, Keith.

jim

[drjgs200]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Hi Jim,

Welcone to Alpha.

The simple way to do this is to open the form from a button in New Record mode. You will not need the staging area that you mentioned.

Well, Keith, I now see what you mean by 'New Record' mode. If I'm not mistaken, it seems that 'New Record mode' is the only mode that allows one to enter data into a browe or table. This mode is activated by Contol-E, or by going up to the 'Records' menu, then clicking 'Enter new record'. Am I right?

If so, then the 'new record' mode unfortunatley doesn't help me much. It only allows me to correct a record if I haven't yet moved the cursor from that record row (and if I'm going 'modeless', I think). I was hoping to find a 'mode' whereby I could enter my data quickly, then sit back and look at the overall table and decide what changes, if any, need to be made. Only after that final birds-eye assessment, would I send it to it's final table.

jim

[Peter.Greulich]

Re: Newbie: 'staging area' for data prior to db 'upload'?

... I could easily make 40 copies of the layout in this way, but each browse layout would be a 'window' onto the same transaction table. Hmmmm... maybe I can just have the physicians draw from the same transaction table. They can see just their own patients if I add a different filter script (upon activation of the form) to each browse. I don't know, I'd have to think about the details of how it will be used. Any thoughts? Am I making any sense?

Wow! That's quite a scenario. Actually though, it's probably simpler than you make it sound. You don't need 40 browses for 40 physicians. That's like having 2000 forms for 2000 customers. Not necessary. One form with an embedded browse or something along those lines. You filter the form/browse on the particular physician (presumably when he/she logs in). I guess a transaction table makes sense. Probably much preferable over Excel. You could use a relatively simple script to copy over the relevant records/or fields to the final table when ready. There is no need to retype data. Enter once and then manipulate as may be needed. Think digital, not analog (I don't mean that as a criticism). ;)

[drjgs200]

Re: Newbie: 'staging area' for data prior to db 'upload'?

Wow! That's quite a scenario. Actually though, it's probably simpler than you make it sound. You don't need 40 browses for 40 physicians. That's like having 2000 forms for 2000 customers. Not necessary. One form with an embedded browse or something along those lines. You filter the form/browse on the particular physician (presumably when he/she logs in). I guess a transaction table makes sense. Probably much preferable over Excel. You could use a relatively simple script to copy over the relevant records/or fields to the final table when ready. There is no need to retype data. Enter once and then manipulate as may be needed. Think digital, not analog (I don't mean that as a criticism). ;)

Thanks Peter. Your explanation is what I was trying to say in my bolded sentences: put the patients for all of the physicians on the same transaction table, use one 'window' to this table (a single browse 'application') and use a physician-specific patient filter (applied upon login, as you suggested).

I assume that I would use an 'append' operation to get the transaction data over on to the main table-set?

Thanks, again. This has been very helpful. It's nice to know that the transaction-table approach is not a ridiculous one. I think I can now take another stab at it - and with a little more confidence.

Jim

PS: ok, now I see.... I don't even need to modify the otherwise identical browses (as I suggested in my prior post). I can do the physician-specific filtering in response to the physician log-on.

[Peter.Greulich]

Re: Newbie: 'staging area' for data prior to db 'upload'?

I assume that I would use an 'append' operation to get the transaction data over on to the main table-set?

Yes, that would be the most direct way to accomplish what you want.

It's nice to know that the transaction-table approach is not a ridiculous one.

Not ridiculous at all. It always comes down to one's needs.

Good luck!

[Mike Wilson]

Re: Newbie: 'staging area' for data prior to db 'upload'?

negate