Conditional info for reports?

[zenmom]

Okay, I'm probably going to get in way over my head here, but I'm trying to design a rather complex report.

The report lists health supplements that correspond to a particular regimen. The doctor wants to see the list in a particular format and order.

What I'd like to do, for a given item in the final report, is test the db and see if the item (let's use "nettle" as an example) both exists in the db *and* is flagged as "current (Y/N?)". So I'd like the field on the report to do a find for Name=nettle and Current=Y.

I'm thinking along these lines:

For a given field in the report (i.e., layout), look for db record where Name = [X] and Current? (Y/N) = Y

If the found set = 0 (no records found that meet that criteria), then print a particular piece of “boilerplate” text.

If the found set = 1, then print contents of Name field from found set,

If the found set = >1,then show an alert message (since there should never be more than 1).

It seems to me there must be a way to do this, but I'm not far enough along to know what it might be. I've looked through FMP 8 The Missing Manual, but nothing jumps out at me. Any ideas?

TIA, zenmom

[bcooney]

The report lists health supplements that correspond to a particular regimen. The doctor wants to see the list in a particular format and order.

Your description is not clear to me. Can you describe the "particular" format and order the Dr requires.

I'm not following the process. Does a Dr want to put a patient on a regimen and therefore print out what supplements to take?

[zenmom]

Hi bcooney!

Sorry I wasn't clear. The doctor wants to see a list of what supplements, etc. the patient (myself, in this particular case) is taking. She wants to see them in a particular order, and by category. This means in practice the same supplement may show up multiple times, in more than one category. I've attached a .txt file to show you what format the doctor wants to see.

In my FileMaker report, I'd like to have something that for each entry (each line in the text file) FileMaker goes to the db, checks to see if there's a valid record, then if yes gets the data from that record and puts it in the report. (Of course, in my db the info for each supplement is broken up into separate fields for name, ingredients, dose, etc.) I'm assuming I'd have this function or script appear multiple times, under as many headings as necessary. However, if I start taking a new supplement, I only need to enter it *once* in the db, and the layout will fill it in as many times as necessary in the report.

I'm still learning proper terminology, which might be one reason my questions aren't clear. I used to use MS Access, and I feel comfortable with databases, but I'm not an advanced user--yet. Maybe someday!

Thanks for any thoughts you might have on this.

supplementlistrevisedbymutation_2_.txt

[bcooney]

Take this apart for me; what am I looking at?

COMT V158M (COMT H62H) - -

VDR/Taq + +

_ 2 Chewable Methyl-Cobalamin B12

_ 1/2 to 1 Quercetin 500 Plus

_ 1/2 to 1 Ginkgo Biloba

_ 1/2 to 1 Curcumin

_ 1 to 2 SAM-e

.

.

.

_____________

I am thinking that you'll have a table of supplements and a table of regiments. You'll have a join table between them, that stores the assignments of supplements to a regiment.

You'll have a table of patients, and a table of regiments assigned to the patient (a join btw regiments and patients).

To print the report of all regiments a patient is currently taking, go from Patient to Regiment to RegimentSupplement and print a sub-summary report by Patient, by Regiment, by Supplement (alpha).

Does this sound OK?

[zenmom]

Dear bcooney,

Thanks for your reply. Here's an "explication du text", as they say in literary criticism,

The first line or two indicates a particular genetic mutation (or set of genetic mutations). [And since everybody has two copies of each gene, the - -, + +, or + - indicate whether a person has a mutation in neither, both, or only one of their two copies of that gene.]

> COMT V158M (COMT H62H) - -

> VDR/Taq + +

Everything after the mutation category is the treatment regimen. In my database the second line below might correspond with a record where Name = quercetin, Product = Quercetin 500 Plus, Number (or amount) = 1, Dose type = pill.

> _ 2 Chewable Methyl-Cobalamin B12

> _ 1/2 to 1 Quercetin 500 Plus

> _ 1/2 to 1 Ginkgo Biloba

> _ 1/2 to 1 Curcumin

> _ 1 to 2 SAM-e

