Psychotherapy DB

[Auraboros]

Hi...new to the forum...thanks! I am rather new to FM but have created solutions before...I am embarking the creation of the Psychotherapist's patient database and right off the bat I am stumped. To keep it simple, let's say I have a Patient Table, an Invoice Table, and a Sessions Table...however, my patient table will include both INDIVIDUALS and COUPLES...a couple consists of two people, with unique information for each person, name, phone, email, siblings, parents, etc. However, they will still function within the database as ONE unit...meaning they couple will receive many invoices (addressed to the joint address, or a specified email address, or both email addresses) the couple will have many sessions...but a session and invoice will be linked to only one couple, not two individuals. Should I approach this with hidden fields and just use the same table for both couples and individuals but have the unique fields for couples show up if a radio button is pressed? Or should I somehow join two individual records into one (??) or what? How would this be solved? THANKS!!!!

[webko]

I would probably add a BillingEntity table to hold this information, then the two people can be added to that Billing Entity - I can also see the possibility that the Billing might go to someone different again (their Employer, for one possibility)

[Fitch]

@webko nailed it. You might want to google "database party model" as this is a database design problem that comes up a lot.

As a rule of thumb, when there's people involved, you're going to need a people (or "Person") table. Then, if you need the kind of functionality you described, you'll make a "Party" table (aka Billing Entity).

Welcome to the forums.

[Auraboros]

Thank you so much! I have spent a good portion of the day trying to learn about the Party Model and Join Tables...unfortunately since I am so new to this I couldn't make my situation fit...is it possible for someone to help me understand this or should I just get back to the tutorials! 

My situation seems simpler than the complex examples of party models I have been looking at. I have a "person" table...and an invoice table, and a sessions table. These are all one to many relationships...person (one) to invoice (many) etc. There are some instances where I will need to "link" two persons into a group...a married couple, and that "entity or organization" (a couple) will then have the one to many relationship with invoices and sessions...although I want to be able to split apart that "couple" if I have to without messing up any of the relationships. How do I actually set up the tables to make this work? Is there a "join table" between "persons" and "sessions" and "invoices"...and what fields would go in it if so? Can "persons" still relate to "invoices" and "sessions" as one to many while some are joined together as couples? Jeesh.... Sorry I am so lame with this...THANKS!!

[Lee Smith]

There are several resources that you can access about FileMaker. 

See if this link helps: 

 

[comment]

26 minutes ago, Auraboros said:

How do I actually set up the tables to make this work?

It's actually simpler than you think. Basically, you need to think of every "patient" as being either one or two "people". In other words, you have a one-to-many relationship between Patients and People. Then you only need to set up the user interface. That can be done in many ways - for example, you could let the user enter the first person of a Patient directly from a layout of Patients.

 

Edited February 5, 2016 by comment

[Auraboros]

So "Patient" could be couple or individual, a many to one relationship to "People" and invoice and sessions relate to the "Patient" table...so a patient, either a couple or an individual, can have many sessions, and many invoices. I will have to do my homework to see how this is physically set up with fields etc. Thanks to all! Hopefully I will figure it out...

[Auraboros]

Thanks to all again, I really do appreciate the help here. I've done some more research and have come up with this chart for this problem. Does it make sense? I do still have two questions about it..

I have three tables (see attached diagram) "People", "Entity" which have many to many relationships to one another, therefore I have put in a "Join" table between them. Even through some "People" can be in several "Entities" and some "Entities" can contain several (two for couples) or more "People" most "People" will belong to only one "Entity" (Individual)...actually ALL people will belong to these (one for each person) "one person entities"...it will be named the same name as the individual, and there will be as many individual entities as there are people. There will be some couples, created as a unique couple entity using the couple's name, like "John & Jane Doe" which will have two people belonging to it. I plan to have a variety of layouts using fields that pertain to the type of entity. The key field will be the name of the entity, a person's name, "John Doe" a couple "Jane and John Doe" or a group "Group Therapy #1" Does this make sense? Or should I go back to the drawing board... :-)

My two questions: 1. Should it be structured with hundreds of individual entities named after the hundreds of people in the "People" table. The primary reason I think I have to do it this way is because of the links for billing...a billing "entity" needs to include the individuals, as I will have to be able to bill them as an entity, as well as billing a couple as an entity. OR, should I leave individuals in the individual People table and still be able to link invoices to them AND to the Entity table?

2. Should most of the information about the person be in the "People" table, or in the "Entities" table. 

If you folks think I am too much of a novice to waste time on I will not be upset...just tell me all these mysteries will be solved if I spend the next six months studying Filemaker...I AM doing that, but so far I have not been able to make sense of this particular problem...however, I certainly may not know enough yet to understand how to solve it on my own. Don't get mad at me! I am not looking for a short cut! I want to learn this...but can't get started on my database until I know how to set up the ERD....

Thanks!!!

