6 Replies Latest reply on May 18, 2017 9:06 AM by ukypayne

    Separating Surplus Records to a Different Table

    ukypayne

      I currently have a database to keep track of my organization's purchased items. Every year, we go through to check that the items still exist and add notes as to their condition, etc. We answer to the state so we have very specific surplus property rules to follow. After we send an item to surplus, we change the status in FM to "Surplussed". I would like the records that have been sent away to be moved into another table in the same database.

       

      Approximately 300 records (15% of the table) is items that are surplussed.

       

      What is the recommended way to migrate these items to a different table so we can still have the data, but not constantly have to filter our the 300 items? Each record currently has 55 fields, so I don't want to manually write a big script with variables unless it is the best practice method.

       

      Thanks to anyone who can give me some advice!

        • 1. Re: Separating Surplus Records to a Different Table
          philmodjunk

          I would like the records that have been sent away to be moved into another table in the same database.

          This can be done very easily with Import Records, but if you have less than 500 items, it seem pretty unnecessary to do.

          If an item is "surplussed" by mistake, for example, you have to move the record back again. It's normally simpler and more flexible to keep the records in the same table.

           

          And if you make any changes to the original table--say to add fields or modify a calculation, you have to make a matching update to the surplussed items table.

          but not constantly have to filter our the 300 items

          What kind of "filtering" do you have to do? This is something that can easily be automated such that you never see the items with a status of "surplus" unless to take specific steps to see them in your table.

          • 2. Re: Separating Surplus Records to a Different Table
            ukypayne

            During our yearly process, we have to run a number of filters to show only certain buildings, or rooms. Having to remind the people who are actually doing the process to remember to filter out the "surplussed" and "awaiting surplus" items is a slight hassle. I just wish the "show All" button would really just show me All except surplus.

             

            We are also about to go through a move and get a lot more items added to the database (a minimum of 4,000 new items), which will also lead to getting rid of a few hundred more.

            • 3. Re: Separating Surplus Records to a Different Table
              philmodjunk

              If you choose to do this, I've given you your answer. You can find all records of the specified status and then import records can copy the found set from the current table to your surplussed table in one batch operation. Then you delete the found set from the original table.

               

              But

               

              Having to remind the people who are actually doing the process to remember to filter out the "surplussed" and "awaiting surplus" items is a slight hassle. I just wish the "show All" button would really just show me All except surplus.

               

              You shouldn't need to do this. As I've stated before, you can automate the process so that this happens automatically.

              If you have FileMaker Advanced, you can modify the "Show all" menu option to perform a script that finds all records except those that you want to omit.

               

              You can set a script trigger on the layout that constrains the found set to omit these records when the layout returns to Browse mode. That way, when the user performs a find, any records found by them are then filtered to omit the surplussed items. The same script can also be performed when the layout is first entered.

               

              Portals, value lists, ExecuteSQL queries and relationships can all be updated to automatically exclude these items as well.

              • 4. Re: Separating Surplus Records to a Different Table
                ukypayne

                So I currently have a script trigger set OnLayoutEnter to run a script that omits records that have the Current Status = "Surplussed". That loads the records correctly (only showing the 1600 found), until I perform any type of search.

                • 5. Re: Separating Surplus Records to a Different Table
                  philmodjunk

                  Correct.

                   

                  See my previous reply on what do do for user performed finds so that they also omit these records.

                  • 6. Re: Separating Surplus Records to a Different Table
                    ukypayne

                    I must have it set wrong because it still shows the items that should not be listed. My script is currently set as

                     

                    Perform Find [RESTORE]

                         Omit Records where CurrentStatus: [Surplussed]

                     

                    Which option should I be changing for the proper constrain?