My first inclination would be to set up an intermediate file that grabs the data straight from the external database, thus mirroring it, and in that file set up all the logic you originally set up in the external file. I'm not sure there's a strong reason for setting up as an separate file rather than just another table in your existing file, except perhaps for password issues regarding accessign the external file. The nice thing about doing things this way rather than using an extract is that changes in the external table will be immediately reflected in your database.
Were you given a reason for why you have to make this change?
If you performed a find on the external data using a layout that references the external data source, you can get a found set of all the records that meet your criteria.
On the other hand, you can define your local table with all the needed calculations and set them up to reference the fields from the employee table in the external source via a one to one relationship. This produces what is functionally the same as your original table, but leaves the original employee table free of these added calculation fields.
Thanks William and Phil, for the reply.
As to why I need to make the change, he uses the database for other purposes and is not willing to add the relationships and fields that I added. He feels that the employees table is a data table and should not have any calculated fields. I've discussed it with him multliple times and ultimately have lost the argument, thus my post.
William: The external data file contains data that is always changing so I do not think duplicating the file would be an option. The external data source holds call evaluations, stats that would be updated daily and an employee list that changes weekly. Duplicating the file would make it only "live" through the date of duplication. I may be mistaken and wrong on this. Is there a way to constantly "mirror" the file?
Phil: I like your second option but am not too sure I understand. If I create a 1 to 1 relationship, would the data from the external data auto populate into the local table?
In essense, my goal, is to create a table that has only the employeeID from the Employees table. If, for instance, I only had employeeID in the local employees table, I could view, say, employeeEmail from the external source through a relationship with the external source. My original idea would be to do this with a script performed at startup and upon updating a record's "include" field.
Sorry, I obviously didn't say what I meant very clearly. My idea, and what I think Phil was suggesting with his last paragraph, is to have a separate table (perhaps in a separate file) YourTable where each field in the external table will be mirrored in YourTable. Let's say the External Table is called ExTable and it has fields FirstName and LastName. In your separate table you'll have a field FirstName = ExTable::FirstName and a field LastName = ExTable::LastName. Your Table will be linked to ExTable via a Key (allow creation of records in this table via this relationship). So, any time a change is made in ExTable, it should be reflected in YourTable. Then you set up the old calculations using YourTable rather than ExTable. Does that make more sense?
Now that I think of it, that way is a bit laborious and probably unecessary, since it competely duplicates ExTable's data in YourTable.
Instead, YourTable can just hold the relevant data and calculations. So, you can have YourTable include calculations that act on the external data; e.g., FullName = ExTable::FistaName & " " & ExTable::LastName. You could still create relationships between your tables and ExTable. I'm guessing that the sysop for ExTable doesn't want you to make change in ExTable, so make sure that the permission level that allows you to access ExTable doesn't include edit permission, or make sure any fields that might edit ExTable's data only permit Find access.
I wonder if your colleague really understands how Filemaker works. In many database systems you don't even have the option of setting up calculations fields as you define such entities in the query lanaguage that feeds data to a form (Think layout in filemaker terms). But in FileMaker, you often simply can't get the results you need without at least some calculation fields. That's due to the design decisions made over the years by FileMaker Inc. software engineers including the fact that we can't set up SQL queries as datasources for Filemaker tables.
That said, a one to one relationship between the external employee table and your table (no need to put it in a file all it's own, you can put it with your other tables in the file that you have control over.) is exactly what I had in mind. And William is gradually figuring out what I had in mind from the start.
You don't need to fully duplicate all the data fields, just reproduce your calculation fields in this new table and set them to reference the original data in the other fields/tables of your system via relationships defined in your file--including links to that employee table that you aren't being allowed to change.
Thanks guys. Yeah, Phil, I'm really not sure about the other guy either. He is above me on the totem pole though and I, unfortunately, can't do anything else. Overall, interaction with him is less than ideal.
Okay. So now the trick that I'm not sure of: what relationships and tables to create and how to populate the data. While waiting y'all's response, I came up with the following, please tell me if I am completely wrong or if there is a better way.
Upon launch of the file, I run the script "Create Local Employees". This:
- Goes to the LocalEmployees table and deletes all data there.
- Goes to ServerEmployees and performs a find for "Include=1", goes to first record.
- Loop: Copies EmployeeID into a variable.
- Goes to LocalEmployees, creates a new record and pastes EmployeeID into LocalEmployees::ID
- Go back to ServerEmployees, Go to next record, Exit after last record.
This works and populates the local table with data. The "include" field is not changed regularly but I will run the script when it is changed. I moved all all of my calculations into the localEmployees table. It is related to the server table through employee ID. So... If I'd like to display employee email next to my calculation, I can call LocalEmployees::Calc_1 next to ServerEmployees::Email.
Again, I feel there is a better way to do this and would welcome the suggestion.
Take a look at the Import Records script step. You can set it up to do an "import matching records/add new" import that only adds those employees that are new.
And you can check the "include" status of exising employees from the context of your local employee table.
Okay... I didn't even think about using the import option instead of the find/loop script. Is there an advantage of doing the import over my loop? I mean, I now have a functioning list of "Included" employeeIDs that has calculations in it and the script doesn't take but a millisecond to perform.
Apologies but I am unclear on your second statement. I believe you are stating that the LocalTable would need the "Include" field from the ServerTable. Is this was you were saying here?
If the script works and you don't see noticeable delays, there's no need to change it. I was thinking that the import records method would be more straight forward and possibly faster when working with large record sets.
I'm suggestig that it the value in this include field changes for an existing employee you can simply use the relationship between your employee table and the external employee table to find and omit that employee from your table without having a copy of the include field in your table.