12 Replies Latest reply on Jan 25, 2010 11:31 AM by philmodjunk

    Copy Records with Portals

    Miguel

      Title

      Copy Records with Portals

      Post

      Filemaker Pro Advanced and Server 10 advanced

       

      We keep records of hard drives with information about projects that need to be processed (Convert TIFF images to JP2 and JPG) We have a filemaker database with three tables, MDO inventory, Project_Settings and DVD_settings. There's a portal in MDO Inventory that has info from the other two tables for the hard drives. Each hard drive has a unique MDO number, i.e, MDO-00001397. Sometimes in order to keep backups of the hard drives, we need to duplicate a record with portals and everything, except for its MDO number That needs to be different. The way I set the MDO numbers for the database is by printing them before hand, and sticking them on the case before the record is created. I've tried duplicating the record via script but I usually end up with major problems and 25000 records crated on project_settings or DVD_settings. Most of the scripts I've found online address the issue but when I change the temporary MDO number to the one on the case everything on the portal goes away.

      Help, please.

      Thanks,

      Miguel

        • 1. Re: Copy Records with Portals
          philmodjunk
            

          The portal records should go away in this situation as they are "owned" by the original record--not the new one you've just created by duplicating it.

           

          The trick is to duplicate your portal's records first, assigning them the new ID number. Then you duplicate the parent record and give the parent record the same new ID number.

           

          You can set it up this way to start:

           

          Define a global text field, gNewNumb and place it on your layout where you see the parent record you might want to duplicate. You'll enter a new number in this field and then trigger a script to duplicate the current record and it's related portal records. I'll call your two tables "Main" and "PortalRecs" for this example. You'll need to substitute your own names where appropriate

           

          If [Not IsEmpty(gNewNumb) /* make sure a number was first entered in this field */]

             #duplicate any portal records

             IF [count (PortalRecs::MDO) > 0 /* there are portal records to duplicate */]

                 Go to Related Record [ From table: “PortalRecs”; Using layout: “PortalRecs” (PortalRecs) ] [ Show only related records ]

                 Go to Record/Request/Page [ First ]

                 Loop

                    Duplicate Record/Request

                    Set Field [ PortalRecs::MDO; Main::gNewNumb ]

                    Omit Record

                    Go to Record/Request/Page [ First ]

                    Omit Record

                    Exit Loop If [ Get ( FoundCount ) = 0 /* All records have been duplicated */ ]

                 End Loop

                 #Duplicate Parent Record

                 Go to Layout [ original layout ]

                 Duplicate Record/Request

                 Set Field [Main::MDO ; Main::gNewNumb ]

          End IF

           

          Note: The brackets and parameters for Go To Related record will look different in your script editor as I copied that portion of this script from a database design report which formats this line a bit differently.

           

          Also: Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:

          The Complete Go To Related Record

          • 2. Re: Copy Records with Portals
            ninja
              

            Howdy Phil,

             

            I use a variant of that script that works well.

             

            Since my parent table uses an autoenter serialID, I:

            trap the parent recordID, $oldID

            create a new record,

            trap the new ID, $newID

            go back to the record to be copied (find IDfield=$oldID),

            GTRR,

            and use your loop to copy the portal records and setfield their ID# to $newID

             

            Same concept as yours, but keeps autoentered serial#s clean.

            • 3. Re: Copy Records with Portals
              Miguel
                 Thanks, guys. I'll give it another try and will post the results soon.
              • 4. Re: Copy Records with Portals
                philmodjunk
                  

                Ninja,

                 

                That's exactly how the real script from which I adapted this does it. Since I couldn't tell if the MDO number was human generated (copied from a label?) or auto-generated, I used a sufficiently generic approach for it to work either way. I'd definitely use your approach if at all possible as asking humans to type in key values is a recipe for trouble.

                 

                If the MDO number is "human entered", I'd modify such a database to use an auto-entered serial number for my relationships and keep the MDO as simply a label so users can use it to find records and such.

                 

                • 5. Re: Copy Records with Portals
                  Miguel
                     The MDO number is a manually generated barcode. I print batches of 100 or 200 at the time, the user grabs the barcode and scans it minimizing human errors. I've been thinking about autogenerate MDO numbers but since I'm fairly new to the world of databases, I've decided to do one thing at the time.
                  • 6. Re: Copy Records with Portals
                    philmodjunk
                      

                    One thing at a time is definitely a good idea. :smileywink:

                     

                    If you haven't already figured this out, you can place your cursor in the global field and then use the barcode scanner to enter the scanned MDO number. You can even program most scanners to append a "postamble" onto the end of the scan that appends an enter, return or tab character to the end of the scan to exit the scanned field. Then you can use the OnObjectExit script trigger for this field to trigger your duplicate records script.

                    • 7. Re: Copy Records with Portals
                      ELBerraco
                        

                      Phil,

                      It works!

                      First, I had to create a new forum user ID because the one I had said that the email couldn't be found. I tried resetting the password and that didn't work either.

                      Anyway, the only thing I changed on the script was "Match found set" in order to get everything on the new record, other than that, it works perfectly!

                       

                      These forums are incredibly awesome!!! 

                       

                      I don't know if I'll ever get access to the other account, and this is a dumb question, Is it possible you can mark your answer as "Solved?"

                      Thanks,

                      Miguel 

                      • 8. Re: Copy Records with Portals
                        ninja
                          

                        Howdy EL Berraco,

                         

                        Typically only the original poster can mark a thread as solved.  But perhaps Phil can do it as Community Leader?

                        • 9. Re: Copy Records with Portals
                          Miguel
                            

                          Well, it looks like I got my forum account back . I do have a question, though, the way I have MDO_ID is, "MDO ID    text     Indexed, Auto-enter calculation replaces existing value, requiered value, Unique, range, Maximum, Allow Override, Message" I get the meesage every time I run the script because one of the options didn't apply, why is this happening? Both tables have a relationship as MDO(Main)=MDO_ID(Projects) Allow creation of records and delete records on Projects table are checked.

                           

                          Also, the more I look at the database the more I want to come up with ato-generated MDO numbers.

                          What would be the best way to create an Auto-serial number keeping in mind that I don't want to have missing MDO numbers, i.e, user a creates new record. Filemaker assigns MDO-00001999 for this new record. User b creates new record and gets MDO-00002000. User a decides to delete MDO-00001999, can I still use that number?

                           

                          As far as creating labels for them, we have a Zebra printer that can be installed in a center location and print the labels from either Filemaker or the Zebra utilities. 

                          Thanks again, guys!!

                          • 10. Re: Copy Records with Portals
                            philmodjunk
                              

                            ...can I still use that number?

                            Not something I'd recommend, though I understand that for auditing purposes, sometimes you have to do this. If you put a Unique Value validation on the field to help protect yourself, you would be able to type in the serial number of a deleted record.

                             

                            A better approach: Put your Label serial number in a separate table and use a hidden serial number (where you won't care about gaps in the sequence) to link to this "label" record. If you delete the item's record, it's "label" record will remain and you can select it to re-use. (I'd also use this internal, hidden serial number for all your other links.)

                             

                            Ninja,

                             

                            I have a few extra options here, but marking another member's post as "solved" is not one of them.

                            • 11. Re: Copy Records with Portals
                              Miguel
                                

                              Phil,

                              What kind of relationship would I need between the new label table and the other ones?

                              Thanks!!

                              • 12. Re: Copy Records with Portals
                                philmodjunk
                                  

                                You'll have to look at all your tables to make sure it works for you but you should only need one new table with one new relationship in most cases. You may not have realized this, but you can reference data in tables "more than one table occurrence away" in most cases if you aren't using the X operator and the in between table (your main table in this case) always has a record to "bridge the gap".

                                 

                                In your case, it wouldn't make sense to have other related tables without a matching main table, so this shouldn't be a problem.

                                 

                                Simply add one new table with two fields:

                                 

                                InternalSerial (number)

                                MDO Serial (auto-entered serial with MDO prefix)  //or you might use a calculation to combine a simple serial number field with your text and leading zeroes.

                                 

                                New relationship:

                                Main::InternalSerial = LabelTable::InternalSerial (enable "allow creation..." for LabelTable records.)

                                 

                                The main catch is you have to trigger the creation of the MOD serial number with a script like:

                                 

                                Set Field [LabelTable::Internalserial; Main::internalSerial]