Can FMP8Adv house a database inside a database?

[at0micgirl]

OK, I don't know if that's quite the way to put my question, but it sums it up. btw, I answered that I'm an "Intermediate" user of FMpro, but really I'm an Intermediate/Adv user of Access who's now switching over. I used FM a few times back when it was version 2 or so, but I've never had the $ to get a real mac til now. Anyhoo...

I know how to create a simple database. But what I'm trying to do is more complex - at least for me.

Since I think it's easier to work with specifics, this is what I'm trying to create:

The main database is single-user to house all my "Outstanding Medical Debt", and I'm going to have the main table be Medical Facility Info (name of hosp/clinic, contact info, MedRec#, and a field to calc TOTAL DUE to that facility).

Now, I could handle making a sub-table to track each visit to such facility (VisitID, Date of Visit, Dr. Name, Total Visit Cost), BUT when I'm billed they never just send one bill on one date with a grand total. It's broken down into DrugCost, LabCost, ProcedureDone, etc.

So how would I make a sub-table for each visit?

Since I'm entering this data by hand from old records & bills, I need to not worry about what order I enter it in (meaning I could never just enter the total for each visit - cuz I won't know til it gets calculated).

I also will need to qualify each of the VisitDetail_ItemCost by whether it is an outstanding debt or PAID by insurance. Then when I calc the TotalVisitDebt, I won't be including anything PAID for already. That means when I get to my final TOTAL DUE for the facility, it will reflect what I actually owe.

Does this make sense?

And eventually I'm going to need to get data from this db to add to the Master Debt Due db, but since I'll just be pulling totals due, I think I can figure that out.

I'm certainly trying to read everything I can, and play around with it too, but I'd love any help so I can get this off the ground sooner.

Even if someone can point me to a db already like this so I can study it...

THANKS!!!

Carrie Anne

[Genx]

Does this make sense? -- No hehehe!.

No, but welcome to the forums .

To be honest it makes little sense to me (though it may possibly make sense to someone else)... I'm just struggling to find the question here e.g. sub-table for each visit? Could you possibly dumb it down a bit?

[at0micgirl]

Yes, that's the question.

I want to create a table inside a table inside a table.

I want to draw a picture to make it easier & I just realized I can. I've attached a scan of my draft sketch of this. Hopefully that will help. (Sorry it's messy - I was just doing it for myself when I drew it - hopefully still useful though.)

If you need to me to redo sketch cleaner I can. Thanks!

[Fenton]

You could fairly easily create exactly what you have in your drawing, but it would be a little tricky to show it in Browse mode. You could create it fairly easily for printing in Preview mode, using a Subsummary part for the data that only appears "occasionally", ie., the ID and dates. You would do that as a report in the bottom level "visit_treatments" table.

"So how would I make a sub-table for each visit?" What you're calling a "sub-table" is just a table which is a child table of a parent table. It is common to have tables in a hierarchy:

Patient ->visit ->visit_treatments

Each one of these tables can view "up" to its 1 parent, down to its many children, and/or down to grandchildren (if any).

One problem with what you show in your drawing is that the ID and Date do not appear on every line. But what really makes it difficult (if not impossible) is that you have "visit" data on one line, then multiple lines of "visit_treatment" data on the following rows. As you likely know, portals don't normally work that way.

The "visit_treatment" data you show below the visit date data is related data, visible by looking down "thru" the Visits table, to the Visit_Treatments table.

It is possible to "gather" related data however, and put it into the portal, under the "visit" data. One tool I've used are 2 Custom Functions, GetRows and GetNthRow, by Bruce Robertson, available at:

briandunning.com/filemaker-custom-functions/list.php

They could gather your visit-treatment data into a single calculation field, in Visits, tab-separated, so you could likely format it to look like your drawing. You could not however enter that data there; that's going too far. The visit-treatment data would be on the same row of the portal with the visit data, but they'd look as if they were in further table.

One (big) problem though. You cannot control the size of the rows; they are not going to expand or shrink to accommodate the number of visit-treatments. This is another reason why what you want to see is really only possible as a report with a subsummary for visits.

Edited January 3, 2007 by Guest

[at0micgirl]

Ok, I'll try to wrap my brain around that.

The site you gave looks like a great resource - thanks!

And now that you've given me a "keyword" (subsummary) - I can more easily find what I need in the help file. I'm looking at the subsummary layouts help page right now, and it looks very helpful.

Thanks again!

[Fenton]

Yes, what you wanted to see as a portal would be fairly easy as a sorted report, in the visit-treatments line item "sub" table, with the Visit data as related fields within a Subsummary part.

If you're going to view records for multiple patients at once, you'd need 2 Subsummary parts, the top one for Patient data (sorted by Patient ID), and the lower one for Visit (sorted by Visit ID). It will produce a hierarchal view. If only viewing one patient then Visit ID is sufficient; the Patient data would just be in the Header (or Title Header) part.

Both of those ID fields can be auto-entered by an "Allow creation of related records" relationship from Visits.

The view would be scripted, so it can Sort, then go into Preview mode. To get something cute you could pop this up in a new sized-to-fit window. Then, when you're done looking at it, close the window.

Edited January 3, 2007 by Guest