5 Replies Latest reply on Apr 5, 2015 7:47 PM by DavidJondreau

    Import Raw data and clean up records



      Import Raw data and clean up records



      I'm importing a bunch of raw sales data from hundreds of different store locations. I have basically 2 tables that are needed for this operation. a StoreInfo table that contains all covered locations (about 400 locations) with full store info. And a second table for Raw Data (RAW)

      We get raw sales data in an excel spreadsheet from a vendor. I can import the data and convert it for our needs just fine. My issue is that I have about 70,000 records in my raw data table but most of it is from locations that I don't care about (basically the ones NOT in my StoreInfo) and it increases the file size quite a bit. 

      So what I'm trying to figure out is an efficient method for either skipping the non-covered stores during import or to then simply delete the non-covered stores from the raw sales data. 

      I created a loop that compares StoreID's from the RawData to StoreInfo using a Find each time and deletes the RawData record if that record contains data from a non-covered location but it takes over 20 minutes on a Quad-Core MacBook Pro with plenty of RAM. I'm convinced I can make this faster, just can't seem to figure it out. 

      Go to Layout (RAW)

      Go to Record/Request/Page (First)
        set Variable ($compare ; RAW:StoreID)
        Go to Layout (StoreInfo)
        Enter Find Mode
        Set Field (StoreInfo:StoreID ; $compare)
        Perform Find
        if (Get (FoundCount) = 0)
          Go To Layout (RAW)
          Delete Record/Request
          Go To Layout (RAW)
        End If
        Go To Record/Request/Page (Next; Exit after last)
      End Loop


      Any "faster" ideas or script steps I might have missed?? 

        • 1. Re: Import Raw data and clean up records

          The simplest way is to set up validation in the import RAW table.

          Create a Value List of your Store IDs. In the Store ID's validation setting in the RAW table, use FilterValues() and ValueListItems() to see if the ID being imported is permitted.

          • 2. Re: Import Raw data and clean up records

            A faster method of that script would be to create a relationship between the Store Id from the raw table and the Store Id from the store table.

            Then you don't have to go to the store layout to find the store, you can simply see, while you are on a record in the Raw table if there is any value in the Store::Id field.

            If there is a related store there will be, if not there will not.

            Then the record can be deleted.

            Go to Record/Request/Page (First) (In your Raw table)
              If ( IsEmpty ( Store::Id ) )
                  Delete Record
                  Go To Record / Request [Next] Exit After Last
              End If
            End Loop

            This is just off the top of my head. But give it a try.

            • 3. Re: Import Raw data and clean up records

              I'd try David's method first, but if you have trouble getting it to work, you can import your raw data, perform a find to find just the records you want and then import them from the RAW table into your other FileMaker table also.

              • 4. Re: Import Raw data and clean up records

                David's method ended up working for me! I validate the StoreInfo during import and use a ValueListItems check. This made the StoreInfo field Empty for every record that didn't have a related StoreID. Then it was a simple find and delete all Empty! 

                Worked great! Thanks again!

                • 5. Re: Import Raw data and clean up records

                  You can save yourself even more time if you set your field validation to "Always" evaluate. Then those records won't even import.