How to create a new file for every patient's visit

[kroywen19]

hello everyone.

I am a newbie and somehow managed to make a simple database for a clinic but I don't know how to create a new file for each patient visit. What I did was to create numbered tabs (20 tabs) representing the number of visits of the patient with date. You can just imagine the numbered tabs would run the entire length of my monitor if I make 50 tabs (please see attached file). My problem is that if a patient visits the clinic twice a year, the records will only be good for 25 years. I tried to search for an answer online but can't figure this out.

I tried to check the table in the layout mode and I found out that I only have 1 table with over 2,000 fields.

Please enlighten me. Your help would be very much appreciated.

Thanks

[No_access]

1st, their is no reason to ever have 2000 fields in a single table, you need to create at least 2 tables and a relationship between them. 1 may be as an example, the customer information, ie. name . unique number, address etc..

2 would be the data you enter for each visit, a new record gets created each time they visit, then you can show it any way you want.

if you attach your file we can help you.

[Kris M]

http://en.wikipedia.org/wiki/Database_normalization

[LaRetta]

Hi kroywen19,

As explained, your data model is not taking advantage of relational structure so it is 50-times more complex than need be. You are welcome to zip and attach your file or if you are uncomfortable doing that you can PM one of us here and arrange a private view. We can then provide a very quick example of good structure which will be a HUGE relief to you once you see it.

It may take a bit to switch all your hard work over to new structure but it is well worth it and you should do it now so you have less 'knitting to unravel'. And welcome to FM Forums!! :-)

[kroywen19]

Thanks guys,

I am grateful for your quick response folks.

Here is my file.

Sample File.zip

[No_access]

Here, take a look at this, should help you. in your case I think it is just easier to make a new database

Sample.zip

[kroywen19]

Thanks No_access,

but why is the "new record" icon grayed out?

How can I make a new record in your sample file?

[No_access]

It is not grayed out for me, you need to make sure you fully unzipped the file and open the unzipped one.

[Lee Smith]

In addition, Win machines sometimes will change the file access from read and write, to read only, so if the suggestions above don't do it, then see if this was changed in the download process.

[Ste!]

Hi! tryout this file, i hope it could be helpful

for "Previous RX" you can do the same work as you can see in "Visit" portal.

Sample File new.zip

[LaRetta]

You've been given several samples now and I'm going to add to it ...

The RTF2 ( attached ) shows the true power of changing a structure ... not something great that I did ... just showing how great relational structure truly is and what flat-file vs relational might look like side by side for this particular project. The highlighted table Contacts orig (what we started with) will be completely replaced with those remaining tables and fields.

2,399 fields in one table reduce down to easily under 300 for the entire business (once finished) counting all tables combined. And being relational, it is limitless in the number of purchases during an Exam, the number of complaints and it doesn't matter the number of exams (which originally were tabs).

The relationships in the center are all working but you can't create an invoice from the LineItems until script is written. I hope this is helpful between all of the files you've now received. In truth, the screen is too cluttered and some information such as Hobbies should be moved to secondary tab. And it would have been nice to plug in true products table ... I ended up putting color and size in Products table and they might be better served as multiline (checkbox) or related table ... I do not know enough about your practice at this point.

By the way, I did not get fancy even in trying to put this together for you because your file is damaged. It was acting quite funky and Recovery provided it out. I would suggest that you start with a clean new file and if it ever crashes on you, immediately replace it with a backup copy (which should make every few hours while designing). This is a larger, complex project for someone with no relational background. I am impressed that you are taking it on. We are always here if you need something else.

EDIT: And yes, there would be a button to go forward back through events etc etc. These were only relational connections and repointing the fields.

UPDATE: I was rushed and I realized I didn't finish adding the scripting for creating the exam to RX (and no global in Patients). Layout should be based upon Exams. And now I'm not where I can get to a computer until tomorrow. I will correct the file then and re-attach it ... Simply too much on my plate this week, LOL!

RTF2.pdf

Edited October 9, 2012 by LaRetta

[kroywen19]

A million thanks to you guys.

More blessings coming your way....

[kroywen19]

I have a few things in mind today.

What if:

- I use navigational tabs to go from table to table such as patient personal info, eye exams, chief complaints and so on.

- I need to create a welcome screen before logging in or used as logging window.

- hide the title bar and status bar of Filemaker.

Are these things possible?

If it is possible, what can I do to achieve them?

[kroywen19]

Hi! tryout this file, i hope it could be helpful

for "Previous RX" you can do the same work as you can see in "Visit" portal.

Hello Stye!

I noticed that when I make a new visit record on your sample file, the previous visit is gone.

Also , the portal color changes to like a negative film when clicked outside the field items.

[kroywen19]

You've been given several samples now and I'm going to add to it ...

