11 Replies Latest reply on Mar 20, 2014 2:56 PM by SteveMartino

    Set Field or Replace Field Contents?

    SteveMartino

      Title

      Set Field or Replace Field Contents?

      Post

           Hello Forum, and as always Phil,

           What I would like to do is remove (Export) all my inactive Customers, and their related records to a different file.  I set up 2 scripts that appear to work fine.  One loops through all the related records, on all the tables,marking the Inactive field (checkbox) with an X.

           The second script does the same, but uses Replace Field Contents, avoiding all the loops.  Is one way better then the other? Could one way be problematic?  

           Any thoughts, suggestions are always welcomed and appreciated

           Thanks

           Steve

           Here's the Replace Field Contents Script 

      InactiveScript.jpg

        • 1. Re: Set Field or Replace Field Contents?
          philmodjunk

               I would use import records instead of either method.

          • 2. Re: Set Field or Replace Field Contents?
            SteveMartino

                 Thanks for your response Phil.  I don't quite understand how I would do that. 

                 Do I clone my DB, change the name to Inactive. 

                 Then on my working DB, bring up found a found set of records for one table.  Back to the Inactive db, import that table of records.

                 Back to the original DB, bring up related records from next table, back to the Inactive db, import......etc., etc.

                 Thanks

                 Steve

            • 3. Re: Set Field or Replace Field Contents?
              MarcMcCall

                   Why are you wanting to move your inactive records to a separate file? Can you explain the purpose of this, there may be a better solution.

                   What is the purpose of the second file?

                   If it is just to have them for historical purposes, you could simply make a copy of you existing file and just delete all of the "Active Record" and now you will have all of you "Inactive" in a separate file.

                   In the Original file then just delete your "Inactive" records, and related record and it will only contain "Active Records".

              • 4. Re: Set Field or Replace Field Contents?
                SteveMartino

                     Thanks for your response Marc.  I did consider your method, which works fine for the initial set of inactive customers.  My concern is as a customer's status changes to inactive, I would like to eventually move them off the database.

                     I probably don't have to, as this is only a 8MB DB. 

                More info...if you need it :)

                     This DB is for a home heating oil company.  No accounting done on this DB (yet). It tracks when a customer will need a delivery & delivery history, all the heating equipment they have, all the service performed, etc.  A customer becomes inactive when they move, switch from heating oil to natural gas or propane, deceased, change companines, etc.  I then initially mark them as inactive, so they don't show up on delivery scheduling, mailings, equipment inventory, etc.  However, I still want to retain the information for situations where a new customer moves in to the existing home (all the equipment information and delivery/service history is now useful.  If it turns out this person will never become a new customer in the near future, the data isn't actively needed.  But, sometimes, customers who leave come back and it would be helpful if I could retrieve that information should the situation occur.

                • 5. Re: Set Field or Replace Field Contents?
                  MarcMcCall

                       IMO i would almost create some new tables in your current file that act as tables for your inactive customers. 

                       Setup with the same relationships that you have, just possibly amend inactive to the end of the table names, then you could do a looping script that transfers them all to the "Inactive" tables. Then if someone ever needs to be reactivated you could just build a script to move them back the "Active" tables.

                  • 6. Re: Set Field or Replace Field Contents?
                    SteveMartino

                         Thanks for your input Marc. 

                         Steve

                    • 7. Re: Set Field or Replace Field Contents?
                      MarcMcCall

                           Be sure to make some backups before making serious changes.

                      • 8. Re: Set Field or Replace Field Contents?
                        philmodjunk

                             And the simplest solution is not to move the data at all. You can set a value in a field to mark a record as "inactive" and then scripts, find criteria, portal filters etc. can omit those records from portals and found sets. This is much like a "soft delete" often used in databases where you "delete" a record by just marking it as deleted. Then you can "undelete" or "reactivate" a record just by finding it and changing the status field value back.

                        • 9. Re: Set Field or Replace Field Contents?
                          SteveMartino

                               Hey Phil, that's what I currently do, always hiding 'inactive' through the record scripts, sorts, finds, etc..  That's why I asked the original question, once I select a customer as 'inactive' (checkbox field on layout), I want a script to go thru all the related records, on the related tables, and mark them all as inactive (an inactive checkbox field is on all affected layouts).

                               So do you think the script I posted is correct, or do you (anyone) see any problems with it?  I currently tried the script on a back up (thanks Marc), in the 3 likely scenarios and it worked fine. 

                               Thanks

                               Steve

                          • 10. Re: Set Field or Replace Field Contents?
                            philmodjunk

                                 Replace field contents makes for a simpler script and when two scripts produce identical results, go with the simpler option. It may also be a touch faster.

                                 But neither script should be performed if there is the slightest chance that another user might have one of these records open for editing. That will "Edit Lock" the record and prevent your script from changing it. I try to use server schedules to run such scripts after close of business to ensure that this cannot be an issue.

                            • 11. Re: Set Field or Replace Field Contents?
                              SteveMartino

                                   Thanks Phil.  Shouldn't be a problem, I'm the only user, and I'll run the script manually.