4 Replies Latest reply on Dec 13, 2013 7:26 AM by filemaker_newb

    How to export specific records/rows within FM...?




      I realise this may be quite a basic question, but I am very new to Filemaker and realise this could be the solution to a number of issues...! I am using FM11.


      I have data stored in a series of Excel files. I have a Master file, which includes all the records I have across all of the spreadsheets. I then have a series of separate spreadsheets, all of which include a different set of records which are included within the Master file.


      I have created additional columns at the end of my Master file with VLOOKUP functions to identify which of the records in the Master file are included within the other spreadsheets.


      What I would be looking to do is import this data I have collected in my Master file in FM - this is simple enough! I would then be looking to generate separate outputs for each of the different spreadsheets through FM - that's where I'm stuck...


      So, for example, let's say I have 400 records in my Master file. I have created a separate column to identify which of these records are held in another spreadsheet. I have run a VLOOKUP function, which shows that 300 of these records are held in the Master file. The remaining 100 records are marked with #N/A. How would I then just export the 300 records from FM...?


      I hope I have been clear enough, but let me know if I should clarify any further. What would be great is if I can get some guidance about running a script, that would allow me to output all of the spreadsheets in one go - I have about 30! Anyway, solving the first issue with just being to output one would be progress at the moment...


      Any help on this would be much appreciated - thanks!

        • 1. Re: How to export specific records/rows within FM...?

          If I am understanding what you are trying to do correctly, you are importing a series of spreadsheets into one master FileMaker Pro file...correct?


          Seems like you could import one spreadsheet into your Master file and tag those records somehow (for example put a "1" in a field called "tag").  Then you could repeat the process for each spreadsheet that you have and put a different value in the "tag" field.  When all is said and done you will have all your records in the master file jumbled up with different tags.


          At that point you could do a search on the tag field for the set of records that you are looking for and when you export you will only export those found set of records.


          In your example above you could also search for the records that are marked "#N/A" and omit them from the found set leaving you with the 300 remaining records.


          If this is on the right track -  a script could be written that simply finds all the records with "1" in the tag field, exports them, then finds all the records with "2" in the tag field and export those, etc.


          Steve Romig

          FileMaker, Inc.

          1 of 1 people found this helpful
          • 2. Re: How to export specific records/rows within FM...?

            I do something like this on a much smaller scale.  It may be easier to do one more step.  Make another sheet in the workbook, call it (ImportToFM) that only contains the 300 records you want to import.  Then reference that sheet for the import in FM

            • 3. Re: How to export specific records/rows within FM...?

              Thanks for this, that's exactly what I'd be looking for.


              I'm doing a bit of searching around via Google and might need some help running this script! For the purpose of this example, I want to do the following:

              - Open my "MASTER" sheet, go to a field named "CHECK"

              - Look in the "CHECK" column for entries which say "#N/A" and select all the other records (so those that do not include "#N/A")

              - Once all those records have been selected, duplicate the necessary records held in "MASTER" to another FM output called "OUTPUT1"


              This is how far I've got with the scripting (I imagine I'm probably way off...):


              - Go to Record/Request/Page [First]

              - Loop

                   - Set Selection [MASTER::_CHECK)

                   - If [MASTER::_CHECK  ≠ "#N/A"]

                        - Open File [OUTPUT1]

                        - Duplicate Record/Request

                   - End If

                   - Go to Record/Request/Page [Next]

              - End Loop


              Once again, hope I am being clear on this, but any advice would be greatly appreciated.


              SteveMartino, thanks too for your response and I could see how that could work. The only thing is that the Master records sheet will be updated and this will impact the outputs in the other sheets. So producing the output directly from FM would be the way to go I think.

              • 4. Re: How to export specific records/rows within FM...?



                This was fairly easy to do in the end (although took me a while to get there!):


                - Go to Layout

                - Perform Find (Omit records including #N/A)

                - Export Records