Making a client database system

[topaznz]

As the Administrator of an alcohol and drug counselling centre, I have been asked to develop a client database which contains

"Client record" = general client info.

"Case file" = This will be accessible via the client record and act almost like an invoice header.

"Session lines" = This will relate to sessions within a case and will act almost like the cost lines of an invoice.

This system will be used by about 10 people, will it pay to make separate tables for each worker for this own "Client records"? Confidentiality is a must for this type of work. I'm guessing if I create seperate tables for each workers "Client records" I can create accounts which will allow only related staff to access their own client files.

Apologies if this message is vague or furthermore if this message is in the wrong place.

I look forward to any replies.

Regards, Topaz

[Fenton]

No. You can control who can view "their own record" by using a combination of 2 things: a text field, with the Auto-enter option to enter, Creation [x] Account name, and by restricting the "record-level access," in Accounts & Privileges.

Let's call the text field "CreatedBy". Also check [x] Prohibit modification

Then go to File, Define, Accounts & Privileges; then Privilege Sets. Create a "users" privilege set (many accounts can share the same privilege set). Click and release the "Records:" pop-up. This shows you the tables for the file, and the options; View, Create, Delete, Field Access. Lots of stuff :-]

In the View pop-up, enter a calculation:

Get ( AccountName ) = CreatedBy

While you're at it, also set the Edit options to the same (I guess redundant in this case, but I'd do it anyway). Also the Delete. Maybe you don't want users to be able to delete. Probably best to leave the Field Access alone for now.

When you've set the correct options for all tables, say OK to close that window. Then look at the other choices: Layouts, Value Lists, Scripts. Then on the right, Printing, Exporting (maybe not), etc.. At the bottom right is Menu commands; should be Editing only or Minimal, but not probably not All (unless you restrict things otherwise, with Custom Menus, etc.).

You will also want to script navigation, etc., as if none of the above is in place. Because now a plain Show All Records command is going to show a lot of records with <> in the fields. Good security, but ugly as heck.

In other words, there's a whole little world of FileMaker security. You can finely control everything about it. Steven H. Blackwell, fondly known as Old Advance Man here (with recent posts), has written a whole little book on it; which I haven't read, but probably should; it would likely have saved me a few scraped knuckles :-]

[Fenton]

I should also say that by "No," I meant do not create a table for each user worker. You may likely want to create one table for Workers however, with a record for each person. This is often a convenient way to get started with navigation in a restricted solution, and can also be used to script account creation/deletion in a multi-file solution. Yours is almost certainly a 1 file solution however; 1 file is easier with the security setup.

I must also say that, in my opinion, your organization is expecting a lot from a beginning developer, to create an easy-to-use yet highly secure solution. They should give you the time to do it. Fortunately it sounds like a fairly simple structure.

The tricky part is setting up the restrictions (as I mentioned; not all that tricky), and getting the person onto their own records at startup. A simple Find on CreatedBy will do that. FileMaker handles restricting their record sets automatically during Finds. Only Show All Records must be bypassed, with a different Find.

[topaznz]

Thanks for your reply Fenton. I've seen quite a few of your posts around and found them helpful as I did for your reply to my question.

My trial ran out on my FileMaker so was waiting for my shipped copy to arrive. Now that it has I've built (what I hope) is the skeleton to my database.

Yes, the organisation should give me more time, but in reality this project must be finished by the 31 June 2007 or we risk some important contracts being axed due to insufficient client management systems.

So, I am going to at least give this a try.

There is no additional money available to either get a system professionally developed or training for FM; I even purchased the FM application with my wages.

Anyway, before I go any further, as well as implement your solution, I just want to make sure I’m on the right track in regards to setting up this database, or if I’ve created a sinking ship at this early stage!

I've seen people post examples of their work in zip files with Windows and Mac versions, how do I go about this?

Many thanks Fenton,

Regards, Topaz

[topaznz]

Also, the attached JPEG will give you an idea of what I'm trying to achieve.

Thanks

Topaz

[Fenton]

Your chart looks pretty good. What I wonder is who is going to be adding new users? Hopefully someone who has permission (privileges) to do so.

One other thing. Even though Reports tied directly to Cases seems the logical connection, it is possible that the reports will actually take place on a Sessions layout. Because that is where the "many" records exist, and where the dates for sessions exist (if that matters, likely). All other info is also available there, as either related fields (client name), or as direct lookups.

If you do a lot of reporting summaries by age, gender, ethnicity, then you might want to look up that data into the sessions table, so it is local. It's not very large chunks of data. Not really something to worry about either way.

