Append Date and Time to VI export using Perform function?

SOLVED

Running SAGE 100 v2018 and exporting a file using VI that requires the current date and time be appended to the file.  Is there a way to accomplish this using the Perform option within the VI Export job or do I need to create a separate batch process once the file has been created?  I'm not well versed in the Perform function and if it can be done in Perform, it would be helpful to see an example.  Thank you.

  • 0

    You should be able to use this answer to get what you need. 

    https://www.sagecity.com/support_communities/sage100_erp/f/sage-100-distribution-manufacturing-and-internet-modules/166889/is-there-a-way-to-add-a-record-count-at-the-end-of-a-vi-export-job/427880#427880

    However, instead of writing "Records Exported: "+STR(RecordsPassed), you would write the date and time however you want it formatted.  You could use the ProvideX DTE function for this purpose or use some of the properties or methods of the coSession object.  If you use DTE, it could be something like DTE(0:"%Y-%Mz-%Dz %hz:%mz:%sz %P")

    Here's a link with more info for the DTE function.

    DTE( ) Convert Date (pvxplus.com)

  • 0 in reply to David Speck

    Wouldn't the Date and Time be added within the file rather than appended to the file name if I replace the information where "Records Exported..." resides?  I'm trying to append the date and time to the actual file name...I'd like the file name to be iPay_Code_202105111103.txt but I currently just have placeholders for the file name when I create it in VI so it currently looks like iPay_Code_YYYYMMDDHHMM.txt.

  • 0 in reply to Paul Smith - Bretthauer

    I misunderstood your original post.  I think there is code floating around on here that renames the file.  I'll try to post something in a bit.

  • 0 in reply to David Speck

    There is, but for import files.  Renamefile.pl ...but it would have to be updated for exports, and I wouldn't know how to do that.

  • +1 in reply to Kevin M
    verified answer

    The following should work for you.  There is a ProvideX RENAME directive but I haven't gotten a chance to test it on Advanced installs but the code below does work on Standard and Advanced installs.  This code does not rename the file but rather copies it using the current file name as the source and the resulting file name in the NEWFILENAME$ variable as the destination.

     LET MAS90_PATH$=%SYS_SS'PATHROOT$
     IF %SYS_SS'CS=1 THEN { LET MAS90_PATH$=%SYS_SS'PATHCSROOT$ }
     LET WDX_OPTION$=COSESSION'WDX$
     IF IMPORTFILEONHOST$="Y" THEN { LET WDX_OPTION$="" }
     IF IMPORTFILENAME$<>"" THEN {
     LET NEWIMPORTFILENAME$=MID(IMPORTFILENAME$,1,POS("."=IMPORTFILENAME$,-1)-1)+"_"+DTE(0:"%Y-%Mz-%Dz %hz%mz%sz %P")+MID(IMPORTFILENAME$,POS("."=IMPORTFILENAME$,-1))
     ! LET NEWIMPORTFILENAME$=MAS90_PATH$+"Home\TextOut\"+MID(NEWIMPORTFILENAME$,POS("\"=NEWIMPORTFILENAME$,-1)+1)
     ! LET NEWIMPORTFILENAME$="C:\"+MID(NEWIMPORTFILENAME$,POS("\"=NEWIMPORTFILENAME$,-1)+1)
     LET S_CMD$=WDX_OPTION$+"CMD /E:ON /V:ON /S /Q /C COPY /Y """+IMPORTFILENAME$+""" """+NEWIMPORTFILENAME$+""""
     INVOKE HIDE WAIT S_CMD$
     }

    Exclamation points comment out lines, I have commented out two lines in the above code to show how they can be used to change the directory. 

    The first line commented out copies the new file name to the MAS90\Home\TextOut folder.

    The second line commented out copies the new file name to the C:\ folder.

    If you leave both lines commented out, the file is simply copied into the original folder with the new file name.

    Put the code in a text file somewhere accessible by users that run Sage 100, I recommend the MAS90\CM\Script folder on the Sage 100 server.  You can optionally change the extension to something like ".pl" instead of ".txt".  Then in your VI job, click the Perform button, set the perform type to "On Completion", set the sequence to something that isn't in use already, hit the flashlight button to see if there is other perform logic in use, then enter the relative path to the file containing the code.

    EDIT: Added 2 lines at the beginning of the code to handle getting correct root path to the MAS90 directory for the Sage 100 server.

  • +1 in reply to David Speck
    verified answer

    That worked perfectly...thank you, David.

  • Couldn't get the movement of the renamed file to work (the lines that are remarked out) after removing the "!" but it that would just have been icing on the cake.

  • 0 in reply to Paul Smith - Bretthauer

    You would only uncomment line 5 OR 6, not both at the same time.  Line 6 would copy it to C:\ whereas line 5 would copy it to the MAS90\Home\TextOut folder.  However, now that i think about it, that MAS90_PATH$ variable might not contain the UNC path if on Advanced.  Below is a revised version that will use the PathCSRoot$ property if the CS property is equal to 1.

     LET MAS90_PATH$=%SYS_SS'PATHROOT$
     IF %SYS_SS'CS=1 THEN { LET MAS90_PATH$=%SYS_SS'PATHCSROOT$ }
     LET WDX_OPTION$=COSESSION'WDX$
     IF IMPORTFILEONHOST$="Y" THEN { LET WDX_OPTION$="" }
     IF IMPORTFILENAME$<>"" THEN {
     LET NEWIMPORTFILENAME$=MID(IMPORTFILENAME$,1,POS("."=IMPORTFILENAME$,-1)-1)+"_"+DTE(0:"%Y-%Mz-%Dz %hz%mz%sz %P")+MID(IMPORTFILENAME$,POS("."=IMPORTFILENAME$,-1))
     ! LET NEWIMPORTFILENAME$=MAS90_PATH$+"Home\TextOut\"+MID(NEWIMPORTFILENAME$,POS("\"=NEWIMPORTFILENAME$,-1)+1)
     ! LET NEWIMPORTFILENAME$="C:\"+MID(NEWIMPORTFILENAME$,POS("\"=NEWIMPORTFILENAME$,-1)+1)
     LET S_CMD$=WDX_OPTION$+"CMD /E:ON /V:ON /S /Q /C COPY /Y """+IMPORTFILENAME$+""" """+NEWIMPORTFILENAME$+""""
     INVOKE HIDE WAIT S_CMD$
     }

  • 0 in reply to David Speck

    I changed the Text Out location to a different location within MAS and it worked.  Thanks again, David.