4 Replies Latest reply on May 29, 2012 3:57 PM by Stephen Huston

    Script to filter unnecessary records


      I have a group of people that submit information in excel format. I am creating a common report from these files.


      The fields are all common however, the record fields vary quite a bit. The first field has 6 valid IDs that I need for a final report. I want to write a script to eliminate all the unnecessary records, but there is no common theme (blank cells etc.) Everyone does theirs different. The only commonality is the valid fields. I assume that I need to create a variable for those valid entries and then eliminate everything that is not valid. I have been attempting to do that, but have not had a lot of luck. I realize this is Filemaker 101, but does someone have a sample script they could share with me to show me how to do this?


      Thanks in advance!



        • 1. Re: Script to filter unnecessary records
          Stephen Huston

          It sounds like you are importing these records from Excel. If you set strict validation on the match field in FMP into which the some-times valid column is imported, only the valid records will import. FMP will stop invalid records from importing if the FMP field definition requires the valid data.


          This does mean you will have to be very specific about what is valid  data when modifying the field definition's validation rule(s).

          • 2. Re: Script to filter unnecessary records

            Thanks for the help Stephen. I tried your suggestion and got most of it by creating a list of the acceptable field items. When I ran that, I still got  a couple of blank records,  so I modified the import script to require that each cell of that field be non-empty and equal to one of the items on the list of acceptable field items.  Still getting rows of empty records??  Any ideas?



            • 3. Re: Script to filter unnecessary records

              Another way to do this:


              Import the excel file into a temporary table, then use a script to process these records.  It would afford you more control over what data is allowed into your solution.

              • 4. Re: Script to filter unnecessary records
                Stephen Huston

                You could run a clean-up script after the import, constraining the imported records (the found set at the end of the import) to records which have unwanted blanks, and then deleting that resulting found set.


                The only drawback is that you no longer have your imported records as the active found set. In fact, at the end of that Delete Found Set, you have no records in the found set until you either Show All or perform a new Find.