10 Replies Latest reply on Apr 25, 2013 10:19 PM by philmodjunk

    Script to create a list



      Script to create a list


           Hello all,

           My database is a local database but it accesses data from an external data source from another FM database.  On that database, there is a table of Employees.  This table has employeeID, employeeName, employeeEmail, etc.  Additionally, there is a boolean field called "Include".

           Previously, I would extract metrics such as call time, call quality, etc.  These calculated fields were placed in the Employees table and a relationship was created from Employees to Stats.  The admin of the external data source now wants me to remove all logic from that table.

           To do this, I am thinking of creating a script that, when the local database is opened, it creates a local list of EmployeeIDs that is extracted from the external data source.  This list would need to only be the ones with "Include" = 1.  So, my question is, how do I create this script.  I know it would be a script that I would include in the file options upon launch.  I would also want it to clear out any existing local list.

           Any help is greatly appreciated and if there is a better way to remove the logic from the external data source, I'd be happy to hear it.  Thanks in advance.

        • 1. Re: Script to create a list

               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.

          • 2. Re: Script to create a list

                 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.

            • 3. Re: Script to create a list

                   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.

              • 4. Re: Script to create a list

                     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?

                • 5. Re: Script to create a list

                       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.

                  • 6. Re: Script to create a list

                         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. wink

                         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.

                    • 7. Re: Script to create a list

                           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.

                      • 8. Re: Script to create a list

                             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.

                        • 9. Re: Script to create a list

                               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?

                          • 10. Re: Script to create a list

                                 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.