[topaznz]

I will be the only person to have administrative rights, so new accounts can only be created by myself. All staff will have access to Case records that contain their Staff ID (hopefully I've got the right idea).

Yes, I also thought about the connection between reporting, Cases and Sessions. For our reporting methods and the information we require, that information is in Cases. We are only funded per case and not per session. Once I get the hang of all of this, I will most likely create reports for sessions so we can report to the Board and funders of the actual workload we carry out rather than just reporting to their specifications.

One present issue I have at the moment which is kind of holding up this project is:

Once the user is at the 'Logged in user's switchboard" I want to make a button which will create a case related to that staff member, and so on creating a button on the Case file that will create a Session related with the Case file. I've been trial-an-error'ing till the early hours and just can't seem to find the right ingredients.

So the user is logged into their switchboard >> They click “New Case” >> The Case layout is opened in a new window >> The Staff ID is automatically filled in from the staff members switchboard >> The user clicks “New Session” and in table-form I need the Case ID to be automatically inserted upon creating the session (I need to filter these sessions to relate only to the case in question).

Sorry if I’ve completely confused you. I was trying to look for an open-source invoice system for FM, as I think it uses the kind of framework I need to learn. The little knowledge I do have of FM is from picking apart open-source solutions I’ve come across, have not yet found an open-source invoice file though.

[Fenton]

Basically you just need to pass the relevant parent ID to the child table. You can do this automatically in a portal, with "Allow creation of related records." That is the simplest, but not usually the best method. Because it requires that you be able to see or reach the last row of the portal; which becomes increasingly awkward as rows increase.

The other method is to script the creation with a button, as you say. You can set the parent ID into a script Variable (a script step, near the top). Then go to the child record, create a record, use Set Field [ parent Id; $variable with id ]. Set whatever else you want, Commit Record [ no dialog) ], Go to layout [ original ].

To return focus to INSIDE the portal add steps:

Go to Field [ portal relationship::field in portal ]

Go to Row [ either 1st or last ], depending on whether you sorted it descending by timestamp (good idea with lots of portal rows)

Several other child fields would populated by Auto-enter options. Timestamp [ creation TS ], Account [ Account name ]. The latter is important for filtering record-level access.

Another important user interface question is whether people need to see the existing Cases and Sessions BEFORE creating a new one. Would they know otherwise whether it was already there or not? In most cases I recommend putting "New" buttons only on layouts where they can see: 1. What new thing am I creating?, and 2. Is it already here?

[topaznz]

Slowly I'm getting there. Now I have a database which asks for you to log on and only shows you client records relating to the Staff ID; only problem with that is it shows that other non-related records exist and plaster to them, is there any way to filter these out?.

I have created a button in the Client Profile which takes you to a client-related Activity File and a portal for Sessions underneath; is there a way to script a button which will create a new row in the portal to add records within the Activity File.

Note: Activity File is the new name for Case File after consulting with staff requirements.

I've purchased a book called "FileMaker Pro 8.5 Bible"; hopefully it will help get me right up to speed with FM so I don't come pestering the forums for every question I have, hehe.

Cheers Fenton

[mr_vodka]

Any found set will omit the records with

[Fenton]

Yes, John's method is easiest. Do a Find on either the account name field (or whatever you're using to tag their records); or just do a Find on a Constant=1 field; as he says, any Find.

Show All Records still bypasses this however. So create a script that Finds as above and label it "Show All", or "Show My Records" or something; or just don't put a Show All button at all.

[topaznz]

Right, well it’s looking pretty good. I've taken all your ideas and implemented them and the database is working great. I have not yet implemented any reporting scripts as I have a few questions of how to go about that.

Once problem I'm expecting to face is multi-page documents. Or if a counsellor writes extensive notes in a scrolling text box, is there a way to take this information onto a "multi-page" layout? Or perhaps I could look around for conversation solution to work between FileMaker and Microsoft Word?

Thanks for all your help to get me this far!

[theShiba]

I am creating a similar database for a similar non-profit organization. Basically, a table with clients, and a separate table for each contact that a social worker has with that client. The two tables are related through a common Client ID number. However, I am having problems with the two separate tables. The user first enters client information, and a Client ID #. There is then a script to create a "contact" for this client. The user then enters the Client ID in this table, and relevant information for the contact.

The problem is that Client IDs are getting changed randomly by the program. For instance, client ID SD4 will suddenly change to LS4 once an LS4 is created by another user. Does that make sense? I also have the problem that creating a child record will sometimes create a new blank parent record.

Any ideas??? Thanks!