6 Replies Latest reply on Sep 29, 2010 8:24 AM by cmspe

    Exporting records scripting problems



      Exporting records scripting problems


      I'm trying to write a script that will export student data.  The first export in the script is one record per student.  Not a hard thing to figure out.  The second export is more difficult.  Each student can have zero to multiple records in the second export.  Here is the script I have written so far:

      Go to Layout ["Export" (Students)]

      Perform Find [Restore] This finds all the students who are not assigned a teacher


      Go to Related Record [Show only related records; from table: "Daily Records 4"; Using layout: "Daily Export" (Daily Records); New Window] Problem, if there are no related records, the script stops.

      Export Records [No dialog; "Daily Export.csv"] *If "Daily Export.csv" already exists, is there a way to append a number (ie "Daily Export2.csv") on the end instead of replacing the file?

      Delete All Records []

      Close Window [Current Window]

      Go to Record/Request/Page [Next; Exit after last]

      End Loop

      Export Records [No dialog; "Unassigned.csv"] *See previous note.

      Delete All Records[]

      Go to Layout ["Home" (Teachers)]

      This works except where there are no related records (line 4) and the problem of overwriting previous exports (lines 5 and 10).  How do I write a "Exit Loop if" or other script to compensate for this occurrence and is it possible to have a custom export name other than having the dialog come up?

        • 1. Re: Exporting records scripting problems

          There are several issues in that script. Number one issue is that you have a Go To Related Records Step followed by a Delete All Records step. If there are no related records, that combination can result in all records being deleted from a completely different table.

          Whenever you use GTRR in a script with following steps that modify data, you should always check to see if there were any related records for GTRR to go to so you can avoid such a potentially disastrous outcome.

          Since you can't append exported data to an existing file, you should pull together all the student data in one found set and perform a single export of this data.

          Perform Find [Restore]
          Go To Related Records [Show only related records; match found set;From table: "Daily Records 4"; Using Layout: "Daily Export" (Daily Records) ; New Window]
          If [ Get ( LastError ) = 0 /* there were related records to go to */
             #If necessary, you can add a Sort step here to organize the records for export
             Export Records [No dialog; "Daily Export.csv"]
             Delete All Records[]
             Close Window [current window]
          End If

          What makes this work is the "match found set" option to Go to the related records for all the students that you just found.  For more on Go To Related Records, see:  The Complete Go To Related Record

          • 2. Re: Exporting records scripting problems

            Everything worked great at first, when every record in the found set had related records.  The second time, only one record from the found set had related records and the other three did not and "Last error 101" popped up, record is missing, causing the If argument to come up false and therefore End If.

            • 3. Re: Exporting records scripting problems

              That is unfortunate! I truly did not expect that result. Match found set is an option that can put a heavy load on your system so you don't want to use it when strictly necessary, but this is a case where it makes sense.

              The resulting error code differs based on whether the current record has related records or not. I just ran a test with two parent records, one with related child records and one without. If I ran the above type script with the record that has related child records as the current records--no error. If I ran the script with the other record current, I got error 101 as you did.

              We need a different test to confirm successful execution of GTRR. Since the step changes specifies a different layout, use this:

              If [ Get ( LayoutName ) = "Daily Export /* there were related records to go to */]

              • 4. Re: Exporting records scripting problems

                The new test worked great.  Now my other dilemma.  When exporting, I want the script to determine what kind of file, where to put it and what to name it because not all of the other teachers will know how to do this.  However, if I do this and there is already an exact file in the same location, it will replace it instead of appending a 1 (ie. Daily Export 1.csv) to it.  Is this a dilemma I'm going to have to live with?  Many of these issues are moot if the school had wifi available to all in order to access a shared file.

                • 5. Re: Exporting records scripting problems

                  You can use a variable to build a filename for export. I suggest including the date or a serial number as part of the filename so that it is always unique.

                  In order to specify different filetypes, you'd need to also have a series of Export Records steps--one for each desired file type so that you can enclose them in If  blocks that control which export is performed each time.

                  Filename example:

                  Set Variable [$Path ; Value:  "file:" & Get ( DesktopPath ) & "FileName " & Substitute ( Get ( CurrentDate) ; "/" ; "-" ) & ".csv"]

                  Then set up The Export records step to export as you want it to an actual file. Once it's set up as you want it, click the upper Specify button for this step and type in $Path as the first line of your external data source references.

                  • 6. Re: Exporting records scripting problems

                    It worked perfect, first time.  I've adjusted the script a little to allow for me to export it to a network path.  Thanks a bunch.