A Starter Template for a Relational DB

[kennedy]

[ NOTE: updated attachment is a few posts down ]

Given FMForums has given me so much help, I thought I'd try to give back by contributing the starter template that I've built based on all the great advice given here. The About text in the database is copied below.

Since I'd hate for someone to start with this and suffer from my mistakes, I'd greatly appreciate it if you smart guys could look the template over and tell me what you'd do different, or what things I've done wrong, or what things will be maintenance headaches, etc. I still have a few more days of work to do on it, incorporating a user login system, before I start replicating it to the dozen or so files that will make up my relational sysem. So, I'll definitely incorporate suggestions you provide before then.

Maybe with your help, this can become a great starter template for others fortunate enough to find FM Forums like I was.

Thanks, Brian

Description

This template is designed to serve as a foundation and starting point for multi-file relational databases.

There are a lot of basic things that FileMaker doesn

EBFits.zip

[Kurt Knippel]

The file attachment does not contain anything. Perhaps it is too big to be attached to the forums. What size is it?

[kennedy]

The file attachment does not contain anything. Perhaps it is too big to be attached to the forums. What size is it?

Darn! Its 180KB, so it should be fine. I just clicked on it and it downloaded it to my desktop and unzipped it... got a directory with three files, just as it should be.

What platform are you using? (I use OS X.1)

Anybody else try to download it? Did you get a directory of three files, or nothing?

[LaRetta]

It worked fine for me - received three files! Thanks much. I really appreciate you doing this for us! Win XP system.

[The Bridge]

The attachment downloaded jes' fine for me... looks good, although I haven't delved too deeply into it. Thank you for the special mention, Brian!

[Kurt Knippel]

Hmm, worked now. I guess you guys just needed to test it a little first!

[kennedy]

Well, I am not sure if anyone has any interest in this template, based on the complete lack of critiques (and there were lots of things to critique), but I promised...

Here's an updated version of the template with an adaptation of the Moyer/Bowers security system added to it. So, you FMP Experts out there, PLEASE tell me if this system is indeed secure... can it be easily broken into without physical access to the files?

I also made numerous other improvements based on recent threads. (I found a lot to critique about it myself.) Once again, I'd GREATLY APPRECIATE it if you FMP Experts could let me know everything that's bad about this template. All us FMP Mortals would benefit tremendously from having a solid template to base our work upon and to learn from!! It still needs a bit of work in the Shutdown scripts, but that is all that I know is lacking.

For your convenience, I've included the "about.txt" contents below:

This template is designed to be the foundation for a multi-file relational database. Most of the files here are support files; the file Template should be replicated for each content file in your relational database. Log and Login would be the only other files normally viewed by users.

The features include: OS X Look, Configurable Tab Bar, Second level of views/layouts in Tab Bar, Status Area integrated into Tab Bar, Help/Message Area under Tab Bar, Find Mode support, Lightweight Print/Preview support, List view with one-click multi-column sort, Audit Trail/Log with per-record and overall views, User login system, Basic help system, and 14-digit quasi-unique IDs.

The only two files that you should be able to open directly are Begin (for normal users) and Admin (for database administrators). The latter is in a separate subdirectory because it should normally not even be accessible to users, for maximizing security.

All the files, except Admin, default to the normal user password, so that normal users are not bothered with the FMP passwords. Rather, they will be asked to enter their own username and password. Use "BrianK" and "123".

Thus, to administer the user accounts, open Admin and enter the master password ("master"). To modify the database files and layouts, open Begin with the option key held down and then enter the master password.

To truly be secure, this must be deployed such that hackers do NOT have direct access to the physical files. That means it should be deployed as a subdirectory in the FileMaker Server directory, which itself should be on a volume that is not accessible to users. FileMaker Server will then serve up the files securely via the TCP/IP network.

To the best of my knowledge, this template is secure with the following exceptions: the master and user passwords are the all too obvious "master" and "user". Change those. If you find security holes beyond that, PLEASE let me know at [email protected].

For credits, please read the About layout in the Globals file.

EBFits.zip

[BruceJ]

That is one totally awesome file. You've used almost every trick in the book. Imagine someday, when FileMaker will actually wake up and hard code many of those enhancements you've had to create work arounds for!

I noticed something on the List layout. The tiny numbers that change as you sort various colums (very cool by the way), "ID" and "Example Text" hace the same numbers in them. Maybe they're supposed to be that way.

Don't ya know it, as soon as you go and "idiot proff" something - they'll create a better idiot (enter Me)!

Looks good. I almost feel guilty using some of these pieces you've sweat blood and tears over, but hey - I'll get over it. Thanks, this is wonderfull!

[kennedy]

I noticed something on the List layout. The tiny numbers that change as you sort various colums (very cool by the way), "ID" and "Example Text" hace the same numbers in them. Maybe they're supposed to be that way.