I'm liking the idea of a table of supplements and a table of regimens, as you suggested. My current db could easily be slimmed down into a table containing just the appropriate fields, leaving some of the other stuff (price calculations, duration, etc) in another table. The join between those two tables would be the Name field, I think. The regimen table, which would be the third table, would include at a minimum the genetic info for the mutation(s) in question, and fields to specify the name and amount of the recommended supplements. Would the join between this table and the other be at the Name field also? I wonder if I should have a field in the first table that lists all the different regimens that a supplement appears under? That seems redundant if I have a regimen table, maybe. I'm thinking out loud here. It might be useful to designate each regimen with a different alphabet letter, to avoid typing the alphabet soup of letters & numbers whenever I want to specify a particular regimen.

Then I've still got the problem of how I generate all the different sublists for the report.

I'm seeing a little farther into the fog, but I haven't gotten to the end point yet. What else do I need to know?

Thanks again for any insight you might have.

[bcooney]

Sorry it took so long to respond.

You do not relate tables by Name, but rather by an auto-entered serial number in each table (referred to as a key field). The "parent" table Regimen table would have a field, __kP_RegimenID (my naming convention) that would relate to the join table btw Regimen and Supplements. In the join table you'd have the foreign key, _kF_RegimenID, a number field.

It looks like you have a table of genetic mutations. For each there exists ONE regimen. Each Regimen has one or more Supplements.

Is this the case?

So, I'd have these tables, related by their auto-entered IDs:

Mutations

Regiments

RegSupp (join table, this would contain the dose)

Supplements

[bcooney]

I've thought about this some more. Do you reuse a regime for another mutation? Meaning, are the collection of supplements AND THEIR DOSES ever assigned to more than one Mutation? If not, then there is no need to store these collections of supplements. I would drop the Regiment table and have the join btw Mutation and Supplement directly.

By looking from Supplement to the join table (MutationSupp), you'd be able to see all the Mutations to which that Supplement is assigned.

[zenmom]

Hi bcooney,

Thanks for the reply. I've been chewing on this some more as well.

> You do not relate tables by Name, but rather by

> an auto-entered serial number in each table

> (referred to as a key field). The "parent"

> table Regimen table would have a field,

> __kP_RegimenID (my naming convention) that

> would relate to the join table btw Regimen and

> Supplements. In the join table you'd have the

> foreign key, _kF_RegimenID, a number field.

Yeah, that was starting to dawn on me after re-reading FMP The Missing Manual some more. Your naming convention helps make more clear to me how the key fields are related (i.e., primary/foreign keys). I've added auto-enter serial# fields as you described, named _kP_SupplementsID for the Supplements table and _kP_RegimenID for the Regimen table. Then _kF_ equivalents in the join table.

> It looks like you have a table of genetic

> mutations. For each there exists ONE regimen.

> Each Regimen has one or more Supplements.

> Is this the case?

Yes.

> So, I'd have these tables, related by their

> auto-entered IDs:

>

> Mutations

> Regiments

> RegSupp (join table, this would contain the

> dose)

> Supplements

I also spent awhile last night pondering checkbox setups. I played around with it a bit, but since there are over 100 supplements in the program, I'm not sure it's the right choice.

Okay, here's something that's tripping me up. If I have a field _kP_RegimenID, is there an easy way to populate the field _kF_RegimenID in the join table, without entering each one by hand? I solved it by starting the serial number at the same place in both fields, and then just adding new fields in the join table until I had the same number as in the Regimen table, but I wonder if there's a better way to do it.

I see you've sent me another message, so I'll continue with that one.

[zenmom]

Dear bcooney,

Continuing on...

> I've thought about this some more. Do you reuse

> a regime for another mutation? Meaning, are the

> collection of supplements AND THEIR DOSES ever

> assigned to more than one Mutation? If not,

> then there is no need to store these

> collections of supplements. I would drop the

> Regiment table and have the join btw Mutation

> and Supplement directly.

A given collection of supplements will only appear under one area of support (mutation or body system). The dose for a particular supplement will not change from area to area. For example, if a patient is taking 2 tablets of GABA, then "GABA 2 tabs" should show up in every collection that includes GABA. If GABA is increased to 3 tabs, then that should be reflected everywhere GABA shows up, which will be under several different areas of support. I'd also like to have the option to add the total dose (ex. 1500mg) in addition to the number of capsules or tablets.

"Mutation" isn't really the right name for that table, since it includes areas that aren't mutations, for example "kidney support", "general systems support". Maybe "Area of Support" is a better name for that. I had been calling it Regimen, but in general use a "regimen" also includes specific doses. And in this case, although the supplements may stay the same, the amounts (doses) of each supplement may change from month to month.

