1 Reply Latest reply on Oct 28, 2010 12:43 PM by philmodjunk

    Searching a FMP 8 database with information from .csv or .xls file

    fogharty

      Title

      Searching a FMP 8 database with information from .csv or .xls file

      Post

      I am still working, unfortunately in FMP 8. I have a database of 1000+ publications that we produce. Some of those publications will require additional editing before they can be reprinted. So I have made a field on the record layout that is labeled "Correction" and has a drop down value list of either "Yes" or "blank." Each record also has an "Item Number" field.

      Once a month we get the reprint list as an .xlsx file.  Ideally, I would like to take the information from the Item Number column in the reprint list (say, 50 items) and do a search of the FMP database and have the result show only the ones that have the Corrections field set to "Yes."

      Is this possible? Do I have to make some kind of script? I could do a search of each item one at a time, but it would be much more efficient to search all 50 items at once to find the 8 that will have to be flagged.

      So I want to search the database with the xls file, matching up the Item Number, then only showing those matching records where the Corrections is set to "Yes."

      Any help would be greatly appreciated.

        • 1. Re: Searching a FMP 8 database with information from .csv or .xls file
          philmodjunk

          Import the xls data into a separate table with a calculation field, cCorrectionKey is set to return "Yes" as its text value.

          Define this relationship:

          Publications::PubID = ImportTable::PubID AND
          Publications::Correction = ImportTable::cCorrectionKey

          Set up a layout based on ImportTable that lists all the fields from Publications that you want to see and/or edit.

          Perform a find on this layout that puts an asterisk in a never empty field from Publication such as PubID and you'll see the list you've specified.

          Also, If your version of FMP has the "match all record in current found set" option for Go To Related Record, you could perform this find then use GTRR with this option to pull up a found set of all the records on a layout based on Publications.