One DB + Multiple Projects + Contacts Relationship

[esegal]

I don't even know where to start searching for this information. I would appreciate being directed to an answer or any help in formulating my own.

Scenario:

I currently have multiple projects (I am a builder) and each project currently has a separate Project DB linked to a single master Contacts DB.

I would like to have one DB that all users can use, where the user selects which project they are woking on and all relationships change to the Project DB and all current and past information is available to the user. They can then switch to another project. This would be to avoid cross contamination of DB's. I also have auto-enter text fields with the project's name, location, and number which would have to change on all the records (cover pages, etc.).

The main reason for this is that as I upgrade features or appearance, I don't want to have to change all the DB's.

Thanks in advance.

[Ender]

If you have a field "Project Type", you can use that to determine which layout to use for each record. A search of open projects might pull up a project list with several different types of projects. Clicking a detail button for a project can then call a script that goes to the correct layout for that project.

Most relationships are probably the same for different project types, but if there are differences, they can be dealt with by using the Project Type as part of the relationship key.

An option: If there is information that is the same for all projects of each project type, you should make a Project Type file that holds records for each project type and their fields. For example, if each project type uses their own return FAX number, you can have that as a field in Project Type.

[esegal]

Ender,

Each project DB is exactly the same with the same relationship to the contacts DB. I want another DB to act only as a GUI (Graphical User Interface) wheras when my employee selects which project they want to work on, all relationships will refer to the related projects DB so it is virtually a remote DB. The GUI would store no info per se, but all info entered in the GUI would go to the related record in the projects DB.

I'm sure there is a clearer language, but I don't yet know what it is. I guess I essentially want to flag every record with the Projects name, like a Contact ID number. This way when I select that project I can do a find and isolate only records from that project. However(!), I don't want all my projects in the same DB file as it will get too large and the damage control factor is higher. I prefer each project have a separate data file.

Does this make any sense?

[Ender]

If you're not looking to merge your multiple project files, then I guess I don't see a clean way to do what you want. Maybe someone else can chime in.

I still think combining them is probably best. If we could we hear more about your concerns with merging, we may be able to help with solutions. How large are these project files (total number of records)? Is this a network solution with all project files hosted by the same machine or are they distributed somehow? How do you have access privileges set up?

[esegal]

Since you put it that way, I would consider merging the files ionto one. I simply want to be able to keep the projects information separate, so no one can potentially input data into the wrong project, or so that there is no possibility to "show all records" except by the administrator, me.

I will have upwards of 30 to 40 projects, each growing to over a thousand records a piece.

I appreciate your help.

[Ender]

If the fields for the different projects are mostly the same, then merging them will be pretty straight forward. The tricky part may be designing the interface so that users only have access to view or edit those projects they are assigned to.

This could be done by relying on a login system, where everyone logs in with their own username and/or password. The system then know who they are and can limit or allow access to different layouts or records. You can then easily show a list of projects for that user, and they would only see what you want them to see; they might have View access to Closed projects and Edit access to Open projects they are involved in.

Security with FM6's built in access priveleges can be pretty clumsy to set up for many users (you have to set up passwords and access priveleges in each file). Another way is a custom login system, this allows centralized management of accounts, but has security holes. You might also look at using a security plug-in. In FM7, this is all pretty easy to set up with the built in security (centralized management and easy to see who the current user is.)

[esegal]

Thanks. That sounds useful. I'm not so concerned with security as I am with people not paying attention and inputting data into the wrong record. It's more a foolproof system. I may make a script whereas a user can switch between projects at any time. I think the way I have it set up now it will require heavy mods to fields, etc. At least I'll only have to do it once, solving half my initial problem.