> By looking from Supplement to the join table

> (MutationSupp), you'd be able to see all the

> Mutations to which that Supplement is assigned.

That's exactly what I want. Plus, I also want to look at it from the other direction, so I can see all the Supplements that go with a particular Mutation (or as I think I'll call it, Area of Support).

I made the _kP_AreaID a text field instead of a number field, as I wanted to be able to use two lines for the mutation designation in a few instances.

So now I've got these tables:

Supplements

Area of Support

AreaSuppl (the join table)

The tables Supplements and Area of Support each have their own primary key, and the AreaSuppl table has two foreign keys, one for each of the previous two tables. In the Relationships window, I've defined a relationship bewteen Supplements:_kP_SupplementID and AreaSupp::_kF_SupplementID, and then between Area of Support::_kP_AreaID and AreaSupp::_kF_AreaID.

Now my brain has just collapsed. I'm having this tenuous notion that I should create a layout based on the AreaSupp join table, that includes a portal into the Supplements table, which will include a find based on the Area ID of the current record. Does this sound right to you? That way it can list as many associated supplements as it finds.

Okay, I'm going to go mess around with it some more. Any thoughts you may have on this are very welcome.

[zenmom]

Grrr. So, does this mean (getting back to an earlier question) that I'll need 6 (or however many) different records in the join table to specify the supplements for calcium support? That is, each record to contain a field with the _kF_ serial# that corresponds with the "calcium" Area of Support, plus a field for the serial# from the _kF_ that specifies the particular supplement? I'm thinking there's got to be a better way to do this, a better way to make all those associations.

Back to pondering.

[bcooney]

I don't see another way. You'll need (or a Nutritionist) will need to relate the supplements for each Area of Support.

On a Supplement form, you'd create a portal to the join table. You can include fields from the Area of Support in this portal, even if it's two relationships away from Supplements.

[bcooney]

Okay, here's something that's tripping me up. If I have a field _kP_RegimenID, is there an easy way to populate the field _kF_RegimenID in the join table, without entering each one by hand?

The typical setup is to have a portal of the child records on the parent form layout. Then turn on Allow Creation of Related Records in the relationship (by dclick the relationship in the Relationship Graph). This will allow you to directly add supplement IDs to the join table thru the portal.

[bcooney]

Here's a demo.

zenmom.zip

[zenmom]

Ah! Thanks, I think I'm starting to get it now. Seeing an example helps a lot.

M.

[zenmom]

Dear bcooney,

Using the example you set up in the file zenmom, I set up a similar structure in my own file. I created separate tables for different areas of work, and then join tables for associating one type of thing with another. It probably looks kind of clunky to you, but it seems to working well enough for what I need, except...

The layout "Support by Area" is the report I most need to produce. I need to sort the contents of each portal window according to priorities for that particular area of support. I made a stab at this by creating a table jSortArea which holds records that associate a supplement with an area of support, and then added a field to hold a number that I could assign according to the order in which I wish the supplements to appear. But even though it seemed like a good idea at the time, I can't seem to get any farther with this. When I run a sort, either I can't get that field to show up, or the results don't show on the Support by Area layout.

Beyond that, I'm wondering if there's a "collapse" switch that can be used with portal windows, so it will only show as many lines as there are entries.

Okay, that's enough for tonight. Will tackle it again tomorrow. I'm attaching my db so you can see what I've done so far.

TIA for any help you can give on this.

new_supp_db.fp7.zip

[bcooney]

I caught a couple of things. Your sort order should be a number field in the join table (it was a container with a lookup?).

You need a subsummary report. I've included a sample in the revised demo attached.

I think that doses need to be in the join table. Also, I'm not sure how you plan to deal with dose changes. Perhaps there's a week 1 dose, week 2, etc. Then you can have respective print layouts for each week.

Also, it will be very helpful for you to learn the anchor-buoy technique. Go to FMI's website and download the developer conventions whitepaper.

new_supp_db2.zip

[zenmom]

Dear bcooney,

Many thanks for your previous help! And... now I've got two more questions.

I've slimmed down my working copy of the supplements database to fewer tables until I get the basics straightened out. I'll add the other tables (doses, cost calculations, sources?) later. For now, I've got it down to 5 tables:

Area

Supp

jSortArea

jSuppArea

Dose