Relationships.pdf

[comment]

I have a question for you: how often is the same person treated both as an individual and as a member of a couple (or another type of group)? Or as a member of two (or more) couples?

 

[Auraboros]

Thanks "comment"...my practice consists of about 30% couples...so out of 100 persons, maybe sixty of them make up 30 couples. However, those persons in couples are usually not ever treated as individuals--but sometimes they are so that option has to be open! To make things more complicated, though, I need to have them (the 2 individuals that make up a couple) be each a billable entity as there are times the couple gets billed, or each individual in the couple gets billed (for different sessions)...this can change from session to session (insurance reasons.) We operate about 3 or four 8 member groups at the most at any one time...so maybe 24 people are in the groups that could also be in individual therapy. Hope that answers your questions...the more I get into how this DB has to operate, the more complicated it gets...that's why nothing "off the shelf" works...

[comment]

The reason I asked is because a join table between Patients¹ and People will add significant complexity to your setup - and you need to be sure it's justified. It is justified only when for the purposes of this solution it is important to know that the John Smith participating in group therapy #27 is the same John Smith receiving individual therapy #52. That is not always the case.

 

--
(1) I cannot bring myself to calling a table "Entities", but perhaps "Accounts" might fit?

Edited February 8, 2016 by comment

[Auraboros]

Yeah, "accounts" would work...I'm still not certain what to do. If I could have a "persons" table and be able to hook up another person to a person in the same table I would be able to cover most of the problem. There actually is a way to do that, isn't there? I only have "couples" (two people) and individuals that need to be billed. The group sessions I do can be treated differently with other tables. The "sessions" table could be linked to "persons" (one to many) (which, if I can have a person linked to another person in the same table, could also service couples.) Invoices would be linked to the person...and two people if they constitute a couple (is that possible?) IF...I can have two people linked in the same table...jeesh....

[comment]

9 minutes ago, Auraboros said:

If I could have a "persons" table and be able to hook up another person to a person in the same table I would be able to cover most of the problem. There actually is a way to do that, isn't there?

You can do that quite easily - but I am not sure that is the best arrangement for your described situation. But ultimately it's your call. All you would have to do is place another occurrence of the People table on the graph, name it Spouse, and relate it as:

People::PersonID = Spouse::PrimaryPersonID

 

[Auraboros]

Well...obviously I don't know much about what I am doing, so "my call" is probably not that informed. If I have an invoice table that I would like to relate to individual people, and some invoice would have two people listed as recipients (the couples) would there be a way to have both of these names on an invoice? The mailing address and everything else would be the same (I would never enter couples in the "person" table with different mailing addresses) but there would need to be two email address, and the invoice would need to be mailed to two recipients...sometimes, and sometimes not! 

[Fitch]

Seems unusual to send a bill to two recipients.

You know, we database designers have a tendency to see relationships as 0, 1, or "many." If there truly are only one or two people per invoice and no more, it's tempting to simply add a person1 ID and person 2 ID to the invoice and forget what I said about the party model. And you could add a "related to" field to the Person table and use a self-join for the spouse.

[Auraboros]

Yes, it IS unusual to bill two recipients...and drives us crazy. It is due to insurance...sometimes both spouses in a couple have different insurance coverage as they each work for a different employer...sometimes they need invoices to be only in one name, sometimes the insurance carrier requires both names, sometimes it goes back and forth from session to session. It is crazy. I need to be able to send them to both email addresses too...or one or the other. Drives us nuts.

