12 Replies Latest reply on Oct 31, 2016 12:22 PM by fmpdude

    Clearing test data


      I have peoples names in my database that I have used for testing the db.


      Now I have added real data the test data keeps appearing. Example:


      Contact ID field = 1

      Contact Name = Paul Smith


      I deleted all the records in the contact table started again.

      Contact ID = 1

      Contact Name = Lee Brooks


      Even though I have deleted the Paul Smith data, his name keep popping up.


      How do I clear this out?




        • 1. Re: Clearing test data

          How are you "deleting" his data?

          • 2. Re: Clearing test data

            I deleted it from the contact table by just deleting the lines in the table.

            • 3. Re: Clearing test data

              Good morning lee423,


              I hope your day is going well. Assuming the ContactID field you mentioned is your primary key field, try setting the field definition to an auto-enter calculation - Get ( UUID ) - so that there is virtually no chance of your primary key field being repeated. Hope this is helpful. Have a great day!


              God bless,




              • 4. Re: Clearing test data

                Do you have other tables in your solution?


                Might it be looking up or linking to test data still present in a different table in your DB?

                • 5. Re: Clearing test data

                  Since I've never had a case where I deleted data in FM and it "came back", I'm going along with Phil here. You must have "related" data in some other table with that information.


                  Since FMP, AFAIK, lacks a global "Find in Database" capability, you can easily get a list of all the tables using a simple ExecuteSQL like this:


                  // How to get a list of all FM tables in the database:

                  ExecuteSQL ( "SELECT * FROM FileMaker_Tables" ; ""; ""  )


                  If you do that SQL in a SET VARIABLE statement, you could then loop through each table and every field to search for the string you want.  You might want a paragraph separator as the last argument for easier handling with other FM functions.


                  In another posting on the forum here, there is this code that will give you all the field names for a given table (Using FileMaker_Fields).


                  Getting ALL field names from a table


                  Combining these two approaches (table + fields), you can create a script that would loop through every table and then, within each table, each field, looking for the string you're interested in.  You might need some conditional logic to skip container fields or something like that, but this is the basic idea.




                  Or, it might just be easier, depending on how many tables/fields you have, to manually go to each layout, look for a suspect field and do a Find on that field using a wild-carded find. Doing a "*az*" would bring up all records for the field you ran the Find on with "az" anywhere in that field.


                  You could easily write an external program that would do the same thing using Java and FM's JDBC driver.


                  HOPE THIS HELPS.

                  • 6. Re: Clearing test data

                    Hmmm, If I wanted a list of all tables in my file, I'd just open Manage | database and select the Tables tab...

                    • 7. Re: Clearing test data

                      I was talking Programmatically, Phil, as in, automation.


                      But for the bottom part of my posting above, then sure, that might be an easier approach.

                      • 8. Re: Clearing test data

                        Exactly, It just seemed like you were using something far more complex than needed for the problems mentioned at the start of this thread.

                        • 9. Re: Clearing test data

                          I'm with Phil..

                          First confirm that you have in fact deleted the row you think you have then...

                          Find a layout control that shows a value that you think you have deleted.

                          Drill into the the field definition and see if its set to lookup, or some kind of calculation that produces the errant value.

                          My guess is that you have used auto enter lookup to get data from a related table.

                          In this case you can delete the row in contacts and have deleted data stored in another table.

                          Youll have to figure out how to retrigger the lookup (if that is the case) when contacts are deleted.

                          FileMaker tends to do exactly what you tell it to do..

                          If you tell it to store contacts data in a suppliers table with a method that allows deletion of the contact without affecting the suppliers table data thats what you will get.

                          • 10. Re: Clearing test data

                            Of course there's the make a clone option that will give you a copy of your database with no data in it.

                            But that does not solve the problem of deleting a record you no longer need.

                            • 11. Re: Clearing test data

                              When you just delete records, that should not have an impact on a serially-generated ID key. Did you also reset the key in that table so it starts over at 1 again? (By the way, I can't see any real reason to do that.) I'm with the people who suspect that you're seeing values coming from a related table.

                              • 12. Re: Clearing test data

                                I don't disagree, but being able to find database data database-wide is a big missing feature in FMP. The OP's problem would have been trivial to figure out with a simple "Find in Database" capability.


                                In Navicat, for example, aside from an extremely powerful "Find in Database" - even using RegEx, you have powerful syncing tools and the like -- all built in.

                                Perhaps there's a script out there already that finds all the data using SQL and LIKE commands on each field in a FM database, but in the past I've always written my own when it needed to be programmatic -- as opposed to Navicat's dialog approach.


                                Thanks Phil.