Basic setup of Client management/history

[Long_p1]

Hi guys, I hope you are all well. I'm fairly new to the forum and this is my 1st post.

I am using FM12 to set up a Customer Management system. I have recently gone back to being a self employed massage therapist having been unemployed for a time and going bankrupt due to the property/financial "problems" we currently have.

I've used FM before (FM10 I think) so this basic set up is really frustrating me, I've got info overload and worst still cant find the copy Filemaker Bible I brought last time i created a database the way i wanted it.

I'll try and give as much info as I can (sorry if its to much) so that any other issues you guys can see may be flagged up now.

So...... I want a system where when I see a client for the first time I can record all there data (Contact/emergency contact/medical history) from the consultation form........ so that will be one layout

I also need to record details of each visit/appointment and treatment given (date, type (value list) and a notes field (it will also have a timestamp field)

Then i need to record sales/billing, now most of the time these will be related to a treatment/appointment ^^ (so would be a 1-2-1? to treatments but a many-2-1 for clients?) but sometimes people may also pay in advance for a block of treatment or purchase gift vouchers.

I'm thinking I'd like 2 main layouts.... One for the Initial consultation and then an "Appointments" screen (layout) which would be the main used screen where when i select a client (via value list?) it displays just the main data: Name, age, post code.

Then a portal? with scrollable previous treatment notes plus any relevant medical history.

--- This way I can have a quick look before i see them any info I need to know plus helps with customer service ---

I need to then add details from the current treatment once complete that would enter all the data into the "Treatment" table.

Lastly i would like to be able to input the sales/payment details from that screen or if its easier just create a button that takes me to a/the "payments/sales" screen but with the details pre populated

Ultimately I love to be able to set up a interactive calendar and schedule (was looking at SeeCo's free solution) but thats just too much for now without getting somewhere with the basic setup

At the moment I can't get FM to pull up a clients details into the "Appointment Screen" when I select them from the value list.

and am lost with the same for the sales

Hope that all makes sense.

I've attached some screen shots to what I have that will hopefully help you visualise what I'm thinking and where Im going wrong.

I know there are plenty of similar questions out there but if you would be good enough to reply to my questions I may then be able to connect the dots from the other threads, like I said I'm having knowledge overload and brain block, I'm unable to relate examples to my setup.

Many, many thanks in advance!

Paul.

[dansmith65]

It looks like your appointment layout is based on the Client table, which means you shouldn't select a client from a value list - you should perform a find to find that client. You shouldn't need any ClientFKID fields on that layout, because the ClientID field exists in the current layout's associated table.

I think you need to decouple Sales and Treatment tables if client's can pay for more than one treatment at a time, pre-purchase, etc. You can do this by adding a table between them that track's application of a payment. Although, this is only touching on the surface of the subject; you should really read up on some general accounting principals, and possibly look for a demo/sample/template FileMaker file that manages client's payments, debit balance, credit balance, etc., and either use this to learn from, or integrate it into your solution.

[Long_p1]

Hi Dan, thank you so much for taking the time to reply.

The appointment layout has the contact elements from the Clients table, the "notes" field from the treatment table.

As for the sales part, Its more of a way for me to just tracking income (plus vouchers i've giving out for charity events), I don't expect many advance or block booking to be honest. I'm be lucky to get unto 3/4 people a day in the near future. Just want to start as I mean to carry on.

I know this is basic stuff as I built a fairly complex DB with FM before but its just doing my brain in, i cant seem to hold a thought!!!

How in the appointment layout do a recall a record to pre populate the fields without creating a new record in the Client table?

Sorry what do you mean by decoupling.

So sorry for the basic nonsense, once I'm the hill i'll probably be ok!

[dansmith65]

As for the sales part, Its more of a way for me to just tracking income (plus vouchers i've giving out for charity events), I don't expect many advance or block booking to be honest.

It doesn't take many, it only takes one. In other words, right now it looks like a payment can only be applied to a single treatment - no exceptions. So, if you ever have a different scenario than that - you will need to modify your database.

How in the appointment layout do a recall a record to pre populate the fields without creating a new record in the Client table?

I don't think I understand your question. I think you may be asking how to perform a find, but I'm not sure.

Sorry what do you mean by decoupling.

Right now the Sales to Treatment table is a 1 to 1 relationship. It needs to be a one to many relationship.

[Long_p1]

Ok thanks.

Yes I think i need to perform a find.... I want to "find" the clients name and then have the other details fill the additional fields (Postcode, contact number etc) plus then I think need a portal to show previous Treatment notes.

I'll have a looking into performing Finds

It doesn't take many, it only takes one. In other words, right now it looks like a payment can only be applied to a single treatment - no exceptions. So, if you ever have a different scenario than that - you will need to modify your database.

Right now the Sales to Treatment table is a 1 to 1 relationship. It needs to be a one to many relationship.

Yes so currently Treatments & Sales have a 1 to 1 relationship but Clients to Sales is one many so is that the better relationship to make?