9 Replies Latest reply on Nov 14, 2013 1:26 PM by philmodjunk

    Export by Item Type

    dansan500

      Title

      Export by Item Type

      Post

           I hope you can help me - I need to write a script that will export information by (Item Type)

           We have maybe 30 different item types in Field = (ItemType)

           I want the script to FIND all the files with each ItemType, then Export an Excel File (Named that Item Type) to a folder, then

           Loop and FIND the next ItemType and export an Excel File to the same folder

           until all the ItemTypes are done

           I dont even know where to start.. Can you help.?

        • 1. Re: Export by Item Type
          philmodjunk

               You start with a script of course. wink

               Answers to the following questions will help me adjust the level of detail in my next response:

               What parts of this process do you already know how to do?

               Have you created scripts before?

               If so:

               Do you know how to set up Export Records with a $path variable to specify the location and name of the file to be created by the export records step?

               Do you know how write a script to perform a find that uses data in a field as criteria used in the find?

               Will you be exporting all the records of each type each time or just the records of each type that have been added since the last export?

               And please note that FileMaker cannot append rows of data to an existing excel file.

          • 2. Re: Export by Item Type
            dansan500

                 This is how I would do one item type called Valve

                 _______________________________________

                 Go to Layout ["INV01" (INV01)]

                 Enter Find mode []

                 Set Field [INV01::Item type; "Valve"]

                 Perform Find []

                 Export Records ["Valve.xls"; Unicode (UFT-16)]

                 ________________________________________

                 I don't know how to loop it. without me having to write a script for each Item Type..

                 i have used $path veriables before

            • 3. Re: Export by Item Type
              philmodjunk

                   Ok, that saves me some time as I can skip some of the details.

                   I have a script that creates a "summary record" in another table from a "line items" type of table. The script "boils down" over a 1000 records each time it runs into a dozen or less records in the summary table. Needless to say, summary reports from this summary table are many times faster to pull up than when I do the same thing from millions of records in the Line Items table. wink

                   The method that I am about to suggest is adapted from that script. It has to find all records with the same item specified out of today's receipts, but only those items actually listed on a purchase order for that date.

                   You didn't answer this question: "Will you be exporting all the records of each type each time or just the records of each type that have been added since the last export?"

                   So there'll need to be some adjustments made to reflect which answer to that question is correct for what you need:

                   Steps in this psuedocode that start with # represent multiple lines of actual scripting left to you to provide. I am also assuming that you have a separate record for each item and that each item is listed in the same field of one of these separate records.

                   Loop
                      Enter Find Mode[]
                      Set Field [ Invo1::Flag ; "=" ]
                      Set Error Capture [on]
                      Perform Find[]
                      Exit Loop If [ Get ( FoundCount ) = 0 // all records have been exported ]
                      Set Variable [$Item ; Value: INV01::Item type]
                      Enter Find Mode []
                      Set Field [ INV01::Item type ; $Item ]
                      Perform Find []
                      #export found set of these records to Excel here
                      Replace Field Contents [no dialog; Invo1::Flag ; 1 ]
                   End Loop
                   Show All Records
                   Replace Field Contents [no dialog; Invo1::Flag ; "" ]

                   This assumes that you export all records all the time. If you only export records that have been added since the last time that you ran this script, leave out the last two script steps that reset the flag field so that no records are marked for the next time around, and add a set field step:Set Field [ Invo1::Flag ; "=" ] as part of the criteria for the second find that isolates records of a particular type.

              • 4. Re: Export by Item Type
                dansan500

                     Looking good, here is the answer to your question: 

                     Each export will be a separate Excel file containing the Found ItemType group. I would like each file save as an excel file by the found set (ItemType) ItemType (example = Valve) the next one would be (O-Ring) and so on until complete.

                     Separate Excel files in the same Folder.

                     The script will run at the end of each day to pick up any new groups (ItemTypes)

                • 5. Re: Export by Item Type
                  philmodjunk

                       Sorry, but it does not answer my question. Let me rephrase:

                       On November 1st, you run this script and export all data to the separate excel files. In the table, there were 20 records  for "valves" and so you get a 20 row excel file. On December 1st, you run the same script again, but 2 more records of type "valves" have been added since November 1st.

                       Should the Excel file for "valves" contain 22 rows--all the records of type "valve" found in your table or just 2 rows--the two added since the last export of your data?

                       My script example assumes that you want all 22 rows of this example. If you want just the 2 added records, follow the directions for modifying the script to produce that result.

                  • 6. Re: Export by Item Type
                    dansan500

                         You are correct, the Excel file will replace the old one each time the script is run with however many records are contained in that found set on that day.

                         Question: do I create a field called "flag"

                    • 7. Re: Export by Item Type
                      philmodjunk

                           Answer: Yes. It's what keeps the script from exporting the same set of records twice.

                      • 8. Re: Export by Item Type
                        dansan500

                             Thank you VERY much....

                        • 9. Re: Export by Item Type
                          philmodjunk

                               But a note of caution: This script will not work correctly if another user has opened one of these records for editing at the time the script runs. The "edit lock" on the record will keep the flag field from being either set or cleared depending one exactly when the user opens the record for editing.

                               This script should only be run at a time when you are sure no other users might be locking one of these records--such as using a server schedule to run it after hours.

                               If you have to run this script when others might be editing the records in this table, let me know and I'll describe a different way to track which records have been exported.