3 Replies Latest reply on Mar 31, 2017 1:00 PM by philmodjunk

    Clarification on moving a record to another table

    BERGSTEN

      Hello,

       

      I have an inventory database (Inventory_Main), with say, 8k records. I want to move all records with Status = "SOLD" to our Sold table (Inventory_Sold), which has say, 1k records.

      Screen Shot 2017-03-31 at 3.40.11 PM.png

      Would I create a scripted button to do this so I could hit it each time I sell an item out of our inventory?

       

      I've found these scripts but am unsure where/how to apply them. It looks like this is a one-off script, which is good for the 58 records that currently need to be moved. After that, I need the function to be done in one click as needed (hence the idea of a button)

       

      I'm also confused by $path. I don't know how to set that part up (aka how to tell it to move it to the inventory sold table). How do I figure out what the path I need is? Thanks!

       

      SAME DATABASE:

      Enter find mode

      set field status::personal = "terminated"

      perform find

      set variable $path = "file:" & get(temporaryPath) & "temp.fmp12"

      export records as filemaker file to $path

      delete found records

      go to layout for new table

      import records from file $path

       

      DIFFERENT DATABASE:

      Enter find mode

      set field status::personal = "terminated"

      perform find

      set variable $path = "file:" & get(temporaryPath) & "temp.fmp12"

      export records as filemaker file to $path

      delete found records

      open file (second file)

      perform script (second file import)

            go to layout (new table)

            set variable $path = "file:" & get(temporaryPath) & "temp.fmp12"

            Import records (from $path)

      Close file (second file, to return to first, optional)

       

        • 1. Re: Clarification on moving a record to another table
          philmodjunk

          There is no need to export and then import. You can just import from one table to another Whether tables are in the same file or not.

           

          But the best option is not to put this data in a separate table at all. Just use a field in the record to show whether an item is sold.

          • 2. Re: Clarification on moving a record to another table
            BERGSTEN

            Hey there,

             

            My superior wants to keep all sold items separate, a "cold archive" as he would call it. He doesn't want the sold mixed in with the active inventory.  As it stands we do have a field that lets us know an item is sold, which turns the serial number red etc. so our employees know that item is no longer active.

             

            I'd prefer to leave it as it is- but he insists this is "cleaner".

             

            So I can use a simple import script for these?

             

            Thanks!

            • 3. Re: Clarification on moving a record to another table
              philmodjunk

              As I answered before, yes. It's just that your scripts are needlessly complex.

               

              And unless your boss is a developer and snoops under the hood to see how you did it. You can design the interface and relationships used such that even though the records are all in one table, Sold and unsold items don't appear at the same time on any given layout. Whether that's a good idea in terms of having a good working relationship with your boss is a decision that you have to make.

               

              All your scripts really need do is:

              Isolate the record in a found set of that one record--that's what the find does in the scripts that you found, but a single find matching records step that specifies the record's primary key is a one step way to do the same thing.

              Import records from Table A to Table B.

              Delete the record from Table A

               

              If the two tables are not in the same table, you'd need to add an external data source reference to the second file and an occurrence of Table B that refers to table B of that file to your relationships graph. But the script would look and work exactly the same once you've done that.