1 2 Previous Next 19 Replies Latest reply on May 24, 2016 11:33 AM by bsrobinson217

    Mass Clearing of Fields

    bsrobinson217

      Hello Everyone,

      I am very very new to File Maker, the person who was in charge of dealing with this unfortunately is not able to continue working with this program so I have taken over.

       

      They have created quite an extensive layout and I would like to use the layouts that they made, but label them with a different year (they are labeled as 2015-2016) and I need to clear the fields and update the field titles to reflect the new year. I am unsure how to do a mass clearing of fields (we have able 1400 records that would need to be edited in this way). Is there a script I can use to clear the fields without damaging the layout/label names or will I have to do everything manually.

       

      Thanks!

        • 1. Re: Mass Clearing of Fields
          steveromig

          Hello Everyone,

          I am very very new to File Maker, the person who was in charge of dealing with this unfortunately is not able to continue working with this program so I have taken over.

           

          They have created quite an extensive layout and I would like to use the layouts that they made, but label them with a different year (they are labeled as 2015-2016) and I need to clear the fields and update the field titles to reflect the new year. I am unsure how to do a mass clearing of fields (we have able 1400 records that would need to be edited in this way). Is there a script I can use to clear the fields without damaging the layout/label names or will I have to do everything manually.

           

          Thanks!

           

          If what you mean is...

           

          "In the found set I am looking at, I want to replace the data in FieldX with "blah" (or nothing / blank)"

           

          ...then the answer is you can use the Replace Field Contents" functionality of FileMaker Pro. 

           

          Find the records you want to change, edit the contents of FieldX in one of those records, and then choose Records > Replace Field Contents and choose the appropriate option from the Replace Field Contents dialog box.  This will replace whatever you typed in to FieldX in the one record in all records in your found set - leaving those records that are not in the found set untouched.

           

          Be wary...the action of replacing the contents of a field cannot be undone so make sure it is really what you want to do.

           

          Steve Romig

          FileMaker, Inc.

          • 2. Re: Mass Clearing of Fields
            SteveMartino

            This may be unnecessary, but lets make sure were on the same page.

             

            A layout is based on a table, or table occurrence.  You can have many layouts, but I don't understand why the fields need to changed.  The fields are tied to the table.  The fields hold data.  Are you going to create new fields for the new year?  This would be a big mistake.  What happens when you need to look at data from last year?

             

            For layout design, you just duplicate the layout, change what's necessary.  But you say clearing of fields, and 1400 records that need to be edited.  Do you mean the table has 1400 fields? This is also not a good approach.

             

            If you don't need the records any more, just show all records, then delete all records.  It's a one shot deal, and you can not undo it.  So make sure you have a full backup or 2 (or more) before you start.

             

            If you could explain a little more about your solution, I'm sure there is a better way to accomplish your task.

            • 3. Re: Mass Clearing of Fields
              Vincent_L

              Hi, I'm replying to the Title, not you user case, because Mass Clearing of fields is a big performance problem in Filemaker and people googling for this would perhaps be interested by my Experience.

               

              Steve is right, replace filed contents is the correct script step. But it's very slow, as it only process one field at a time, that means it has to loop through all the records for each field you need to clear. So if you've 1400 fields and 1000 lines, that's 1.4 million operations. That's terribly inefficient, and unfortunately you don't have a script step that can update several fields at once.

               

              1. Lengthy but most efficient native method for scripting it

               

              Here's the fastest technique I found if you need to to this very often (you'd create a script). So of course it's a bit complicated (to explain, it's easier doing it that explaining it), so this is only for recuring needs.

               

              So, you create another table "empty fields", you create one calc field with 1 inside, and you create as much empty calc fields  you need as empty "" (you can make them regular field, but just make sure you never pout data in those). So if you need 1400 fields (that sa bit extreme) fields to be empty, you create 1400 empty fields (ok creating fields is a chore but you can hack a copy paste with Cilpboard Manager from fmButler, or you  just create an excel file with 1400 column, you'll then use Filemaker Excel conversion, that would create your fields).

              You create one record 1 that table (don't put anything in the fields)

               

              Then, on the table you need to empty fields on, you create a calc filed with 1 in it, and you create a relationship from this 1 to the 1 of the "empty fields table"

               

              Back to the main table you create an export and you chose to export all the fields of the related empty field tables, and with the key filed of your main table at the beginning.

               

              Then you do the export as a text file, this will create a file with 1400 empty columns with the same number of rows as in the foundset (each row containing the key field value, followed by the 1400 fields = 1401 fields)

              Then you will import that with update matching on the first field to you key field as a match, and you'll map all the 1400  fields to empty to the correspo,ding empty field.

               

              That import will clear all the fields in one go very fast.

              Of course you'd script the whole process

               

              2. For your particular case

               

              The quickest way to do it (perhaps not in execution time, but setting up time :  It's the quickest to setup because you won't have to use the gui to set your 1400 fields), Is to use SQL, don't worry it's simple. BUT you'll have to use a plug-in. Use the excellent and free base element plugin. You install it.

               

              and all you need to do is execute this (if you need to do it on the whole table) :if you need to empty field_01/02/04 in the table myTable

               

              Set Variable [ $x ; Value: BE_FileMakerSQL ( "UPDATE \"myTable\" SET fied_01='',fied_02='',fied_04='' ") ]

               

              and if you only want some rows, you specify them with where IN ()

               

              Set Variable [ $x ; Value: BE_FileMakerSQL ( "UPDATE \"myTable\" SET fied_01='',fied_02='',fied_04='' WHERE \"myKey_SKU\" IN ('SKU_20','SKU_40','SKU_70')  ") ]

               

              you can use a text editor like text wrangler

               

              Maybe this 2) method is as fast as method 1, I didn't test it extensively, but my guess is that 1 should be faster as imports and exports are the fastest things in filemaker

               

              Here's an example file

              login : Admin

              password :      -> Blank

               

              Dropbox - Mass_field_clearing.zip

               

              Of course those method are not straightforward and I feel that filemaker fails to provide a quick and easy efficient solution for it's users. It's much more complicated than needed, we need a ReplaceMany script step that would allow to set several fields at once.

              • 4. Re: Mass Clearing of Fields
                bsrobinson217

                Hello,

                Thank you for the response.

                Basically what is happening is: we are a cohort of about 6 using file maker to track site visits. We have layouts expressing the data we need for these site visits labeled by year. The previous creator of the layout had about 7-10 of them all doing different things but all using the same data. So for example: if you put a date or name in one particular field it would populate in all of the other fields.

                 

                The contact layout is directly related to the report layout. After creating a duplicate of the 2015/2016 year, I want to create a NEW report layout labeled 2016/2017 year that is using the information from the contact layout....with all of the same fields but have the contents be blank...so that when we are visiting the same sites this year we can enter in new information for those specific site (we visit the same places every year or so and need to keep a record)

                 

                I am not looking to erase past information (I apologize for not making that clear). Basically I want to duplicate a layout, rename it and remove the fields contents so that new information can be added later.

                 

                We have about 1400 records in the master file. There are definitely more than 1400 fields being used overall.

                • 5. Re: Mass Clearing of Fields
                  BruceRobertson

                  None of this makes the tiniest bit of sense.

                  I suggest you save the file as a clone and upload it here; or get a developer to help you.

                  • 6. Re: Mass Clearing of Fields
                    bsrobinson217

                    I am not really sure how to explain what I want to do. I want to create a clone of a layout, and while leaving the fields intact (so the field names would remain the same)...ONLY delete the contents of those fields to be able to replace them with updated information.

                     

                    So if I have a layout called 2015/2016, I want to be able to clone it. Rename it 2016/2017 and blank out all of the field contents from the cloned layout so that I am able to enter new information. Basically I just need to make a copy of the layout with all of the fields but with no contents within them.

                    • 7. Re: Mass Clearing of Fields
                      SteveMartino

                      I think what everyone is having trouble with is this.  What you want to do is easy.  But what you want to do doesnt seem correct.

                      Duplicating a layout, and erasing the field contents is deleting records.

                      Bruce's idea of posting the file/getting help us best before you end up doing something that damages the database or its data.

                      • 8. Re: Mass Clearing of Fields
                        BruceRobertson

                        "I am not really sure how to explain what I want to do. I want to create a clone of a layout, and while leaving the fields intact (so the field names would remain the same)...ONLY delete the contents of those fields to be able to replace them with updated information."

                         

                        As Steve says. There is no such thing a "clearing the contents of the field" while retaining your historical data. You are talking about creating NEW records.

                         

                        I'm afraid I have to say it again. What you're asking still does not make the tiniest bit of sense.

                         

                        You're going to need to find somebody or hire somebody who knows what they are doing.

                        If you post a clone here, somebody may be able to help you.

                        Under File, Save a copy, you have an option to create a clone of your file.

                        A clone of a file retains all the layouts, fields, scripts, etc but does not contain any of your data.

                         

                         

                        File save a copy choices.png

                        • 9. Re: Mass Clearing of Fields
                          bsrobinson217

                          Okay.

                          Here is a clone what I am working with.

                          The original copies has information such as the schools name, street address etc. There are also layouts that include information from past years, those are labeled as Visit Report 2015/2016, Visit Report 2016/2017, and 2015 Visit Booking Report.

                           

                          I need to keep any information that is entered in the Visit Report 2015/2016 layout because we use that when we are planning visits for the current year.

                           

                          The Visit Report 2016/2017 needs to have all the same fields and is basically a clone of the 2015/2016 year. When I try to clone 15/16 everything copies over including all of the notes our counselors entered for that year. What I need to do is remove the notes in those fields making them blank so that they can enter information for the new year.

                           

                          We have about 1400 schools that we visit over the course of the semester so we need to keep the school's information and school's contact information but we need to make it so that the visit reports for the new year are blank as well as the dates and times the visits are scheduled.

                           

                          Hopefully that makes more sense?

                          • 10. Re: Mass Clearing of Fields
                            erolst

                            Hey, why has Vincent's forehead->desktop inducing essay vanished?

                            • 11. Re: Mass Clearing of Fields
                              erolst

                              I thought this post was gone – strange forum …

                               

                              Vincent –

                               

                              after you've robbed n records of their identity, e.g. erased all field data – payload, meta, whatever – you have nothing but n empty shells – where is that result different from having deleted the records and created n new ones?

                               

                              btw, I think that even the requirement to erase the contents of not all, but a large number of fields hints at severe structural problems (a field count of 1,400 tells that story all by itself) – in a properly normalized solution, a record only makes sense with all (ideally), or at least (in practice) most of its fields filled.

                              • 12. Re: Mass Clearing of Fields
                                Vincent_L

                                My techniques are meant to clear specific fields (not all) from a bunch of records.

                                I use it everyday with cache fields (of course I have 70 of those not 1400) : That's used for inventories, I  import from  mysql server for all product that have stock, then omit records, then in those I search for record where at least one of those fields is not empty, and run my import clearing method. That way I ensure that I don't have leftovers and hence product that have outdated inventories.

                                 

                                And yes that's not a normalized good method to deal with inventories, but Filemaker being slow, that's the way to get decent speed in search, sorts.

                                • 13. Re: Mass Clearing of Fields
                                  Vincent_L

                                  robison, your file is damaged, it's better to use dropbox and put a dropbox link

                                  • 14. Re: Mass Clearing of Fields
                                    JesseH

                                    wouldn't something as simple as making a copy of the table and renaming it 2016/2017, then make a copy of the associated layout and change which table it pulld data from?

                                     

                                    This would keep all the original Fields, use the same layout setup, and no records = no data?

                                    1 2 Previous Next