-
1. Re: One DB related to multiple DBs
Is there a way to do it with a single portal that looks at all four tracking DBs and finds the related records?
Yes, but it requires pulling on the Job records from your 4 different departments into a single table. It is indeed possible for your 4 departments to access the same jobs table with portal filters, finds and security settings set up to limit each department's access to only the Job records for their department even though they are all accessing data from the same table.
-
2. Re: One DB related to multiple DBs
The problem I have there is that the 4 department trackers are separate files and have been pretty much been set up separately. Though they all have the fields I want to show in the portal, they are not necessarily named the same, and they have many other fields that are dept specific. As far as security I am only looking to view the data from the trackers in this portal, so would not allow editing or record create or delete through the portal, and the idea is to allow anyone with access to the Quotes DB to see the related data in the trackers.
Are you suggesting a separate table in the Quotes DB for the tracker data, then importing data from each of the four dept trackers?
-
3. Re: One DB related to multiple DBs
Best solution would be to merge all the data into a single table and then modify those 4 separate files to use the same merged table as an external data source.
You could import from the separate tables, but if you are able to do that, then taking that merged table and using it with each of the 4 separate files also makes sense and eliminates the issues of having to constantly import the data in order to keep the merge table up to date.
My reference to security settings is just to list it as one of the main tools for helping restrict users to a given subset of the total records in the database--in this case restricting a user's access to just the Job records for their department.
-
4. Re: One DB related to multiple DBs
So would I create a new table in the Quotes DB that has the common fields (QuoteID, Job#, cust name, etc), then change each of the individual tracking files to use that new table as the source for the common fields? i.e. for each department tracker change those fields to be
MasterTracker::Fieldname instead of DeptA:Fieldname?
Then the other fields that are unique to each Department's file would still be DeptA::Fieldname?
-
5. Re: One DB related to multiple DBs
You have two options and different experienced developers will recommend differently here.
Option 1:
Pull all the fields from all the different files into one table. For any given record, only the fields needed for that department would be used. This often requires less restructuring of your system to make it work as each seperate table can be replaced with a reference to the same common table.
Option 2:
Do as you describe and then link a "department specific" table in a one to one relationship to the new table. This avoids having all those "unused" fields and ultimately can look and function just like using the same table as in Option 1. It does, however, require adding a new Table Occurrence box to each file's Relationship graph as you now have two related tables taking the place of the original. It can, however, reduce the amount of updating you need to do on your layouts if you retain the current tables as your "department specific" tables.
Take your pick, the end result is nearly identical from a user's persective.
-
6. Re: One DB related to multiple DBs
Let me know if I am misreading here.
If I have the Quotes DB and four dept tracking DBs, call them DeptA, DeptB, etc
I set up a table in Quotes called MasterTrack, populate that with the fields I want to show (job#, dates in and out, customer name, part number, etc)
Then in each of the four tracking DBs set up a relationship to the MasterTrack table. For each instance of the common fields on the layouts, say Job#, I change it from DeptA::Job# to MasterTrack::Job#.
That way as a job is entered into the DeptA database those fields actually get entered into the MasterTrack table. The individual dept DBs just show those fields from the MasterTrack table, but keep their other unique fields.
-
-
8. Re: One DB related to multiple DBs
Hey Phil, thanks for all your help with these issues. Am coming up with a pretty tight system thanks to your help.