9 Replies Latest reply on Sep 22, 2016 11:31 AM by justinc

    How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)

    Peter.Guthrie

      I have a database that has developed through several different users (none of us are professional programmers) over about 10 years.

      There are a large number of fields that have been added and deleted over the years. When I run the DDR, Several of the Scripts have more than have of the field lists as <Missing Field>. It gets distracting. It is also a problem when I am setting up a defined import order, because all of those missing fields show up as blanks on the FM column.

       

      Is there any way to clean up the data base to remove all of those empty field "memories"? I realize one solution would be to reconstruct the database, but I don't really have that much time (or energy). Does FM have any tools to help with this?

       

      Thanks, pbg

        • 1. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
          Johan Hedman

          I would by the Base Elements analys tool and from that check for Errors, Warning and Unreferenced items before I start working on that solution

          BaseElements | Goya Pty Ltd

          • 2. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
            Peter.Guthrie

            Thanks - I will try it, but I don't think I will be able to get my senior management to purchase a copy for me to use, and I can't afford to buy it myself.

            pbg

            • 3. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
              justinc

              In addition to Base Elements (the tool - not the plugin), there are also Inspector Pro or FMPerception, which can help you find these references.  Each of these costs about $400 - $500 for a single-user license I believe.  You can often find discount/coupons, though.  If management is really that annoyed, they might spring for it. 

               

              But, another way that you can at least search for "<missing field>" errors in your scripts is to export the scripts to a PDF.  If you open Script Workspace, select all of the scripts in the left window pane, then 'Print' the scripts.  This works easily in OS X where you can choose "PDF" output once the Print dialog has opened.  On Windows, there are a couple of PDF 'printers' that you can install - then you pick that 'printer' as the output and it will generate a PDF instead of actually printing it.

               

              This PDF will then show you all of the script steps in every script.  Then you just search through the PDF for "<missing field>" to find out which scripts have them.

               

              ALL of these options, though, won't do the cleanup for you.  You will still have to go in and manually delete the script steps from each script.  These tools are just there to help you find those script steps in the first place.

               

              --  Justin

              • 4. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                philmodjunk

                Even simpler, generate the DDR as HTML and let it open in your web browser, Then use the browsers text search tool to find all instances of "missing", and "unknown" to track down and correct this missing references.

                 

                Third party tools such as Base Elements or Inspector can save you time, but it's possible to just do it yourself in the web browser.

                2 of 2 people found this helpful
                • 5. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                  macwombat

                  I have done exactly what philmodjunk has suggested with the DDR over a database which was structurally very large - its time consuming but can definitely be done.

                  • 6. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                    justinc

                    I don't know if I have ever played with the HTML version of the DDR - always used the XML + Inspector.  The HTML suggestion is probably better than the printed Scripts because you will also get field and calculation problems.  Unless you want a narrow scope and stick with just the scripts.

                    • 7. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                      BruceRobertson

                      What does it mean that you have these scripts with field errors; and you are still using the file; but you have not fixed the scripts?

                       

                      Does it mean that these scripts are not used? In which case - maybe you should just delete them.

                      Does it mean that the scripts are being run, but generate no problem?

                      The scripts are being run, and are in fact not functioning properly and are therefore making your data inconsistent?

                       

                      Your management doesn't want to spend money to solve the problem.

                      So: this database is actually irrelevant to the effective functioning of your business?

                      • 8. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                        Peter.Guthrie

                        I guess I need to make a fuller description. I'm a scientist, working at the National Institutes of Health. The database I'm talking about is very useful for us, but I can't make an argument to purchase any of the tools I've seen suggested so far, especially since none of them seem to do what I need want. If I can't convince myself that a product will be useful, I'm certainly not going to spend the people's money on it.

                         

                        All of the scripts run fine, and any missing fields in a script can be easily identified with the DDR. However, when I use the DDR to look for problems, the section Fields used in this script has many, many <Missing Field> entries included in the list. The DDR shows over 700 <Missing Fields>, and most of those are in three scripts. They don't seem to interfere with the script, but I don't see why they should be showing up. An additional issue is that when I need to specify an import order, I have many, many blank entries in the right side of the Import Field Mapping window, which make it difficult to scroll around and to move fields around to get the proper match.

                         

                        So, my problem isn't identifying where a Missing Field could be causing an error. My problem is not being able to clean out what I suspect is a field list table in FileMaker, which isn't being purged of deleted fields, so they continue to show up in the DDR. Everything I've seen here so far is directed at finding the errors, and won't help me, in essence, sweep out the deleted fields from the database tables.

                         

                        Yes, this may be more a matter of aesthetics than of functionality, but I find it very distracting, and annoying. (Speaking of annoying, when will FileMaker be able to import .xlsm files? They've only been around since Excel 2007.)

                        • 9. Re: How to clean deleted fields from DDR listing and from import order listing (FMPro14Advanced)
                          justinc

                          That's just it - there is no 'automated' method of removing those field references.  The only way to get rid of them is to manually delete the scripts steps that have a '<missing field>' reference.  OR...alter those script steps so that they point to a valid field.  Our suggestions have all just been about how to find those references - if you know that they are essentially in 3 scripts, then you probably don't need those tools - as you noted.

                           

                          Maybe you know this, but those '<missing field>' references are caused thusly:

                          TableA::Field1 exists

                          Create Script

                          Add script step:  "set field [ TableA:Field1; value: "Sam"]"

                          ... (time elapses)

                          Delete field:  TableA::Field1

                           

                          Now the script will read:  "set field [<missing field>; value:"Sam"]"  (or something like that).  Internally FileMaker builds a map of all references (that's what you get when you export the DDR).  So there's an entry for "Field1: id=1234".  (This is an internal FM id, not anything you created.)  Then when you create the script step, it maps "Script step: set field <-> fieldID=1234".  If Field1 is deleted, FIleMaker goes back to the script and removes the reference to the field, but leaves behind the script step.  So you have:  "Script step: set field <-> fieldID=<null>".  FileMaker won't ever remove the entry for ""Script step: set field <-> fieldID=1234", it can only update the "field" reference/mapping portion.

                           

                          In order to fix your extensive list of blanks in the "Import..." script steps, though, I think you might have to start over:  create a NEW "Import..." script step (don't 'duplicate' the existing one) in the script, and remap everything.  The new step should only contain entries on the right side that correspond to current existing fields.  Tedious, especially with FM's wonderful mapping UI.