Grouping presentation of data

[torifile]

Apologies in advance for what I'm sure will be a very easy question to answer.

I want to have a screen that presents my records grouped by a particular field.

Therapist A

+ client a

+ client b

+ client c

Therapist B

+ client d

+ client e

Therapist C

+ client f

etc...

I can do this using the layout wizard but it only displays this way in preview mode when I use the "columnar list/report" template. I want a view that I can browse and/or use with IWP similar to this. I know there's an easy way to do this, but I can't figure it out right now.

My layout currently looks like this:

<<therapist>>

<<clientID>>

I thought this would do it, but it's not. Any pointers?

[torifile]

Ok, I think I had a lightbulb come on... The therapist has a one-to-many relationship with clients. i should define a table of therapist with the clients as records. Right now, I've got the therapist field in the client info table. It makes logical sense that way, but doesn't from a DB standpoint, I guess. How would I define this relationship?

Now the question is, can I show the data in this way without having to use portals? The reason I ask is because while one therapist could have 3 clients, another could have 7. Ideally, I wouldn't want to have to make a portal that's 7 lines long because it wastes space and is pretty ugly (I really don't like the way portals look in general). I don't want to make a portal with fewer because then there would be scrolling involved.

How I would do it with PHP and mySQL, say, would be to query the DB and do a while loop on the grouping variable, printing the clients on a line by line basis until the end and then print another "header" with the therapist and repeating. A nested while loop, I guess. Is there an analogous method in FM?

[transpower]

Have two tables: 1) therapist, 2) client. Have a portal in therapist to show the clients; make the portal three rows and show vertical scroll bar. I don't know of a way to dynamically adjust the number of portal rows shown.

If you really dislike portals that much, I suppose you could open a New Window and display the clients in table view; you could adjust the size of the Window based on the number of clients.

[torifile]

Alright. I guess I'll use portals... I just need to figure out how to make them pretty

This seems easy enough, but what about the relationships between these two tables? Should I "allow creation of records" for either table? I suspect since there's one therapist to each client, the client table should be allowed to create rows in the therapist table, right?

Also, say I wanted to go a step further, like this:

Therapist A

+ client a

- 9/22/04

- 9/29/04

- 10/6/04

+ client b

- 10/4/04

- 10/11/04

+ client c

- 10/3/04

Therapist B

+ client d

- 9/8/04

+ client e

etc.

That is, group appointments by client which are grouped by therapist. Or am I being crazy?

[RalphL]

You have added another table Appointments, both Therapist and Client need to be related to Appointments. Therapist needs to see appointments and Client needs to see appointments.

I think for most thing I would work from the Therapist table, there are fewer of them. A portal in Therapist would show Clients and another would show Appointments.

NewCenturyData had a demo called Separation Demo that might be useful.

http://www.newcenturydata.com

Look under Filemaker 7

[Fenton]

I would think the other way around, the Therapist table should (possibly) be able to create records in the Clients table; in a Clients portal* on the Therapist table layout.

One of the biggest problems I see in most databases is duplicate data. From the Clients table, you have no way of easily seeing if a therapist already exists; so how can you safely just add one?

The same would be (partly) true from the Therapists table. But if you're sure that a particular client can only have 1 therapist; and even if the client has another therapist already, you'd see it. But what about all the data entry for the Therapist? Surely you have more than just the name?

*If indeed there's only 1 therpist per client, then you don't need a portal, just related fields. Portal doesn't hurt though; it's more or less the same thing. If there's more than one therapist per client, then you need a "join" table.

You definitely need a line items table for the dates. That's where your report above would be. Data entry could be from a portal in Clients. You may want to use a button for a new entry (not "allow creation"), so you could sort the portal descending; in case of a lot of dates.

As far as grouping for reports, you have to enter Preview mode; which works with portals also, if everything is set to "slide up" and "also reduce the size of corresponding part." But is normally used with a Subsummary part(s) Therapist Subsummary Part, Client Subsummary Part, Dates in the Body.

As you said, SQL "prints" the data in order to display grouped. Preview is how FileMaker does it. Yes, it would be nice to be able to coerce it to plain text sometimes. (Try the Copy All Records command on a layout with a portal. Or Export to text.)

[transpower]

I like the idea of the third table: appointment; now I think you have the correct database architecture.

[torifile]

Ok. I'll try these suggestions out. Thanks for the advice. BTW, I've already got a table for appointments. Is there an alternative to preview mode for showing the grouped data, though? Preview mode doesn't work with IWP - how most of my users will be accessing the DB.

[torifile]

I would think the other way around, the Therapist table should (possibly) be able to create records in the Clients table; in a Clients portal* on the Therapist table layout.

One of the biggest problems I see in most databases is duplicate data. From the Clients table, you have no way of easily seeing if a therapist already exists; so how can you safely just add one?

The same would be (partly) true from the Therapists table. But if you're sure that a particular client can only have 1 therapist; and even if the client has another therapist already, you'd see it. But what about all the data entry for the Therapist? Surely you have more than just the name?

*If indeed there's only 1 therpist per client, then you don't need a portal, just related fields. Portal doesn't hurt though; it's more or less the same thing. If there's more than one therapist per client, then you need a "join" table.

