8 Replies Latest reply on Jul 10, 2012 10:47 AM by MoOsman

    Delete record script

    MoOsman

      Title

      Delete record script

      Post

      Hi, 

      So I'm a beginner in File Maker, but thanks to these online forum I'm advancing. My issue right now is finding a script to delete SOME fields within a record and update them from an Excel sheet. The current script I have deletes everything and inster the updates. 

      Is there a way to say delete all records except "Person Photo and Notes" for example? Please help (^^,)

      Below is my current script

       

      if [$$fmauto_1065090 <> 1 or Get ( ScriptParameter )]

      Go to Layour ["Complete Details" (Details)]

      Freeze Window

      Show All Records

      Delete All Records [No dialog]

      Import Records [No dialog; Source: "fulldata.xlsx": Worksheet: "District 1": Add: Mac Roman]

      Set Variable [$$fmauto_1065090: Value:1]

      Go to Layour [original layout]

      End If

        • 1. Re: Delete record script
          philmodjunk

          You seem to be confusing "records" with "fields" in your post.

          Have you tried using an Import Records with the "update matching" option? That might make for a two line script to do this:

          Show All Records
          Import

          • 2. Re: Delete record script
            MoOsman

            I appreceate the fast respond! Yes, sorry. I did say I was a beginer. That being said, my excel file is updated often, so my original scrip alows me to link the file to my filemaker database. Allowing it to update to the content in excel everytime I run the scrips.

            BUT, i want to keep one or two fields as is without them being affected by the scrips. For example, I have a file container with a photo, and a notes section with some key notes. Is there a way to protect those two from being erased/deleted? 

            Thanks,

             

            • 3. Re: Delete record script
              philmodjunk

              The script I am suggesting meets each of those concerns though I'm not sure what you mean by "link the file to my filemaker database." as you  are copying data from it into your file--not linking to it.

              When you set up such an update import, you can specify which fields map to a column in your spreadsheet and which fields will not. Those that do not map to a column, will be left unchanged.

              The other option is to have two or more tables and include only the fields you want modified by this import in the table, putting the other fields in other, related tables. This way, the delete records action will not affect them.

              • 4. Re: Delete record script
                MoOsman

                Thanks for the support, I think that second option might do that job. I'll try placing those fileds in a seperate table. Just to clearify what I meant was that I want to link filemaker to excel using a "recurring import" so that all of the changes I make in excel will update in filemaker.

                -Thanks again (^^,)

                • 5. Re: Delete record script
                  philmodjunk

                  It's possible to script your own recurring import though it's harder to tell if the file was updated since the previous import.

                  Using the second option (and a recurring import was the possibility that prompted me to suggest it), you'll need some sort of value in your import file that uniquely identifies each record so that your data in related table(s) can consistently link to the correct imported data.

                  • 6. Re: Delete record script
                    MoOsman

                    I see, Here's what I just did: I added a "COPY" of my two fields before my original scrips, and a "PASTE" after the script. It worked perfect. Can't beleive I didnt think of it, but thanks for all of your suggestions. I'll use that when I start on another project from scratch.

                    • 7. Re: Delete record script
                      philmodjunk

                      Don't see how that could possibly work unless you are only importing a single row from your spread sheet.

                      Using the copy and paste script steps are rarely a good idea in FileMaker. Scripts that use them can mysteriously fail to work after editing a layout as they require the referenced field to be phyiscially present on a layout.

                      But most of all, they destroy any data the user may have copied to the clipboard prior to your script replacing it with other data when it peformed the copy action. This can be both confusing and annoying to your users.

                      Most of the time, you can avoid this issue by using  a combination of set variable and set field to move the data into a variable instead of copying to the clipboard and then into a field instead of pasting from the clipboard.

                      • 8. Re: Delete record script
                        MoOsman

                        This is so frustrating, it worked at first and now it doesn't. There's no easy short cut to this, unfortunatley.