1 2 Previous Next 17 Replies Latest reply on Jun 1, 2016 1:24 PM by Mike_Mitchell

    delete all records over a certain age (or export)

    psychonaut

      hi i hope you are well.

       

      i have a  customer with a file maker pro 11 database.

       

      it has a 9gb datafile spanning 10 years of records.

       

      i want to trim this down to the last, say, 2 years to make the whole thing easier to backup

       

      i cant see how to do this. i have zero experience with filemaker so excuse me if this is a n00b question. i did about 2.5 yrs of sql 10 years ago though...so ive some idea of how databases operate.

       

      ive made a copy of the database for archive purposes, and now want to remove all the records except for the last 2 years from the live database. (or i can do it the other way round, or some other way if anybody has a better suggestion)

       

      any idea as to how i can do this please? bear in mind i have NEVER seen filemaker before.

        • 1. Re: delete all records over a certain age (or export)
          Mike_Mitchell

          Is there a date field in the table you want to archive that you can use? Like a creation date, closure date, whatever?

          • 2. Re: delete all records over a certain age (or export)
            psychonaut

            probably - but ive no idea even how to look at the table structures - all i can see is the front end of the db in user mode.

             

            told you i was a n00b at this!!

            • 3. Re: delete all records over a certain age (or export)
              Mike_Mitchell

              Assuming you have full access credentials, go to File > Manage > Database.

              • 4. Re: delete all records over a certain age (or export)
                psychonaut

                yeah...thats one of the issues then - they are all greyed out.

                 

                customer swears blind that they only have the one login credential....i'll have to ask them to "reassess" that....is there a default admin login for FM?

                 

                ok. so assuming i can get into that bit, how does file maker work? is it SQL or is it propriety?

                 

                im guessing i will just delete

                 

                1)all customers  that have a created date of before some date

                2) all invoices before some date

                3) i dont even know what else is in there....probably quotes and job sheets. same for them.

                • 5. Re: delete all records over a certain age (or export)
                  psychonaut

                  actually, i wont delete the customers, that would be a bit daft.

                  • 6. Re: delete all records over a certain age (or export)
                    psychonaut

                    soemthing else occurs - the db is probably big becuase they shove loads of images in it. is there a way of seperating these out?

                     

                    maybe i could juts delete all images that are older than 2 years.

                    • 7. Re: delete all records over a certain age (or export)
                      Mike_Mitchell

                      If these date fields are on the layout, you can just perform a Find like this:

                       

                      1) Enter Find mode (View > Find Mode or Ctrl / Cmd - F)

                      2) Type "<=" plus the date you want to use as the breakpoint.

                      3) Press Find (or numeric keypad Enter).

                       

                      And yes, you'll need to do that on each layout (table).

                       

                      If you don't have full access credentials, you're going to have difficulty maintaining the database. No, there is no "back door" credential for all FileMaker databases.

                       

                      FileMaker is not a SQL system; it uses a proprietary structure. However, it is possible to use SQL in certain applications (such as the ExecuteSQL function or the Execute SQL script step).

                      • 8. Re: delete all records over a certain age (or export)
                        Mike_Mitchell

                        Yes, it's entirely possible that this is the problem. There are plenty of ways of separating them out (really, keeping everything inside the database file would be my last choice, except in specific circumstances):

                         

                        1) Use externally managed containers with FileMaker Server. This stores the assets in the FileMaker Server directory tree, managed by the application.

                         

                        2) Use a third-party tool such as 360Works' SuperContainer. This uses a web viewer object to point to the assets stored on a web server.

                         

                        3) Use a separate database file that has the images in it, and relate those image records back to the main database via a relationship.

                         

                        It's often a good idea to have a separate file for container assets anyway, one of the reasons for which you've already discovered (backups). It also makes data migration between versions much easier and more stable.

                         

                        HTH

                         

                        Mike

                        • 9. Re: delete all records over a certain age (or export)
                          Heidelberg

                          It ist still not clear for me if you have full access. Assuming that you have, you should make a new filed of the type "formula"

                          As a formula enter get(container attribute;NAME OF THE CONTAINERFIELD WITH THE PICTURES;"created")

                          You may need to use a comma instead of a semicolon as a separator - depending on your country/language (I am from Germany). Type of output: Select "Timestamp"

                           

                          You may have to make formate adjustments  using the inspector so that the field uses a date formate like for example Year Month Day

                           

                          After that you could search for records with "older than" dates.

                           

                          The "created" attribute is mostly  available when a picture is only linked to a container and not imported. But also imported pictures could be search for creation dates ... it shows usual if one types in above formula "all" instead of "created". In this case the output-type is to set to "TEXT. In that case you would have to search the "all" field for a certain year for example 1998. But since "all" shows for example also the file size (12341998 byte) you may still have to screen the search results - it would just make it somewhat easier. There is also the option to search the "all"-field for a certain string at a certain position. But the way you write that might get too complicated.

                          You may want to try also other options to use as in a fat-attribute-fomula  for example the modification date. Read the help segment for "get(container attribute)

                          • 10. Re: delete all records over a certain age (or export)
                            Heidelberg

                            Just got another idea.

                             

                            You can sort all field of FMP. An you can click to "unsort". Since the display of unsorted files corresponds with the date of entry you can as well just say "lets kick out all files but the last 500".

                             

                            In that case you should make a new field of typ "number"

                            In browser mode:

                            Unsort database

                            Select that field

                            Record-menu ... Replace field content

                                second option = replace with numbers ... interval

                            Select all records ... your criteria for example where field content is <2222

                            record-menu ...delete selected records

                             

                            Goes without saying ... would test any proposal firts with a copy of the original

                            • 11. Re: delete all records over a certain age (or export)
                              Mike_Mitchell

                              Heidelberg wrote:

                               

                              It ist still not clear for me if you have full access.

                               

                              He does not.

                               

                              "yeah...thats one of the issues then - they are all greyed out."

                              • 12. Re: delete all records over a certain age (or export)
                                Mike_Mitchell

                                If you're going to be joining the FileMaker community (a wonderful place to be, IMHO), I suggest you grab a copy of the FileMaker Training Series: Basics. It's free and will give you the baseline of knowledge you need to administer a FileMaker DB.

                                 

                                (This issue is for version 15, but since 11 went out of support in September of last year, it's probably not a bad idea to upgrade.)

                                • 13. Re: delete all records over a certain age (or export)
                                  psychonaut

                                  its very kind of you all to respond.

                                   

                                  i might farm this out to someone - the chances of the customer paying to upgrade is close to zero...

                                   

                                  ive been lumped with this dinosaur of a database. whether the client is going to want to pay me to learn how to administer it is another thing....

                                   

                                  the problem is that they have a crap broadband connection and so the db, at 9gb, never gets backed up, as it changes daily.

                                   

                                  it may be easier to just have a local backup.

                                   

                                  however, is anyone up for taking this on? if so what kind of charges?

                                  • 14. Re: delete all records over a certain age (or export)
                                    Mike_Mitchell

                                    psychonaut wrote:

                                     

                                    the problem is that they have a crap broadband connection and so the db, at 9gb, never gets backed up, as it changes daily.

                                     

                                    I'm not sure what one has to do with the other. Are they not using FileMaker Server?

                                     

                                    however, is anyone up for taking this on? if so what kind of charges?

                                     

                                    You can find a consultant in your area here:

                                     

                                         FileMaker Consultants, Data Consultants, Database Consultants

                                     

                                    You may also want to check freelance sites like eLance, guru.com, etc.

                                    1 2 Previous Next