(I'm not using Dose at the moment, but it had a lot of data I didn't want to re-enter, so I didn't delete that one.)

The table Area defines the different areas of support (body systems & genetics). The table Supp lists all the supplements that show up various places on the lists. The join table jSortArea includes two fields I'm using to control the sort order, first support areas and then supplements within each area. The join table jSuppArea defines the multiple areas a supplement can support.

In my layout "Report 2", I've used the format you suggested using a subsummary. It's been working fine, with just a few instances of panic when things looked strange, which turned out to be a sort issue. I figured out how to get the fields to slide up and the parts to resize as necessary, since some of the area descriptions take several lines. And I've got the majority of the data entry in the join tables done. So far so good.

Also, I downloaded the white paper you mentioned. It sounds like the anchor-buoy technique is a convention for how to lay out Table Occurrences? However, trying to compare that with the examples in the FM Pro 8 Missing Manual book got me rather confused. I may print out the doc and mull it over some more later.

Now to my first question. *Something* is not joined correctly, because although I'm getting headers on my layout (in Preview), they're not always the *right* headers. It's sometimes picking up one of the other areas a supplement belongs to, rather than the one I want. I've got a feeling that it's a "directional" issue, but I can't figure it out. I've reconnected the tables about a dozen different ways, which has only gotten me in worse shape. What am I missing here?

My second question is fairly straightforward (I hope!) - When one of the subsummary lists breaks between pages, how does one either a) tell FM to start the section on a new page, if to do otherwise would result in a break, or have FM put something like "[section name], cont" at the top of the new page after the break?

About doses data. I'm not sure I want doses in a join table. There are a couple different things that need to be expressed, such as how many (capsules, tables, drops, etc), and how much (in mg, mcg, and so forth), and some calculations are involved. This is why I was thinking that doses should have their own table, but perhaps I am

[zenmom]

Dear bcooney,

Many thanks for your previous help! And... now I've got two more questions.

I've slimmed down my working copy of the supplements database to fewer tables until I get the basics straightened out. I'll add the other tables (doses, cost calculations, sources?) later. For now, I've got it down to 5 tables:

Area

Supp

jSortArea

jSuppArea

Dose

(I'm not using Dose at the moment, but it had a lot of data I didn't want to re-enter, so I didn't delete that one.)

The table Area defines the different areas of support (body systems & genetics). The table Supp lists all the supplements that show up various places on the lists. The join table jSortArea includes two fields I'm using to control the sort order, first support areas and then supplements within each area. The join table jSuppArea defines the multiple areas a supplement can support.

In my layout "Report 2", I've used the format you suggested using a subsummary. It's been working fine, with just a few instances of panic when things looked strange, which turned out to be a sort issue. I figured out how to get the fields to slide up and the parts to resize as necessary, since some of the area descriptions take several lines. And I've got the majority of the data entry in the join tables done. So far so good.

Also, I downloaded the white paper you mentioned. It sounds like the anchor-buoy technique is a convention for how to lay out Table Occurrences? However, trying to compare that with the examples in the FM Pro 8 Missing Manual book got me rather confused. I may print out the doc and mull it over some more later.

Now to my first question. *Something* is not joined correctly, because although I'm getting headers on my layout (in Preview), they're not always the *right* headers. It's sometimes picking up one of the other areas a supplement belongs to, rather than the one I want. I've got a feeling that it's a "directional" issue, but I can't figure it out. I've reconnected the tables about a dozen different ways, which has only gotten me in worse shape. What am I missing here?

My second question is fairly straightforward (I hope!) - When one of the subsummary lists breaks between pages, how does one either a) tell FM to start the section on a new page, if to do otherwise would result in a break, or have FM put something like "[section name], cont" at the top of the new page after the break?

About doses data. I'm not sure I want doses in a join table. There are a couple different things that need to be expressed, such as how many (capsules, tables, drops, etc), and how much (in mg, mcg, and so forth), and some calculations are involved. This is why I was thinking that doses should have their own table, but perhaps I am not thinking about this the right way. And I'm still thinking about how to handle the problem of doses that change over time. Maybe by defining different "regimens"?

But the fire I have to put out right now is how to list the supplements and amounts currently being taken. I'm hoping that, once I solve the immediate challenge of getting the right header to show up, I can add a couple fields to the subsummary area to display "number" (a number field) and "unit" ("capsules" or...) taken.

