11 Replies Latest reply on Feb 3, 2010 9:58 AM by 86TurboZ

    Copy several related records



      Copy several related records


      I'm working on a modification to a work order system I wrote years ago.  The system is written in an older version of FMPro and all workstations are running Windows XP. I'm currently using FMPro version 6. There are 12 workstations and a server hosts the databases for all the users. Server is Server 2003 and FMPro Server version 6.


      I need to pull records from a 'table' or database which contains part numbers and quantities which relate to a model number and type of work.  For example if the model number needs to be overhauled then a list of parts are found which match the 'common' field Model#TypeOfWork which I create as a combined field of the to seperate fields. 


      I can locate the records I need easily enough. The problem now is that I need to 'copy' those to a seperate database which will be unique to the work order. So, in summary a new work order is created, a TypeOfWork is defined and a Model number. I then look up the parts I need based on those two fields. If I can then copy those records to a related database where those records will also have the work order as an additional field I can do what I need.  I also need those 'new' records to be able to be added to manually. Those added records will be common to the work order but will not effect the original 'master' database where they were originally copied from.


      I can't seem to be able to 'find' a data set and copy those records to a different database.  Any help would be greatly appreciated.

        • 1. Re: Copy several related records

          There may be a better way than copying entire records from one file to another, but to do what you request:


          Use go To Related records to isolate the related records in a found set.

          In the file to recieve the imported records, use import records to import this found set of records into your other file.

          After import, these records will make up a new found set and you can use Replace field contents or other script steps to modify the records (to assign a value to link them to your work order record, for example).

          • 2. Re: Copy several related records

            Thanks for the reply. 


            So it's possible using the guidelines you describe above to use a script and automate the process.  For example, when a user starts a new work order and enters the model and type of work, that a new set of records can be created which 'relate' including the addition of the work order number.  From there they can use those records to edit or add or delete without effecting the original dataset where the records were copied from.


            Just to confirm, there is an 'import related records' option available in the version of FMPro 6?  I'll check for that, thanks.


            I'll play with this tonight and see if I can't figure something out.  Additionally, any more help would be appreciated.

            • 3. Re: Copy several related records

              Been using Import records since Filemaker 3 or thereabouts so I'm sure FMP 6 has it also. This is one to test only after making back up copies so you can through out your current file and try again if your import doesn't work as expected (there's a lot of settings to learn about).


              Also, try to set up your import to use the matching fields option as it is much safer.


              (This whole approach only makes sense if you are pulling in multiple records. There are simpler approaches to bring in the data from a single matching record.)

              • 4. Re: Copy several related records

                Agreed, I'm working with backups so  can 'master' the necessary script steps. When working with a manual process, I didn't notice the 'import related records' option.  However I'll look not at the script and try to work it out totally with script.


                Just to review...


                I'll create or setup a database which will relate to the work order database using the model and type of work. Each record will have a field which is the combination of these two so I only have to worry about that one field in the search.  Then when I find all those which match they will be the 'related set'. Then I will perform a script step which imports the found set of related records.  These  must also have a field added to the found set which represents the work order number so these could be modified as necessary for only that work order.


                I think my head is working itself around the solution now with only one hole left.  The addition of the field (WO#) and then populating it with the correct value. 

                • 5. Re: Copy several related records
                     You can use Replace Field contents to update a field in all the records of your newly imported found set to add the work order number. This is another high power tool to research first, make a back up and then test. You can store your current work order in a global field to make it's contents available to your replace field contents script step.
                  • 6. Re: Copy several related records

                    So if I can isolate the imported records into a found set I could 'replace' the value of the empty field with the current work order number. I'll check into it.



                    Bob O'Neill

                    • 7. Re: Copy several related records
                         And immediately after the Import records the imported records are already in a found set ready for you to do this.
                      • 8. Re: Copy several related records
                           Thanks, I'll work on it tonight and see what I can come up with.
                        • 9. Re: Copy several related records
                             OK I've been successful importing related records to a new database. The problem I'm having now is with the 'global' variable. I'm having trouble understanding how I can reference the 'global' variable defined in one database from a script in another. It's not like 'Public' variables. If there is no relationship how do I reference the global variable defined? The new database needs to be related to the work order database and I've been doing this using the work order number.  The problem is now that the new database doesn't yet have the work order 'values' until I 'replace contents'.  Also, for some reason I'm having issues with the 'replace contents' with an error indicating that I'm trying to replace with invalid contents. I'll keep playing with that but if anyone has any hints I'd appreciate it.
                          • 10. Re: Copy several related records

                            I was forgetting that you're using filemaker 6 and this limits you to one table per file and (I think) this version requires a valid relationship before you can access the global field. (Note:  global variables are something else and they don't exist in your version.)


                            Define the following calculation field in both files (tables): MasterLink, and simply enter a 1 as the value. Now create a new relationship that links your files by these two fields. Since every record in both files has the same value, you'll always have a match. (That's how we did cartesian joins before we had the X operator in later versions.) Now you can use this relationship to access the number stored in the global field.

                            • 11. Re: Copy several related records

                              Well, I was able to import the found set and add the value of the work order to he work order field. Tonight I'll try to work the portal issue where a relationship will 'then' exist between the work order number and the new database which has the 'new' records and 'new' work order value. This may result in a circular but if I use a script to refresh the portal then it 'should' work. 




                              It turns out that it works the way I wanted it to. I created a portal which is based on the relationship of the 'new' database with it's imported records where the new 'field' value of Work Order matches the work order field value of the work order itself. The portal doesn't populate until the work order field is populated with the right value.  The problem I'll have now is when and if additional records need to be added by the user, I may have to execute another script to poplulate the 'new' records with the work order number so they will appear next time the same work order is referenced. I'll let you know what  come up with.


                              Thanks for your help with this. It's been a long time since I messed with any real work on this system as FMPro 6 indicates.  I've been working with Visual Studio .NET an SQL. :)