The RTF2 ( attached ) shows the true power of changing a structure ... not something great that I did ... just showing how great relational structure truly is and what flat-file vs relational might look like side by side for this particular project. The highlighted table Contacts orig (what we started with) will be completely replaced with those remaining tables and fields.

2,399 fields in one table reduce down to easily under 300 for the entire business (once finished) counting all tables combined. And being relational, it is limitless in the number of purchases during an Exam, the number of complaints and it doesn't matter the number of exams (which originally were tabs).

The relationships in the center are all working but you can't create an invoice from the LineItems until script is written. I hope this is helpful between all of the files you've now received. In truth, the screen is too cluttered and some information such as Hobbies should be moved to secondary tab. And it would have been nice to plug in true products table ... I ended up putting color and size in Products table and they might be better served as multiline (checkbox) or related table ... I do not know enough about your practice at this point.

By the way, I did not get fancy even in trying to put this together for you because your file is damaged. It was acting quite funky and Recovery provided it out. I would suggest that you start with a clean new file and if it ever crashes on you, immediately replace it with a backup copy (which should make every few hours while designing). This is a larger, complex project for someone with no relational background. I am impressed that you are taking it on. We are always here if you need something else.

EDIT: And yes, there would be a button to go forward back through events etc etc. These were only relational connections and repointing the fields.

UPDATE: I was rushed and I realized I didn't finish adding the scripting for creating the exam to RX (and no global in Patients). Layout should be based upon Exams. And now I'm not where I can get to a computer until tomorrow. I will correct the file then and re-attach it ... Simply too much on my plate this week, LOL!

Hi LaRetta,

"In truth, the screen is too cluttered and some information such as Hobbies should be moved to secondary tab"

Please elaborate on this because what I'd really like is to make separate tabs out of the Patient info, Prior Rx, Case history, Medical history, etc. while creating unlimited visits on each patient.

I tired to explore on your sample and tried emulating what you've done but still I can't finish my project.

Thanks

[Ste!]

Hello Stye!

I noticed that when I make a new visit record on your sample file, the previous visit is gone.

Also , the portal color changes to like a negative film when clicked outside the field items.

HI! Here is a new batabase, let me know if something goes worng!

Sample File new2.zip

[kroywen19]

HI! Here is a new batabase, let me know if something goes worng!

Can't open the file on filemaker...

[Ste!]

- I need to create a welcome screen before logging in or used as logging window.

- hide the title bar and status bar of Filemaker.

Are these things possible?

Hi, in your last post you told us about the possibility to ha a welcome screen and hide the bar of filemaker ...

Now you have both ... just open the file go to the lower right corner and click on ">>database" to switch. (it's a button!)

if you need to allow the filemaker bar click the little window on the lower left corner as you can see in the picture.

if the file could not be opened correctly with FM i don't know why i'm sorry ... i have tryed to download it ... no problem found!

let me know.

[kroywen19]

HI! Here is a new batabase, let me know if something goes worng!

Thanks again Ste!

Finally opened the file after doing a recovery.

The thing is, when I add a new visit (on both portals), it does a find request.

[kroywen19]

Hello guys,

I hope somebody help me with this because I can't seem to make this db right.

This is a variation to the sample file I posted some days back in which I made and added navigation tabs to

show the tests made to a patient per visit by clicking ti the tabs.

Before making portals to each of the test, I would like to know if the relationship graph is in the right order.

If you notice, patient visit table is left out above patient table.

I really don't know how to resolve this problem.

Please see attached files.

Thanks again in advance.

join db scrnsht.zip

[LaRetta]

Hi! Thank you for the screen shot of your graph. You certainly understand n:n (many-to-many) by using join table. :^)

From first blush I would question duplicate join tables (PatientSpecPurchases and PatientCLPurchases). It is usually better to have one Purchases table with a Type field (CL or SPEC) and if there are fields which totally differ it doesn't really matter (fields are pretty cheap) but if a lot or they are containers, large text etc, you can place those fields off in a 1:1 relationship based upon your unique PurchasesID. In this way, a Patient's single Invoice would hold lines from each as a single financial obligation. Same with Exams.

Also, is that an Inventory table (where stock is tracked) or a Products table holding your products for sale? And what would be stored in the Patient Inventory? I would suggest providing list ( or screen shot ) of the field names inside each of those tables or the file itself (zipped). Unfortunately, a table occurrence name alone (which is all we can see from that shot) only tells part of the story.

You have come a long way from 3,000 fields in a single file!! :yep:

[kroywen19]

Hi LaRetta,

You can find a copy of my file along wuth the screen shot you told me about.

Regarding Inventory, I would like a portal to show the available frames in stock and to deduct the sold ones.

[LaRetta]

Sorry but there is no file attached. :^)