4 Replies Latest reply on Sep 25, 2013 8:23 AM by DranLang

    Importing Records

    DranLang

      Title

      Importing Records

      Post

           Hi everyone!  I'm currently working on a quick project for my boss, part of which  he wants to be able import records from excel to filemaker and be able assign a specific ID number for all the records that doesn't have a specified ID number.  How can I do that?  I can import records now form excel but unable to assign an ID number.  Here's how my boss wants it:

           In a layout, there is an empty field for ID number that he wants to assign to those records that will be imported and there is also a button that will trigger the script to import the file/records he wants to import from excel into FileMaker.  SO when he clicks the button, the number he entered in the ID field will be assign automatically to the records.

           How can I do that?  Thank you for the help!

        • 1. Re: Importing Records
          philmodjunk

               Give that empty field global storage so that this field stores the user's value once for all records in the table. Make this a different field from the actual ID field in the table.

               You then have two options,

               You can set up an auto-enter calculation on the table's ID field to copy this value from the global field. You can keep the "do not replace existing value" option selected so that only empty fields will get this value. Then, be sure to enable auto-enter field options during the import records. (There's a check box for selecting that option.)

               After the import, the imported records form the found set for your table. Use a script to constrain the found set to just those with empty ID fields, then use replace field contents to copy the contents of the global field into the ID field of those records.

          • 2. Re: Importing Records
            DranLang

                 How can I set my script to look for an empty ID fields?  I tried the script below but I'm not sure about it 'cause it didn't do what I thought it would, can you tell me what I need to change with my script?  I'm still new when it comes to scripting that's why I'm not sure about this script.  The script is I wrote is as follows:

                 Go to Layout["Import"(RawData)]

                 Set Variable [$$ID_Num; Value:Data::ID_LookUp]

                 Enter Find Mode [Restore]

                 Set Field [Data::ID#; $$ID_Num]

                 Replcae Field Contents [RawData::ID#; Data::ID_LookUp]

                 Commit Records/Request [No dialog]

            • 3. Re: Importing Records
              philmodjunk

                   Your script never performs a find. It just enters find mode.

                   I'd use this script:

                   Go to Layout
                   Enter Find Mode [] ---> no criteria stored, clear the pause check box
                   Set Field [RawData::ID# ; "="]
                   Set Error Capture [on]
                   Perform Find []
                   If [ Get (FoundCount ) //records with an empty ID field were found ]
                      Replace Field Contents [no dialog; RawData::ID# ; YourTable::GlobalFIeldWithUserEnteredID ]
                   End If

              • 4. Re: Importing Records
                DranLang

                     Thank you Phil! This worked perfectly, thanks again!