7 Replies Latest reply on Sep 21, 2010 3:29 PM by philmodjunk

    Updating a file via a script

    BrianWilliams

      Title

      Updating a file via a script

      Post

      Hi all, I am in the process of creating a membership database.  We download membership applications and renewals from an online registration service in Excel format.  I would like to know if it is possible via script to update a table based on data imported from this spreadsheet.  I have read about settng up recurring imports, but I would like to verify the incomming data in a script, running exception reports afterward.  I'm not sure I want to directly update my main table from the import, so I'm thinking importing to a temp table.  The main table wil have an exclusive key - email address, so the script would have to read the temp table with the imported data and find the email address in the main table, and if found do updates to fields like expiration date, update other tables with the membership table key, send an email, etc.  If not found, it would need to create the membership record.  And along the way set a validation flag if the import data is somehow incorrect - amount paid does not equal the membership cost, etc.  I could set up reports to show the flagged records easily.  Can this type of processing be acomplished in FileMaker 11 Pro?  If so, can you point me to some referencees?  The book I purchased - "The Missing Manual" does not seem to cover this....

      Thank you

        • 1. Re: Updating a file via a script
          philmodjunk

          This seems like a fairly straight forward task to do in FileMaker Pro. What part of this process don't you know how to set up?

          Do you know how to import the data?

          Can you write scripts that loop through the imported data in the temp table and update the related records in your main table?

          • 2. Re: Updating a file via a script
            BrianWilliams

            Thanks for the reply...

            Although I am just now setting up the tables and defining the releationships, I'm fairly confident that I can use what I have read so far to create a recurring import to the temp file - hopefully this can be done via a button as people other than me will be doing it.

            At this point I do not know how to write a script that would loop through the temp table and update the main table.  I'm employed as a programmer so I just need to learn the syntax.

            From your answer I'm asuming this can be done - good news!

            • 3. Re: Updating a file via a script
              philmodjunk

              "Recurring Import" usuallyrefers to a FileMaker 11 feature that has its limitations. You'll probably want to use Import Records within a script to do what you want and it can definitely be done from a button click or a button click that opens up a a file dialog to select the file from which to import the data.

              Just to get you started, a typical script to loop through a found set of records follows this structure:

              Go To Record/Request/Page [First]
              Loop


                 //Do the stuff you need inside the loop here

                 Go To Record/Request/Page [next ; Exit after last]
              End Loop

              The exit after last parameter is what exits the loop after the script reaches the last record in the found set.

              • 4. Re: Updating a file via a script
                BrianWilliams

                OK that seems pretty straightforward.  As for inside the loop, my file is keyed by email address.  Do I have to loop through the membership or main file to find the correct record, or is there some kind of find operation?

                If I don't find the record, I will want to add a new one.  Is there an operation for update found record vs. add new record?  And is moving the data as simple as membership_table.field1 = temp_file.field1?

                • 5. Re: Updating a file via a script
                  philmodjunk

                  The key is in the relationship you set up.

                  Relate your two tables like this:

                  MainTable::EmailAddress = TempTable::EmailAddress

                  enable "Allow creation of records via this relationship" for TempTable.

                  Inside your loop:

                  IsEmpty(MainTable::EmailAddress) will be true if no matching record exists in your main table.
                  Set Field [ MainTable::DataField1 ; TempTable::DataField1 ] will update DataField1 with newly imported data from the temp table

                  If no matching record exists, Set Field [MainTable::Anyfield ; Anyfield or value ] will create a new record in MainTable and automatically enter the email address from the temp table. This only works if you've enabled the "Allow creation..." option specified earlier in Manage | Database | Relationships.

                  • 6. Re: Updating a file via a script
                    BrianWilliams

                    Ok - I'll give it a shot...  On thing I didn't understand was " enable Allow creation of records via this relationship" for TempTable"  Wouldn't it be the other way around - create records in the Main table?

                    • 7. Re: Updating a file via a script
                      philmodjunk

                      Yes, it would be for the Main table.