Ooops... how'd that get there? Cut'n'paste mistake, I guess... I intentionally don't support sorting on ID's... that button on ID shouldn't be there... its a copy of the button on Example Text. Thanks for pointing that out.

I almost feel guilty using some of these pieces you've sweat blood and tears over, but hey - I'll get over it.

Don't... by using it, you're helping me test it! Besides, its not like I didn't build on the blood, sweat, and tears of others!

Enjoy!

[gwmbox]

Brian

I have been working through your template, in particular the logging section of it and I have trying to work out how the system gets the original text in the logs, the calculation is (for field LogNext)

 DateToText(Status(CurrentDate)) & "|" & TimeToText(Status(CurrentTime)) 

& "|" & G::User & "|" & Status(CurrentLayoutName) & "|" & If(Status(CurrentFieldName) = "",

"||", Status(CurrentFieldName) & "|" & GetField("LOG " & Status(CurrentFieldName))

& "|" & GetField("TEXT " & Status(CurrentFieldName))) & "

[kennedy]

"TEXT " is a string... it concatenates that with the base field name to form a new field name... then it gets the value of that field. Note that for each field named "Foo", I have a field named "LOG Foo" to hang onto the last value and a field "TEXT Foo" that converts "Foo" to text properly.

You could discard the TEXT Foo fields and instead just get the value of the raw field in the expression you quoted... but then when you looked at your log, things such as dates would show up as bizarre whole numbers. The TEXT fields are also useful (to me) for things such as formatting phone numbers with hyphens.

Hope that clarifies.

[Geeksharka]

Hi all,

The template sounds awesome, but I have a download error to report. Perhaps it's my machine.

When I download any attachment from FMForums, all I get on the desktop is a PHP file -- 'download.php'

This has happened with both attachments on this thread, and one on another (yesterday).

??

Any ideas, fixes. I'd love to see the latest attachment offered by Kennedy.

I'm using IE 5.2, Mac OSX (jaguar).

Thanks,

[kennedy]

Yeah, the forum's attachment mechanism is setup wrong... the PHP code gives you the file properly, but it names it wrong...it gives its own name (download.php) rather than the name of the attachment.

Just rename the attachment to download.zip and/or open it with WinZIP or StuffitExpander or whatever you use to expand zip files. Annoying, but easy to work-around.

[kennedy]

P.S. Make sure you download the later one, not the one from the first note in this thread... I see that more people have downloaded the original version than the later version... I wish the forum gave me a way to remove that first attachment.

[Geeksharka]

Thanks Kennedy. I got it and unzipped it.

[gwmbox]

Great - I think I have the gist of the TEXT field now thanks.

Now one more thing the login function it uses two built in FM passwords, with the default being 'user' how can I have it set up to have up to seven passwords (i.e. six levels of access and the admin password) and when the user logs in the system will deteremine which built in password to use?

Thanks

Greg

[kennedy]

That's not really how its designed to work. It uses the 'user' FM password to turn off all the menus and such built-in stuff... such that all functionality is provided explicitly. Then it implements its own fine-grained Privileges for each thing you want to control access to. For each such thing, you code the check into the command. In that way, you can control access to commands at whatever granularity you want. You can have commands that require dual privileges (AND), or you can have commands that OR privileges.

Without plugin, I don't think FMI provides enough interface to allow you to drive their password mechanism from your own authentication. If you want to use both, your user will have to provide both. I think the plugin option was discussed in another thread.

[gwmbox]

Ok thx for the response, shame it can't do what I want and that there is no way through FM itself to set the Fm built in password, but hey can't have everything - I'll try and find out what the plug in is and try to include that - again thx for your help.

[mse]

Could somebody of you, FM professionals, post here your comments on compliance of the template with the USFDA 21 CFR Part 11 document on electronic records and signatures ?

Thank you for your thoughts

[Sridhar Kodela]

I am a newbie to FM. Like it a whole lot compared to Access (my opinion ). I am having problem unassigning the Privilage. When I click on the arrow pointing left nothing happens. If I understand the code correctly clicking on the arrow should trigger the UnAssignPrivilage script which inturn Delete Portal Record. Am I missing something that is staring in my face ? Any help is much appreciated.

[stardust]

Nice job! I am also a FMP newbie. Thanks for saving me a ton of time and grief!

[kennedy]

[ What happened to the ability to edit Sample Files threads forever? ]

This thread should be considered closed, superceded by this thread:

"The Works" Multi-File Template

The downloads in this thread are older versions... the new one is much better.

Of particular interest are probably these additions:

* Print Preview floating control window (no need for built-in status area)

* Generic Notes mechanism (add any number of dated notes to any record)

* Vastly improved error handling in all the scripts (thanks, Ray / CobaltSky)

* Examples of startup and shutdown scripts

* Examples of a pair of relational files built this way

* Examples of import files that implement data validation in the process

Anyway, the latest and greatest is now in that new thread.

The attachments in this thread are of, at most, historical use.