11 Replies Latest reply on Apr 21, 2011 7:45 AM by EmilyShep

    Create a Script to export a .txt file

    EmilyShep

      Title

      Create a Script to export a .txt file

      Post

      I am currently using a script to export my records into an excel sheet and from there I am manually saving it as a tab-delimited text file. I cannot using the tab-delimted text file .tab that fmp uses since it cannot be read by the program the report is used for.

      If there is a way that I can make a script to give me a .txt output file that would be great. Any help is appreciated.

      Thanks.

        • 1. Re: Create a Script to export a .txt file
          philmodjunk

          When you specify the file name during export, include .txt and the file should be exported with that file extension.

          • 2. Re: Create a Script to export a .txt file
            EmilyShep

            Alright, I want to save the .txt on the desktop using variables in the name. This is what I have in the script, I know its wrong because its not working but I'm not really sure how its supposed to be written out:

            file:Desktop/$Project Number - WE - $Work Date.txt

            Where did I go wrong?

            • 3. Re: Create a Script to export a .txt file
              philmodjunk

              Do it this way:

              Set variable [$Path ; Value: "file:" & Get ( DesktopPath ) & " & $Project Number& " - WE - " & $Work & " Date.txt"]

              Then enter $Path in the file reference dialog instead of the above expression.

              • 4. Re: Create a Script to export a .txt file
                EmilyShep

                Ok it made the text file (YAY!!) but the file name shows the &'s and $'s instead of putting in the variable... Do I need to define these variables first?

                Project Number and Work Date are fields I wanted to pull the information from, will I have a problem with Work Date considering it is 04/19/2011?

                • 5. Re: Create a Script to export a .txt file
                  philmodjunk

                  There's a typo in the above example that puts quotes in the wrong place so that the variables are enclosed in quotes instead of outside the quoted text. (I spotted the reference to the desktop at the last second and goofed when I edited the line to use Get (DesktopPath). Embarassed)

                  workDate will be a problem as / is the character for dividing folder names in the file path.

                  Is use this expression to change the / to - in the date: Substitute ( $WorkDate ; "/" ; "-" )

                  And I see my earlier expression misinterepeted the name of your variable. Correcting for both of these gives you:

                  Set variable [$Path ; Value: "file:" & Get ( DesktopPath ) & $Project Number& " - WE - " & Substitute ( $Work Date ; "/" ; "-" ) & ".txt"]

                  • 6. Re: Create a Script to export a .txt file
                    ryan

                    the & and $ appearing are due to incorrect possitioning of your quotation marks:

                    "file:" & Get ( DesktopPath ) & " & $Project Number& " - WE - " & $Work & " Date.txt"

                    Should be: 

                    "file:" & Get ( DesktopPath ) & $Project Number & " - WE - " & $Work Date & ".txt"

                    and yes the "/" in your date field will be a problem.

                    Since it appears you are already using a variable for the Work Date, use the Subsitute function to either remove the "/" and render it as "04202011" or you could substitute the "/" with "_" rendering it as "04_20_2011", etc.

                    • 7. Re: Create a Script to export a .txt file
                      EmilyShep

                      Ok almost there! The variables aren't showing up in the file name... it shows blanks - WE - .txt... do I need to define the variables anywhere or will it know to look in the fields?

                      • 8. Re: Create a Script to export a .txt file
                        philmodjunk

                        Variables won't have values unless you use set field or a Let function to assign values to them. If this is data in a field of your current record, you can just refer to the field in your calculation without using a variable.

                        • 9. Re: Create a Script to export a .txt file
                          EmilyShep

                          Ok I still can't get the record from the field to appear in the title. Does it need to be a global field for this to work?

                          "file:" & Get ( DesktopPath ) &Time Sheets::Project Number& "-Payroll WE -"  & Substitute ( Time Sheets::Work Date ; "/" ; "-" ) & ".txt"

                          • 10. Re: Create a Script to export a .txt file
                            philmodjunk

                            I'm guessing that you are not on the correct layout in order for this to work as written.

                            Is the curent layout basd on the Time Sheets table occurrence? (Show Records From in Layout Setup...)

                            Do you have the correct record current at the time this step executes?

                            If You have FileMaker advanced, enable the script debugger and data viewer and then run this script stepping through these steps and checking to see what values are in Time Sheets::Project Number and Time Sheets::Work Date at the time this step executes.

                            If you do not have advanced, you can put these two steps just before it:

                            Refresh Window
                            Show Custom Dialog [" Project Number: " & Time Sheets::Project Number & " Work Date: " & Time Sheets::Work Date ]

                            to see what values are in the current record and to see what record is current at the point where this script step executes.

                            • 11. Re: Create a Script to export a .txt file
                              EmilyShep

                              You are correct as usual! I was calling from the wrong layout. It works beautifully now! Thanks again for your help, really appreciate it!