These are all great suggestions and when I know more about constructing the DB I'll be able to implement them. If anyone has anything else to suggest, please do. I would rather create an ERD that works than experiment in the middle of construction to see what works...but I may have to do that. This couples problem makes the whole thing crazy...there isn't a CRM out there that I have found (other than custom made DB's for therapists...but even those are rare) that intelligently deal with this issue. I would like to find a solution that works elegantly.

Thanks again to everyone!!

[Fitch]

Is this database meant to handle insurance billing as well as client billing? Or do the clients deal with their insurance companies?

[Auraboros]

In Ontario the client pays us for our services and then gets reimbursed when they receive our receipt...so the "invoice" we send them they submit for the reimbursement. So no, the database does not handle "insurance billing"...just that the invoices have to be made out a certain way and have specific information on it.

[Auraboros]

Me again...not sure where to post this, but a general question about populating fields. I am planning on using an iCal plugin that will pull in iCal events. These events are appointments with clients, who are in the solution "clients" table. My question...how can I link an event title, such as "Todd Hayen - individual therapy" to my "Todd Hayen" record already in my client table? I want information stored in that record to populate certain fields, but since the actual event title is not an exact match to the name in the "name field" in the client table I don't know how to do this. All of the events pulled in from iCal have the full name (which could be matched to a record in the client table) with a dash ( - ) following the name...

Thanks!

Todd

[webko]

This would probably have been better in a new topic, but...

I think you can get most things to match on Name - if there's a mis-spelling or they're not in the database, you might need to manually intervene

Let's import the data to iCal_Title

Then have a match field that's an auto-enter calc like:

Left ( iCal_Title; Position ( iCal_Title ; " - "; 0; 1) )

This says take the Left characters of the iCal_Title field up to the first occurrence of " - " which is the delimiter for the end of the name, according to your info above.

[Auraboros]

You guys are geniuses...I feel like a caveman with most of this stuff...

I have another one, which is more on topic...

I have been trying to do this self-join to select a partner (spouse) from my contacts table. I have followed all the rules for setting this up but it simply would not work. I tried this setup so many ways I lost count.

I finally got it to work. In fact, it works exactly as I would like it to work...however, my relationship to the TO is screwy. In fact, according to all that I've read, it is backwards...

I have attached the little database..."Partner" fields (to choose spouse) are hidden unless "couple" is selected in the "type" field. 

Even though this works I am concerned because I am afraid when I start building other relationships to the original table it is going to get all screwed up...is it? 

Everything I've read says to relate the original table clientIDpk to the partnerIDfk in the TO...but that didn't work at all. When I reversed it, relating the original table partnerIDfk with the TO clientIDpk it worked...why?

Thank you so much!!!!

spouse1.fmp12

[Fitch]

Seems ok to me. It would work either way. It's all about the context -- i.e., the TO that the layout is based on.

Instead of "Customers 2" I'd suggest you name that TO "Partners" (although I generally prefer singular for all table names) or "Customer Partners" or something and don't just refer to it as "the TO" when talking about it -- I think I understood you but it was confusing.

[Auraboros]

Thanks Fitch...I should have cleaned up the table titles, didn't mean to leave it Customers 2...in fact the original table needs to be renamed to client and the TO renamed client_PARTNER or something like that.

I'm not quite sure if I understand the "TO the layout is based on". I do think I understand context...but that's what I thought was strange about this layout. Not all fields are from the original client table (Customer). And there is no portal for fields from the TO. 

Still...this thing works the way I want...you say it would work either way...well, when I tried to switch around the relationship it did not work at all. So I am still confused. Like which table is now the parent and which is the child? Or is that designation irrelevant? Should I change the field names?

Thanks again for taking the time...I really do appreciate it...I've included an updated file if anyone wants to look at it.

 

spouse1.fmp12

[Fitch]

I'm saying that in this instance of a one-to-one relationship, for the purposes of linking the records it doesn't matter whether you store the parent ID on the partner record vs. store the parner ID on the parent record.

However, the constraints of a particular interface design may lend themselves better to one option over the other. In this case, given that the context is the client record and you want to select the partner from a value list, you're correct, storing the partner ID in the client record is the way to do that.

[Auraboros]

Thanks again Fitch. I will keep learning.

One thing I DON'T like about this solution is the field where I have the pull down (using Name_Full as the secondary field in the value list) puts the ClientID in the field instead of the person's full name. Several of the other "versions" I had come up with, that didn't actually "work", did not do this...and I am confused as to why it doesn't put the Name_Full field contents in the field when I choose it from the pull down value list...instead it puts the first field choice (ClientID) in the field...that's why I had to create the partner field so we could see the person's name rather than just an ID number...I'm sure I am doing something wrong here, but of course have no idea what it is...

Thanks!

[Fitch]

Nope, you're doing it right. The ID is what you want to store.

[LaRetta]

15 hours ago, Auraboros said:

One thing I DON'T like about this solution is the field where I have the pull down (using Name_Full as the secondary field in the value list) puts the ClientID in the field instead of the person's full name. Several of the other "versions" I had come up with, that didn't actually "work", did not do this...and I am confused as to why it doesn't put the Name_Full field contents in the field when I choose it from the pull down value list...instead it puts the first field choice (ClientID) in the field...

Hi Todd,

This behavior perplexes many people but it is normal behavior for drop-down lists.  You can change it to pop-up menu ( which is probably how those fields which did work were configured ) or you can place a merge field below/beside the id drop-down for displaying the 'second value'.  You can even be a bit clever and stack the fields, hiding the id field when not empty.

Tom Fitch (our resident flying pig) is correct that storing the ID is always best approach.  This allows you, over time, to change or correct the text 'description' or 'second-field name' without breaking all your relationships which depend upon it. 

[Auraboros]

Wow LaRetta...you are absolutely right, I WAS using the pop-up when it "worked"...I thought they were the same, pop up and drop down, guess not. Jeesh. Thank you so much for the comment. I have gone through a bunch of the trainings online for FM and even the advanced training only covers about 2% of what this program can do (I am sure what I have studied has told me the difference between drop down and pop up, I just don't remember.) 

Thanks for your help...and everyone here. I am sure I will have many more questions, and hopefully one day I'll be able to contribute!