12 Replies Latest reply on Jan 26, 2017 11:17 PM by Malcolm

    Archive Records

    RossPartridge

      Title

      Archive Records

      Post

           I have a table with Transaction Records.

           Fields are Member ID; Date; Transaction Type; Amount.

           One of the Transaction Types is "Balance C/F"

           These records are displayed in a portal, listed by Member ID

           I wish to write a script which will:-

           1. Find all records before an entered Date

           2. Calculate a New Balance C/F record as at that new date, from records in found set

           3. Append all records in found set to an identical structured table called "Archived Records"

           4. Delete the found set from original table.

           Anyone have any ideas??

            

           Ross

            

            

        • 1. Re: Archive Records
          davidanders

               Why not just save a copy of the database with date appended? Then delete the records.

               Or save a clone of the database and export the records to the clone?

               Not that either are superior, but #1 is very easy.

          • 2. Re: Archive Records
            philmodjunk

                 Import Records can copy blocks of records from one table to another. It can be used to move data between two tables in the same file or different files. Are you familiar with this tool?

            • 3. Re: Archive Records
              mpainesyd

              Filemaker desperately needs an APPEND function to do exactly this. My DOS software Open Access had this feature from the mid-1980s!

              The steps are:

              FIND records (eg account_date < 1/1/2015 )

              APPEND found records to an archive table

              DELETE found records (from original table)

               

              This is a basic need of anyone maintaining a large database with chronological records such as accounts. I have been hoping for it to be included in each FM update - have I missed something?

               

              I have been getting around the problem by exporting the records to Excel then importing them from Excel to the archive table. This is not very elegant (to use polite terms).

               

              The other "solution" is to use Import Records to bring the found records into the archive table but, again, this is not elegant.

              • 4. Re: Archive Records
                philmodjunk

                Filemaker desperately needs an APPEND function to do exactly this. My DOS software Open Access had this feature from the mid-1980s!

                The steps are:

                FIND records (eg account_date < 1/1/2015 )

                APPEND found records to an archive table

                DELETE found records (from original table)

                Sorry, but scripts using Import Records can do exactly what you describe here.

                Use a scripted find to find the records with the specified date

                Import this found set. Use the Import Matching option, if needed to avoid adding duplicates of existing records (but it's pretty easy to modify the find criteria to avoid finding records already archived.)

                Delete found set from original table.

                 

                Done.

                • 5. Re: Archive Records
                  Malcolm

                  mpainesyd wrote:

                  have I missed something?

                   

                  I have been getting around the problem by exporting the records to Excel then importing them from Excel to the archive table. This is not very elegant (to use polite terms).

                   

                  The other "solution" is to use Import Records to bring the found records into the archive table but, again, this is not elegant.

                  You may have missed Scripted Imports. You may have missed Recurring Imports. You may have missed the fact that Perform Script can call a script in another table.

                   

                  In your Archive file, create a script that will Import Records from your Active file.

                  In your Active file, create a script include steps that do this:

                   

                  Find [ date < X ]

                  if [ Get ( FoundCount ) ]

                       Perform Script [ "Import" from file: "Active file" ]

                       Delete All Records

                  end if

                   

                  malcolm

                  • 6. Re: Archive Records
                    mpainesyd

                    Thank you. I will try this but my archive TABLE is in the same database FILE as the active table.

                    Anyway I will look at creating my own function to do this process.

                    • 7. Re: Archive Records
                      BowdenData

                      It is still the same sequence that Philmodjunk outlined, regardless of where your Source and Archive tables are located. Hope this helps.

                       

                      Sent from my iPhone

                      • 8. Re: Archive Records
                        philmodjunk

                        Import Records can import record from Table A in to Table B of the same file.

                         

                        When you go to select the file from which to get a list of tables as your source table, just select the very file that you have open.

                        • 9. Re: Archive Records
                          siplus

                          Actually your request is a bit more complex than it seems.

                          You want to preserve the balance.

                           

                          When creating a new transaction record I presume you get the current balance with a SQL or in some other way.

                           

                          Let 's say you have the following records for client 001

                           

                          date          amount     Balance C/F

                          1.1.2017     +500          2500

                          3.1.2017     -100           2400

                          7.1.2017     -250           2150

                           

                          if you choose 4.1.2017 as archiving date, no problem, because you still have a client record with a Balance C/F, but if you choose 8.1.2017 all records will be archived and when creating a new transaction for client 001 you can't just retrieve the last record and get its balance, you'll have to go to the archive.

                          • 10. Re: Archive Records
                            philmodjunk

                            A commonly used trick is to replace the archived transactions with a single "balance forward" entry that keeps the balance running totals correct.

                            • 11. Re: Archive Records
                              mpainesyd

                              OK - I have got it working. Thank you for the tips.

                              I was confused by the Import Field Mapping dialogue (see snapshot) which is called "Specify Import Order" in the script manager. Also I noticed that "Add new records" will create duplicate records in the target table, if I accidentally haven't deleted them from the source file. But that is manageable through scripting.

                              • 12. Re: Archive Records
                                Malcolm

                                Glad you got it working.

                                 

                                The "Arrange by:" pop-up menu includes "Matching Field Names" which looks like it would be perfect for your use. Though "Last Order" will work perfectly now that you have the fields mapped.

                                 

                                Earlier you had said that you were going to delete records after they had been transferred. That would prevent duplicates being imported a second time around.

                                 

                                Malcolm