3 Replies Latest reply on Aug 4, 2010 3:21 PM by philmodjunk

    Searching and clearing  contents of repeating fields

    ambrosiasw

      Title

      Searching and clearing  contents of repeating fields

      Post

      I posted earlier in regards to a project I am working on to move data from our old database to our new one, which doesn't contain any repeating fields.  But before I can be done with this fields forever I have to move this data. My question now is, is there a way to script FileMaker to search my product repeating field using the rightwords function to find values that aren't numerical, then clear the field?  Anything in the field that ends in a version number (i.e. 1.0.10) I want saved, any entries in the field that end in words or letters I want cleared. 

        • 1. Re: Searching and clearing  contents of repeating fields
          philmodjunk

          Since you are working to eliminate the repetitions anyway. I'd import the data first into separate records and then search that table for such records and delete them.

          You could define a calculation field like this and search for any records with 1 in this calculation field:

          IsEmpty ( Filter ( RightWords ( productField ; 1 ) ; "0123456789" ) )

          That will find all records that do not have a numerical digit in the last word of ProductField.

          • 2. Re: Searching and clearing  contents of repeating fields
            ambrosiasw

            The data has already been exported from our old database, and I would prefer not to move it again before putting it in the new one. Basically what I did was the old database was our invoices database. I exported the invoiceID and Product fields from each record into new records in their own database. So now I have almost 60,000 records that are only product names, invoiceIDs and version numbers. The invoiceID field is what i will use to tie the data to the right records in the new database. The product field holds the name of the product and the version number for most products. I created the version field in this new database to put just the version number in, for records that have it. The contents of the version field will be imported into the new database once this project is done and the invoiceID field will be used to match records. What I need is to clear any entries in the product field that don't end with a version number, hence my question above. I need a way to go through all these records and leave only entries that end with version numbers. Can your suggestion above be modified to work this way?

            • 3. Re: Searching and clearing  contents of repeating fields
              philmodjunk

              No, because you'll want to clear specific repetitions of the repeating field instead of entire records. It will be many times more difficult to do that instead of what is really a very simple operation after you have split the data into separate records.

              If you've imported this data already, isn't it already split up? It should be, as that makes life much easier in many areas as you work on completing the conversion.