Are you still reading? Sorry to take up so much of your time! You are very kind to provide guidance, it is much appreciated. I've attached a copy of my "slimmed down" db, with the name new supp db3, in case you have time to take a look.

[zenmom]

Dear bcooney,

Many thanks for your previous help! And... now I've got two more questions.

I've slimmed down my working copy of the supplements database to fewer tables until I get the basics straightened out. I'll add the other tables (doses, cost calculations, sources?) later. For now, I've got it down to 5 tables:

Area

Supp

jSortArea

jSuppArea

Dose

(I'm not using Dose at the moment, but it had a lot of data I didn't want to re-enter, so I didn't delete that one.)

The table Area defines the different areas of support (body systems & genetics). The table Supp lists all the supplements that show up various places on the lists. The join table jSortArea includes two fields I'm using to control the sort order, first support areas and then supplements within each area. The join table jSuppArea defines the multiple areas a supplement can support.

In my layout "Report 2", I've used the format you suggested using a subsummary. It's been working fine, with just a few instances of panic when things looked strange, which turned out to be a sort issue. I figured out how to get the fields to slide up and the parts to resize as necessary, since some of the area descriptions take several lines. And I've got the majority of the data entry in the join tables done. So far so good.

Also, I downloaded the white paper you mentioned. It sounds like the anchor-buoy technique is a convention for how to lay out Table Occurrences? However, trying to compare that with the examples in the FM Pro 8 Missing Manual book got me rather confused. I may print out the doc and mull it over some more later.

Now to my first question. *Something* is not joined correctly, because although I'm getting headers on my layout (in Preview), they're not always the *right* headers. It's sometimes picking up one of the other areas a supplement belongs to, rather than the one I want. I've got a feeling that it's a "directional" issue, but I can't figure it out. I've reconnected the tables about a dozen different ways, which has only gotten me in worse shape. What am I missing here?

My second question is fairly straightforward (I hope!) - When one of the subsummary lists breaks between pages, how does one either a) tell FM to start the section on a new page, if to do otherwise would result in a break, or have FM put something like "[section name], cont" at the top of the new page after the break?

About doses data. I'm not sure I want doses in a join table.

new_supp_db3.fp7.zip

[zenmom]

(Apologies for the double post, my computer hiccuped which sending...)

[bcooney]

I do think it's better to build a little and get it right before you add more.

Yes, anchor-buoy is a way to organize and name table occurrences. You'll need some convention, as the complexity increases.

I don't understand the jSortArea table. If you want to control how supplements sort within an Area, put the sort order number field right in the jSuppArea join table.

Handling page breaks in sub-summaries is challenging. There isn't a straightforward technique for repeating a sub-summary heading on a subsequent page if the details continue to the next page. However, there are some techniques. Search the forum, I think I've seen one posted. Check Matt Petrowsky's site,JMO's Database Pros or FM Advisor.

Here's an amended file. Your Report 1 wasn't based on the correct TO.

new_supp_db4.zip

[zenmom]

Hello, Collective Wisdom,

I've got a layout that is getting close to what I want. I request the range of records I want, and display them, organized into sub-summary lists (thanks, bcooney, for your help). The problem is that sometimes I'd like a page break before a particular sub-section, and sometimes not, depending on how long the list of items is below that particular heading (for example, the old "widows & orphans" problem with a heading and 1 item at the bottom of a page, and then the rest of the items on the next page). But I don't want a page break before *every* sub-section, because some of the sections are only a few items long, and three or four of those would fit on a page. So... is there any way to export a particular layout/ request/ sort/ "form view" combination in such a way that preserves the layout formatting, but allows one to tweak the page breaks before printing?

My significant other accuses me of being a "bit twiddler", I guess that's true.

Have any of you out there solved this type of problem?

TIA for any advice you might have.

Marcia Morrison

in Salem, Massachusetts (brrrr)

[bcooney]

Hello, again, Marcia. Have you seen this thread? I haven't studied it, but it appears to be a technique that might get you thinking.

K. Frank Sub-summary Technique

...and then there's exporting your report to Word using XML.

EZxslt - XSL

[zenmom]

Dear bcooney,

Thanks for showing this to me, it looks like it'd fix that pesky list break problem. Unfortunately, it requires v9 and I'm still on v8.5. But hey, a good reason to upgrade!

M.