10 Replies Latest reply on Oct 18, 2016 8:25 AM by navarro

    Controling ID during data import

    navarro

      Hello,

       

      When adding new records, the auto-enter option of the Import script step starts from 1 without taking into account the ID's of the target table. Is it possible to control it?

       

      For example, let's assume that:

       

      My target table file contains 100 records with ID's ranging from 1 to 100.

      My source table file contains 50 records with ID's ranging from 1 to 50.

       

      After adding new records with the Import script step, I get a table can has both the records of the target and source table but:

       

      The first 100 records, originally in the target table, have ID's ranging from 1 to 100.

      And, the following added records, coming form the source table, have either their original ID's (if the auto-enter option is unchecked) or they are given IDs raging from 1 to 50 (if the auto-enter option is checked).

       

      So, after importing the new records I get repeated ID's (1 to 50)

       

      Would it be possible to have the first 100 records from the target table have their original ID's (ranging from 1 to 100) and the newly added 50 records having ID's franging from 101 to 150?

       

      If the answer is yes, any ideas how to achieve this?

       

      Thanks

        • 1. Re: Controling ID during data import
          philmodjunk

          Actually, the auto-enter option won't replace the contents of your ID field if they are not empty and so you get the same ID's with either option.

           

          Three options come to mind:

          Instead of serial numbers, use a text field that auto-enters the calculation Get ( UUID ). This will produce an ID that is unique for both tables and no change of the field's value is needed.

           

          Clear the ID field in the source table before importing and then the auto-enter option can do it's thing. That doesn't sound like the safest option to me, but in theory at least, it should work.

           

          Immediately after import, your imported records form a found set and you can update the serial numbers as a batch operation. You can get the next serial ID, there's a function for that and you can use Replace Field Contents to both assign a serial number and also update the next value setting on the serial number field. This option should only be used if there is no chance that some other user might be creating records in the target table at the same time that you are importing records.

          • 2. Re: Controling ID during data import
            navarro

            Hello Phil,

             

            Many thanks for your clear, concise and useful answer.

             

            Glad to see that you are still around in the new forum.

             

            In the former forum you help me with many difficult questions.

            • 3. Re: Controling ID during data import
              navarro

              Phil,

               

              I have decided to follow your third recommendation because ID field is used in many relationships with other tables.

               

              Immediately after import, your imported records form a found set and you can update the serial numbers as a batch operation. You can get the next serial ID, there's a function for that and you can use Replace Field Contents to both assign a serial number and also update the next value setting on the serial number field. This option should only be used if there is no chance that some other user might be creating records in the target table at the same time that you are importing records.

               

              After data import through scripting I can replace the serial ID manually using: Records --> Replace Field Contents:---Replace with serial numbers

               

              But,  after data import I would like to script the serial ID update too.

              My attempts have failed so far because I don't know how to emulate what the Replace with serial numbers do.

               

              The script that I have been trying is the following:

               

              # Total record count before importing

              Go to Layout [ “Medicine_Detail” (Medicine) ]

              Show All Records

              Set Variable [ $MedicineTotalRecordCount ; Value:Get ( TotalRecordCount ) ]

               

              #Import Medicine Records

              Import Records [ Source: “file:ns_medicine.fmp12” ; Target: “Medicine”; Method: Add; Character Set: “Windows ANSI” ; Field [ No dialog ]

              Pause/Resume Script [ Duration (seconds): 2 ]

               

              # Renumbering Medicine ID field

              Loop

              Replace Field Contents [ Medicine::__kp_Medicine ; Replace with calculation: $MedicineTotalRecordCount + 1 ] [ No dialog; Update Entry Options ]

              Go to Record/Request/Page

              [ Next; Exit after last ]

              End Loop

               

              The problem with this script is that the same number ($MedicineTotalRecordCount + 1) is added to all the imported values.

               

              Using the example shown in my first comment of this post, the 50 imported data have all 101 as serial number.

               

              I can see that the script step:

               

              Replace Field Contents [ Medicine::__kp_Medicine ; Replace with calculation: $MedicineTotalRecordCount + 1 ] [ No dialog; Update Entry Options ]

               

              Is not working like: Records --> Replace Field Contents:---Replace with serial numbers

               

              Any guidance about how should I modify my import script to update the serial numbers correctly (101, 102, 103, ...150)

               

              Thanks

              • 4. Re: Controling ID during data import
                beverly

                you cannot increment in the same step as it is applied:

                 

                Loop

                Replace... $MedicineTotalRecordCount ...

                Set Variable ( $MedicineTotalRecordCount ; $MedicineTotalRecordCount + 1 )

                ...

                End Loop

                • 5. Re: Controling ID during data import
                  navarro

                  Hello beverly,

                   

                  I did as you said (if I understood you right) but it didn't work.

                   

                  I also realized that my original script had an additional mistake. So I redid it, but unfortunately it is not working.

                   

                  This is my current Import script based on your suggestion and a change on my part.

                   

                  # Initial value for serial number based on total record count

                  Go to Layout [ “Medicine_Detail” (Medicine) ]

                  Show All Records

                  Set Variable [ $InitialValue; Value:Get ( TotalRecordCount ) + 1 ]

                   

                  #Import Medicine Records

                  Import Records [ Source: “file:ns_medicine.fmp12” ; Target: “Medicine”; Method: Add; Character Set: “Windows ANSI” ;

                  [ No dialog ]

                  Pause/Resume Script [ Duration (seconds): 1 ]

                   

                  # Renumber Medicine ID field

                  Loop

                  Replace Field Contents [ Medicine::__kp_Medicine ; Replace with calculation: $InitialValue ] [ No dialog ]

                  Set Variable [ $InitialValue; Value:$InitialValue + 1 ]

                  Go to Record/Request/Page [ Next; Exit after last ]

                  End Loop

                   

                  Any idea about what needs to be modified?

                   

                  Thanks

                  • 6. Re: Controling ID during data import
                    beverly

                    before your loop are you going to the first record?

                    Go to Record ( First )

                     

                    beverly

                    • 7. Re: Controling ID during data import
                      philmodjunk

                      If you are using replace field contents, you don't need a loop as this works over your whole found set.

                       

                      It's just one script step:

                       

                      Replace Field Contents

                       

                      Immediately after the import records step

                       

                      There's a serial number option inside the replace field contents step that does what you need to update the serial numbers and also the next serial number value.

                       

                      Use replace with serial numbers option and then specify "entry options".

                      • 9. Re: Controling ID during data import
                        navarro

                        Beverly,

                         

                        I added the Go to Record (First) before looping but it wasn't working either. I went through debugging and every looked okay until it reached the last record, then it added the last record ID to all the imported records. Can't understand why.

                         

                        Many thanks for answering my question.

                         

                        Phil,

                         

                        Can't believe how easy it was!.

                         

                        However, there is something strange under the hood.

                         

                        It is true that the 99 records from the source table file where added to 182 records of the target table and that each got the right ID value. But if you select the kp_Medicine (Medicine ID) field  and look inside the Auto-Enter tab, the next value is 183 instead of 282 (=182 + 99 +1).

                        So if after importing the data. I create manually a new record it gets the duplicated ID of 183, instead of th expected 282.

                         

                        It is true that I am using FileMaker Pro 12 Advanced. I will check with FileMaker Pro 15. But, isn't it a bug?

                         

                        Again, many thanks.

                        • 10. Re: Controling ID during data import
                          navarro

                          Phil,

                           

                          Never mind

                           

                          I forgot to check the update serial number in the entry options.

                           

                          Everthing working fine.

                           

                          Many thanks again.