3 Replies Latest reply on Oct 10, 2016 11:09 AM by philmodjunk

    Export to various excel files

    CarlosDíazIzquierdo

      I'm developing a script to help me managing my orders to suppliers.

      The idea is to identify all the pending orders and generate different files to send to the appropriate supplier by mail.

      Each excel file can have several references (products).

       

      The question: Is there a way to generate different excel files from a script, so that I can send mail to each supplier with the products we need from them, specified in an excel file attached?

       

      Thank you for your help in advanced

      Best regards,

       

      Carlos

        • 1. Re: Export to various excel files
          philmodjunk

          The short answer: Yes.

           

          The real question: "HOW would you do that", the answer to that is "it depends". It depends on exactly what do you need to send to each supplier. It will be simplest if you can send out spread sheets that each have the same columns, but different rows of data to each supplier, then the same export records step can be used to export the data in each case, after first performing a find to pull up a found set of the records needed for that supplier.

           

          The basic script would:

          a) Find records

          b) export records to an .xlsx file in the temporary folder using a $Path variable

          c) email the file using $Path to specify the attachment

           

          If possible, a looping script could loop through a list of suppliers and do the above set of steps for each supplier--probably with an added test to not export and email if no records for that supplier are found.

           

          If the actual columns of data need to be different, it's still possible, but more complicated to do.

          1 of 1 people found this helpful
          • 2. Re: Export to various excel files
            CarlosDíazIzquierdo

            Hi @philmodjunk

            Thank you for answering to my question!

            I think your approach is a clever way of solving my task

            All the files have the same columns, but different rows

            I was trying to automatize the whole process as we have relatively many suppliers. What I don't have quit clear is how to organize the looping (sorry, I'm not an expert :-)

            Thank you again!!

             

            Carlos

            • 3. Re: Export to various excel files
              philmodjunk

              The details will depend on the design of your database, and even then there are multiple options.

               

              If you have this relationship:

               

              Suppliers------<Orders

               

              Suppliers::__pkSupplierID = Orders::_fkSupplierID

               

              And you have date field called Orders::DateOrderExported

               

              You can:

              Go to the orders layout

              Loop

                 perform a find specifying = in the DateOrderExported field to find all unexported orders

                 Exit Loop if No records found

                 Use Find matching records to find all records for one supplier ID and = in that same date field to get all unsent orders for one supplier

                 Export the data

                 Email the spread sheet

                 Use Replace Field Contents to put today's date in the DateOrderExported field

              End Loop