You definitely need a line items table for the dates. That's where your report above would be. Data entry could be from a portal in Clients. You may want to use a button for a new entry (not "allow creation"), so you could sort the portal descending; in case of a lot of dates.

As far as grouping for reports, you have to enter Preview mode; which works with portals also, if everything is set to "slide up" and "also reduce the size of corresponding part." But is normally used with a Subsummary part(s): Therapist Subsummary Part, Client Subsummary Part, Dates in the Body.

As you said, SQL "prints" the data in order to display grouped. Preview is how FileMaker does it. Yes, it would be nice to be able to coerce it to plain text sometimes. (Try the Copy All Records command on a layout with a portal. Or Export to text.)

Ok, I'm stuck. Again. This is frustrating because I know it should be an easy solution, I just can't see it right now.

I've moved the "therapist" field from the client information. I have a layout with the client table with the related field of therapist in the layout, so that the therapist can be assigned at the time the client info is entered. I'm having the same problem with the grouping. I think that I'm doing something wrong because the only way to get the therapist field in the client layout editable is to check the "Allow creation of related records" box.

Again, I know this should be easy but I'm really stuck...

Maybe I'm relating the wrong field between the tables... Right now, I'm relating patientID in clients to patientID in therapists. Am I doing something wrong?

[torifile]

Agghh! I'm just about there. Where I am now is using therapistID as the primary key for the therapist table and a foreign key for the clients table. That much works so far. But I can't figure out how to add a new related record. The result is that I have a new therapist created every time I create a new record. That makes sense because that's what I'm asking it to do, but not what I want it to do. I know that I can do this with portals but it's unwieldy given that I'm trying enter the client information.

Maybe I've confused the heck out of myself? Is there a way to go the other way without creating a new related therapist record if that therapist already exists?

[transpower]

In the portal of the clients for the therapist, you should be able to create a new related client--type data into the field in the last row of the portal.

[torifile]

In the portal of the clients for the therapist, you should be able to create a new related client--type data into the field in the last row of the portal.

I guess I'm not making myself clear with what I need. So far, I've got two options:

1) Make a screen for the therapists table with related records for the clients shown. I can do this as a portal or as a related field. Allow creation of related records checked for the clients table.

2) Make a screen for the clients table with the related records of therapist shown.

The only way 2 is workable is if I allow related records to be created in the therapists' table. Unfortunately that won't work because I don't need a new therapist created when a client is added. So I've chosen (so far) to go with 1. The issue is that the client's have a lot of information associated with them and using portals with information from every client that therapist sees is unwieldy. The other alternative is to use a portal to add a client to the therapists' list and have a new screen for the client specific information. This would require 2 screens (and more room for error) to get the information in.

Is there any way I can have the therapist table updated with the related records WITHOUT having to do this? The primary issue is that the therapist is assigned AFTER the client information is entered. Why is this so hard? Maybe someone could whip up a nice easy example that I can look at in FM7? That might make it easier... Thanks for all your help so far!

-Sammy

[transpower]

Yes, there should be three layouts: therapist, client, appointment (the join file). You should have buttons on the layouts to go back and forth between the layouts. Presumably the therapist table is fairly stable; it's the client file that's more dynamic. Therefore go to the client layout first and have a value list for the therapists (field-based).

[RalphL]

Is there any way I can have the therapist table updated with the related records WITHOUT having to do this? The primary issue is that the therapist is assigned AFTER the client information is entered. Why is this so hard? Maybe someone could whip up a nice easy example that I can look at in FM7? That might make it easier... Thanks for all your help so far!

Now if I understand what is going on. A new client calls for an appointment. The user creates a new record in the client database and enters the data for that client. Then the client is assigned to therapist. What determines which therapist is choosen? Then an appointment is made. How do you know when the therapist has an opening?

As I see this the therapist database is very stable, old ones leave and new ones join. Clients may be more dynamic and much larger. Appointments is where things are going to be very dynamic. I think that this what you are really interested in. You need to know when there is an opening, make a list of clients for each day, billing, etc.

[torifile]

Yes, there should be three layouts: therapist, client, appointment (the join file). You should have buttons on the layouts to go back and forth between the layouts. Presumably the therapist table is fairly stable; it's the client file that's more dynamic. Therefore go to the client layout first and have a value list for the therapists (field-based).

ok, I think I'm getting it. slowly but it seems to be sinking in...

Now what is this "join" you speak of? I've got the "Learn filemaker pro" book. Is it like the line item example they talk about?

[transpower]

Yes, pp. 118-119 of the Stars' book. If you want a more advanced book on this topic, see the Que book Using FileMaker 7--it has much more detail, but it may overwhelm a newbie.

[torifile]

Just picked up Using FM7 this afternoon. I'll have a look through and see what I can figure out.

[torifile]

I FINALLY got it.

I was able to figure out the whole join table thing and now it works! I made it more complicated than it needed to be but I stripped down what I was working on to the essentials, just to get a clearer picture of what I needed. It turns out it was a simple little thing - my initial attempt at a join table didn't work because it wasn't getting the proper keys in it.

Now I'm onto something a little more complicated. Having a "star join" working right between the three tables. I'm going to shelve that idea for now because my solution is working. But that's the next task... Thanks, all, for the help!