12 Replies Latest reply on May 31, 2011 1:59 PM by philmodjunk

    Editing Field that determines relationship

    JasonGideon

      Title

      Editing Field that determines relationship

      Post

      I have a database with three tables. There is the master table, which is the only one that has a layout dedicated to itself. There are two other tables that are closely associated with the master table, designed to store a person's name, role for this record, and start/end times of that person's work. There are an unknown number of people associated with each

      The FileMaker help website went into great detail on the benefits of portals versus repeating fields. I'd like to run statistics about how much time people spend in a given role, and the average amount of time they spend. Therefore, I think a portal would better serve my needs. [http://help.filemaker.com/app/answers/detail/a_id/7462/kw/]

      There is one problem with using portals, though, that wouldn't be an issue with a repeating field. If I change the project name (which is unique, but can change when we resequence projects) all the records in the portal dissappear until it is changed back. Is there any way to make it to where when this field is changed in the master record, it updates all the records in the other two tables that had the old project id to reflect the new project name?

      I thought about creating another field that would contain a unique ID, but project records are merged every month into another database. Once this merger is complete, the project name never changes, so it seemed safter than risking an ID number that might get out of sync between the two databases.

      Thanks,

      Jason

        • 1. Re: Editing Field that determines relationship
          philmodjunk

          Use the ID number. Do not use the project names field. The ID number should be generated in the table where you first create a record for the project. This number should be meaningless to the user. As long as it is unique, it should always work for you. Unlike a project name field where you have the problems you are describing plus the challenge of ensuring they are unique and never entered incorrectly as simply typos can often cause problems. (Say you misspell a word in the project title and don't discover the error until after you have created several related records.)

          Don't see any way way that this number should get "out of sync" with the other database as this number should be generated in the original table only and never modified in the original table nor in the database to which you "merge" it each month.

          • 2. Re: Editing Field that determines relationship
            JasonGideon

            Thanks, Phil. My concern is that since there are multiple databases getting merged into one, that two databases will generate the same ID and create a problem. Is it possible to create some sort of unique hash from within FileMaker, or would the ID always need to be incrementing by 1? So if I have Manager1.fp7 and Manager2.fp7, and merge both of those into Company.fp7 would there be a collision between the ID fields? The key problem, it seems, is going to be keeping it unique. That does make much more sense though, if there is a workaround to this small problem.

            Jason

            • 3. Re: Editing Field that determines relationship
              philmodjunk

              Describe your multiple databases and why you have them. There are a number of simple methods to insure that each separate database has a uniqueID, but would like to know more before making any recommendations as I may recommend that you not have indvidual database files like this in the first place.

              • 4. Re: Editing Field that determines relationship
                mgores

                I have 4 different filemaker tracking files (for 4 different depts) that are related to a master tracking table in a fifth file by a MasterTrackID.  The MasterTrackID is generated in each of the dept tracking files.  In the serial number area for the field options I just prefixed the number with a unique dept identifier so the next value box says "US1" or "MAT1" and the increment by is 1.  So jobs entered into the US department have MasterTrackID's starting with US, mat department with MAT, etc.  It also makes searching the master table by dept easy as I can perform finds using the prefix.

                Since the DBs were inplace already when I did this, I had to add the MasterTrackID field to each of the DBs then use the "replace field contents/replace with serial number" to get the numbers entered for the existing records, then set the field definition to start counting from the last number +1.

                • 5. Re: Editing Field that determines relationship
                  philmodjunk

                  Since each department prefaces the serial numbers with a different block of text, you should not have any problems with your merged data having overlapping ID's. Just make sure that this field is of type text.

                  If your departments all used the same file for this use (manage security can be set up so that users from each department can only access their departments records), you would not have this issue to resolve in the first place.

                  If there are significant differences between the data recorded in each department, you can still avoid the need for a "merge" by designing your system so that users from each department create the new record in the Master File's table first then have their customized record linked to it via a relationship. Any data that you are now importing from the department tables would be directly entered via the relationship into the master table and only those departement specific details that are not merged into the master file would be entered into the department's related record.

                  • 6. Re: Editing Field that determines relationship
                    JasonGideon

                    Basically, we just copy/paste a database to any manager who needs it to track a project. There is only one project in a database, and they're merged back into a separate database on FileMaker Server every week or two. These computers are offline for security while a project is in progress--it's not until it's completed that we merge them back to the FileMaker server. Something like Mark's solution might work, but I worry that people would keep on copy/pasting the database, and the department identifier box might not get set.

                    Jason

                    • 7. Re: Editing Field that determines relationship
                      mgores

                      In my case I had the 4 separate files that had been independantly established by each department many years ago and I needed to merge certain information from each into a central location.  While I agree that it would be more efficient build it from the ground up they way you described, with each of the 4 DBs being as established and customized as they were, it seemed easier to go at it the other way.

                      In Jason's case it would depend on how the manager files are hosted.  If they are on the server, it would make more sense to have 1 file with separate layouts for each manager.  If the manager files are on thier computers and not always on the network, it would make more sense to have the prefaced IDs.

                      - just read Jason's response.

                      It sounds like you have a master file that crreates a project DB that is used offline, then eventually imported back to the master file.  In that case you should be able to have the master file create the serial number when you make the project DB, say ProjectID.  Then even if that project DB gets copied more than once they would all have the same serial number.  You could then have timestamp and "modified by" fields that get populated as the individual files are used.  When you import them back to the master file there would be multiple records with that Project that you can sort though by the timestamp and "modified by" fields.

                      • 8. Re: Editing Field that determines relationship
                        JasonGideon

                        The managers are offline, so I guess we could preface the ID with a     unique code. Each manager would just have to remember to code their  own    file before they enter any records. Another option would be to  use  the  username of the computer the file is on, since that should  always  be  unique. Is it possible to hash (md5?) the  filename of the  FileMaker   Database (/User/manager/Desktop/Projects.fp7 -->    4723c89308024bc7ced3bd19714e5de3) then append the unique code for the    record so it would look something like this for the ID:    4723c89308024bc7ced3bd19714e5de3-000001.

                        Another issue I thought of is that once records are moved into  FileMaker server, they're deleted from the manager's computer. Would  this cause the auto generated ID to reset? I remember back in the day,  Microsoft Access could generate a unique ID number that had a long  string of letters and numbers. This link (http://www.devx.com/dbzone/Article/10167/1954) is close to that. Does FileMaker generate a GUID?

                        Jason

                        • 9. Re: Editing Field that determines relationship
                          philmodjunk

                          The managers are offline, so I guess we could preface the ID with a unique code. Each manager would just have to remember to code their own file before they enter any records.

                          Absolutely not! That risks catastrophe should they forget to do so. As I understand it, they each have a different file, so you can set up each file to enter this form them automatically.

                          • 10. Re: Editing Field that determines relationship
                            JasonGideon

                            It's looking more and more like a GUID or UUID is going to be a much better option for what I'm trying to do. I've been looking at a few sites that seem to indicate that FileMaker will support this. For the number of records we have (~500/yr) the possibility of a collision is very small. I'm going to look into installing the free plugin, since it seems like FileMaker Pro Advanced is required to install a Custom Script. I'll post back how this turns out, but for this application, unique identifiers seem like the way to go.

                            http://www.briandunning.com/cf/969

                            http://360works.com/scriptmaster/

                            Jason

                            • 11. Re: Editing Field that determines relationship
                              mgores

                              There should be a ProjectID number generated from the server file and placed into a global field in the individual project file.  That number will be unique to that project.  When the project file is copied onto individual computers, users will create records within that file.  If each record has a "mod by" and timestamp" to identify them, they can be distinguished when the records are imported back to the server at the end of the project.  The individual files can then be deleted with out affecting anything and the next project file copied onto the individual computer will have a new projectID.

                              After the imports there could be multiple records in the main file with the same projectID, depending on how many managers copied that project file, created records in it, then imported them back to the main file.  These records can be sorted out using the "mod by" and timestamp fields.

                              • 12. Re: Editing Field that determines relationship
                                philmodjunk

                                Shouldn't be necessary.

                                What you have described, an auto-entered ID that combines a text prefix specified in the local file as an auto-entered value combined with a serial number will give you unique ID's for every one of your records in every one of your separate "off line" files where the records are first created.