Hello fellow PICKIES,
I am trying to use FTD from Accuterm to run a file download. I need to select and sort the items it is going to list into an excel spreadsheet. I can get this to work through the wizard, but not by passing in the data statements in a program. Help?
This works:
05 ED VOC K66
Top of "K66" in "VOC", 63 lines, 983 characters.
*--: p
001: [Job]
002: FileName=mvdb://MultiValue Host*$52370\\*\\K66
003: TransferProtocol=Kermit
004: TransferMode=Text
005: PreserveExtension=Yes
006: HeaderRow=1
007: Delimiter=Tab
008: Direction=Download
009:
010: [UL]
011: TransferType=File
012: HostFile=
013: SelectOption=SelectAll
014: SelectString=
015: Database=Excel
016: HostItemIds=
017: DosPath=
018: DosFiles=
019: WorkbookName=
020: Worksheet=
021: Range=
022: AccessMDBName=
023: AccessTableName=
*--: p
024: AccessConnectString=
025: TextFileName=
026: IncludeAllAttributes=False
027: Attributes=
028: AutoID=False
029: SuppressItemID=False
030: FirstRowHasColNames=False
031: SelectByName=False
032: AutoIDPrefix=
033: AutoIDStart=0
034: Overwrite=No
035: ExplodeMV=No
036:
037: [DL]
038: TransferType=Data
039: HostFile=SIL
040: SelectOption=SelectStatement
041: SelectString=SSELECT SIL BY F120 BY F1
042: Database=Excel
043: HostItemIds=
044: DosPath=
045: DosFiles=
046: WorkbookName=C:\\TEMP\\JJ
This does not:
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA 'SSELECT SIL BY F120 BY F1' ;* ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' PP!A1'
DATA 'F1 F2 F3 F120 F126 F124 F125 F13 F121 F129'; * ATTBS TO LIST
DATA 'Y' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD'
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
I Kathleen,
Try selecting the records before you execute the FTD command and just leave the DATA field blank for that line.
EXECUTE 'SSELECT SIL BY F120 BY F1'
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA ''
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' PP!A1'
DATA 'F1 F2 F3 F120 F126 F124 F125 F13 F121 F129'; * ATTBS TO LIST
DATA 'Y' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD'
FTD will use the select list if it's active.
------------------------------
Joe Goldthwaite
Consultant
Phoenix AZ US
------------------------------
Both the commands work, one sorts the items one does not. Has nothing to do with the network.
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Kathleen,
Instead of executing FTD with data statements, I recommend calling the FTSEND subroutine and passing data. That is what the FTD command does. You should have the signature for FTSEND to get the required parameters but it is essentially what you are putting into the DATA statements. In my way of thinking, it gives you more control over the process. Also, you are not having to create a new execution environment as it will run in the same environment as your program since you would be calling a subroutine. I believe that using SSELECT prior to calling the subroutine will work. I hope that makes some sort of sense.
Dick
------------------------------
Richard Thiot
President
Maverick Systems
Plano TX US
------------------------------
Hello fellow PICKIES,
I am trying to use FTD from Accuterm to run a file download. I need to select and sort the items it is going to list into an excel spreadsheet. I can get this to work through the wizard, but not by passing in the data statements in a program. Help?
This works:
05 ED VOC K66
Top of "K66" in "VOC", 63 lines, 983 characters.
*--: p
001: [Job]
002: FileName=mvdb://MultiValue Host*$52370\\*\\K66
003: TransferProtocol=Kermit
004: TransferMode=Text
005: PreserveExtension=Yes
006: HeaderRow=1
007: Delimiter=Tab
008: Direction=Download
009:
010: [UL]
011: TransferType=File
012: HostFile=
013: SelectOption=SelectAll
014: SelectString=
015: Database=Excel
016: HostItemIds=
017: DosPath=
018: DosFiles=
019: WorkbookName=
020: Worksheet=
021: Range=
022: AccessMDBName=
023: AccessTableName=
*--: p
024: AccessConnectString=
025: TextFileName=
026: IncludeAllAttributes=False
027: Attributes=
028: AutoID=False
029: SuppressItemID=False
030: FirstRowHasColNames=False
031: SelectByName=False
032: AutoIDPrefix=
033: AutoIDStart=0
034: Overwrite=No
035: ExplodeMV=No
036:
037: [DL]
038: TransferType=Data
039: HostFile=SIL
040: SelectOption=SelectStatement
041: SelectString=SSELECT SIL BY F120 BY F1
042: Database=Excel
043: HostItemIds=
044: DosPath=
045: DosFiles=
046: WorkbookName=C:\\TEMP\\JJ
This does not:
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA 'SSELECT SIL BY F120 BY F1' ;* ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' PP!A1'
DATA 'F1 F2 F3 F120 F126 F124 F125 F13 F121 F129'; * ATTBS TO LIST
DATA 'Y' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD'
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Clearly not many others are using this feature, as no one seemed to know the answer :( I ended up reading the code to find it...the answer is a greater than mark...
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '>SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' ;* ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD'
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Clearly not many others are using this feature, as no one seemed to know the answer :( I ended up reading the code to find it...the answer is a greater than mark...
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '>SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' ;* ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD'
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Hi Kathleen -
Sorry I have not responded earlier. I think Joe Goldthwaite suggested that an active select list will be used by FTD if you enter null for the source item list prompt. That should work and if it does not work for your MV platform, then it is a bug that we need to fix. I was surprised to see that the '>' prefix for item list, to run a SELECT command, was not documented. For a saved list, you can use a '(' prefix. I will submit a bug report to document '>' for FTD. I don't recall which MV platform you are using.
Can you respond to this post with your MV platform, and any account flavor (e.g. on Universe, is the account "ideal" or "pick" or ???, on Unidata do you use BASICTYPE "P" or BASICTYPE "U" ?)
Dick Thiot suggested calling FTSEND instead of EXECUTE "FTD". That is almost correct. FTSEND / FTRECV are callable subroutines that perform the same task as the FT command. FTEXPORT / FTIMPORT are callable subroutines that perform the same task as FTD. I think using the subroutines is more reliable and the code is more clear. Under the hood, the subroutine or command or wizard all call the same low level routines. The subroutines include documentation as comments in the source code.
Thanks, Pete
------------------------------
Peter Schellenbach
Rocket Internal - All Brands
------------------------------
Hi Kathleen -
Sorry I have not responded earlier. I think Joe Goldthwaite suggested that an active select list will be used by FTD if you enter null for the source item list prompt. That should work and if it does not work for your MV platform, then it is a bug that we need to fix. I was surprised to see that the '>' prefix for item list, to run a SELECT command, was not documented. For a saved list, you can use a '(' prefix. I will submit a bug report to document '>' for FTD. I don't recall which MV platform you are using.
Can you respond to this post with your MV platform, and any account flavor (e.g. on Universe, is the account "ideal" or "pick" or ???, on Unidata do you use BASICTYPE "P" or BASICTYPE "U" ?)
Dick Thiot suggested calling FTSEND instead of EXECUTE "FTD". That is almost correct. FTSEND / FTRECV are callable subroutines that perform the same task as the FT command. FTEXPORT / FTIMPORT are callable subroutines that perform the same task as FTD. I think using the subroutines is more reliable and the code is more clear. Under the hood, the subroutine or command or wizard all call the same low level routines. The subroutines include documentation as comments in the source code.
Thanks, Pete
------------------------------
Peter Schellenbach
Rocket Internal - All Brands
------------------------------
Hi Pete -
selecting the file before running the FTD did not work on my system, as I mentioned. I tried that before adding my question to the forum of course, as that would be an obvious solution had it worked.
Here is our release info:
Top of "RELLEVEL" in "VOC", 5 lines, 32 characters.
001: X
002: 11.3.4
003: PICK
004: PICK.FORMAT
005: 11.3.4
I did also see the subroutine version to execute this but that wasn't my question really - as there are always many ways to do the same thing. The code was easy enough to follow so I found my answer.
Thanks
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Hi Pete -
selecting the file before running the FTD did not work on my system, as I mentioned. I tried that before adding my question to the forum of course, as that would be an obvious solution had it worked.
Here is our release info:
Top of "RELLEVEL" in "VOC", 5 lines, 32 characters.
001: X
002: 11.3.4
003: PICK
004: PICK.FORMAT
005: 11.3.4
I did also see the subroutine version to execute this but that wasn't my question really - as there are always many ways to do the same thing. The code was easy enough to follow so I found my answer.
Thanks
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Hi Kathleen -
Thank you for the quick response. I was able to reproduce the problem, and I believe it is due to the way select lists are handled in Universe Basic. Looking at how AccuTerm handles the '>' prefix, it appears that you need to use the RTNLIST clause on the original EXECUTE "SELECT" statement, then use the PASSLIST clause on the EXECUTE "FTD" statement. From a TCL command line, an active select list is passed to the FTD command, but the same does not appear to be true when the EXECUTE "SELECT" is executed within a program. Universe also appears to allow the first DATA statement after EXECUTE "SELECT" to be the command to run after the selection. Here are two alternatives that should work with Universe, for future reference:
EXECUTE 'SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' RTNLIST IDLIST;* ITEM LIST
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '' ;* USE ACTIVE SELECT LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD' PASSLIST IDLIST
DATA 'FTD' ;* RUN FTD AFTER SELECT
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '' ;* USE SELECTED ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' ;* ITEM LIST
------------------------------
Peter Schellenbach
Rocket Internal - All Brands
------------------------------
Hi Kathleen -
Thank you for the quick response. I was able to reproduce the problem, and I believe it is due to the way select lists are handled in Universe Basic. Looking at how AccuTerm handles the '>' prefix, it appears that you need to use the RTNLIST clause on the original EXECUTE "SELECT" statement, then use the PASSLIST clause on the EXECUTE "FTD" statement. From a TCL command line, an active select list is passed to the FTD command, but the same does not appear to be true when the EXECUTE "SELECT" is executed within a program. Universe also appears to allow the first DATA statement after EXECUTE "SELECT" to be the command to run after the selection. Here are two alternatives that should work with Universe, for future reference:
EXECUTE 'SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' RTNLIST IDLIST;* ITEM LIST
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '' ;* USE ACTIVE SELECT LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'FTD' PASSLIST IDLIST
DATA 'FTD' ;* RUN FTD AFTER SELECT
DATA 'S' ;*RECEIVE
DATA 'K' ;*KERMIT
DATA 'SIL' ;* PICK FILE
DATA '' ;* USE SELECTED ITEM LIST
DATA '\\\\kvfileserver\\bproddistcharges$\\':RPT.NAME:'.xlsx!':CUST:' ITEMS!A1'
DATA 'F1 F2 F3 F5 F9 F6 F11 F54 F13 F131 F130'; * ATTBS TO LIST
DATA 'C' ; *CREATE HEADERS
DATA '' ; * EXPLODE
EXECUTE 'SSELECT SIL ':CUST.SEL:' AND ':DATE.SEL:' BY F1 BY F5' ;* ITEM LIST
------------------------------
Peter Schellenbach
Rocket Internal - All Brands
------------------------------
Thanks Pete, I appreciate you showing me why the select didn't work for me. I do like finding answers ;)
I did already move on using the '>' and select within the data statements and that is working so I will likely not change it at this time. I did also feel calling the subroutines would be even better but I have no example of that. Do you have a simple working example I could try?
And lastly, I'd like your opinion on reporting. Right now this company is using FTD as a reporting tool. They are literally writing out each line of an excel to a work file on universe and then FTDing the lines to the spreadsheet. This seems less than ideal to me. What reporting tool do you most often use on PICK? Whether specifically accuterm compatible/ feature or not...
Thank you
Kathleen
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Thanks Pete, I appreciate you showing me why the select didn't work for me. I do like finding answers ;)
I did already move on using the '>' and select within the data statements and that is working so I will likely not change it at this time. I did also feel calling the subroutines would be even better but I have no example of that. Do you have a simple working example I could try?
And lastly, I'd like your opinion on reporting. Right now this company is using FTD as a reporting tool. They are literally writing out each line of an excel to a work file on universe and then FTDing the lines to the spreadsheet. This seems less than ideal to me. What reporting tool do you most often use on PICK? Whether specifically accuterm compatible/ feature or not...
Thank you
Kathleen
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
I wrote a subroutine last month to automate the FTPICK command. It might help to see how it works.
(I tried attaching the file but it doesn't seem to work on my browser under Linux).
SUBROUTINE SEND.FTPICK.SUB(FILE.NAME, ITEM.LIST, SUCCESS.MSG, ERROR.MSG)
* PARAMETERS:
* FILE.NAME = NAME OF FILE TO SEND
* ITEM.LIST = ATTRIBUTE DELMITED LIST OF ITEMS TO SEND
* SUCCESS.MSG = ATTRIBUTE DELIMITED LIST OF SUCCESS MESSAGES IF SUCCESSFUL
* ERROR.MSG = ERROR MESSAGE IF FAILED
* WRITTEN BY JEG 2023-05-12 USING FTBP FTPICK AS A BASIS FOR AUTOMATING FILE TRANSFERS
DIM PARAMS(30),VARS(26),CLIENV(10)
EQU TXT.FLG.PARAM TO PARAMS(13)
EQU DIR.FLG.PARAM TO PARAMS(21)
EQU PRO.FLG.PARAM TO PARAMS(25)
EQU ICNT TO VARS(1)
EQU BCNT TO VARS(2)
EQU IDMODE TO VARS(3)
EQU AUTO TO VARS(4)
EQU HDR TO VARS(5)
EQU DISC TO VARS(6)
EQU TARGET TO VARS(7)
EQU DCHK TO VARS(8)
EQU FILE TO VARS(11)
EQU FNAME TO VARS(12)
EQU ID TO VARS(13)
EQU IDLST TO VARS(14)
EQU IDS TO VARS(16)
EQU ISIZ TO VARS(17)
EQU ISEQ TO VARS(18)
EQU ITEM TO VARS(19)
EQU XSIZ TO VARS(20)
EQU PATH TO VARS(21)
EQU FN TO VARS(22)
EQU FNLST TO VARS(23)
EQU DFILE TO VARS(24)
EQU FNS TO VARS(25)
EQU MD TO VARS(26)
*
EQU CLI.PLATFORM TO CLIENV(1)
EQU CLI.PRODUCT TO CLIENV(2)
EQU CLI.RELEASE TO CLIENV(3)
EQU CLI.CAPAS TO CLIENV(4)
EQU CLI.SVCS TO CLIENV(5)
EQU CLI.SERIAL TO CLIENV(6)
EQU CLI.LICENSE TO CLIENV(7)
EQU CLI.USER TO CLIENV(8)
EQU CLI.VERSION TO CLIENV(9)
EQU CLI.CONFIG TO CLIENV(10)
NO.PAGE=@(0,0);* DISABLE PAGINATION (QM, ON)
**************************************************************************
*
* VALIDATE CONFIGURATION & INITIALIZE PARAMETERS
*
**************************************************************************
*
SUCCESS.MSG = ''
ERROR.MSG = ''
MAT VARS=''
CALL FTINITEX('',MAT PARAMS,MAT CLIENV,FV,STAT)
IF STAT<>'' THEN
ERROR.MSG = STAT
RETURN
END
OPEN 'MD' TO MD ELSE
OPEN 'VOC' TO MD ELSE
ERROR.MSG = 'CANNOT OPEN MD'
RETURN
END
END
DCHK=0
**************************************************************************
*
* PROMPT FOR DESIRED ACTION
*
**************************************************************************
* TEST ACCUTERM CLIENT CAPABILITIES
IF CLI.PLATFORM=0 THEN
ERROR.MSG = 'FTPICK requires AccuTerm to run!'
RETURN
END
IF INDEX(CLI.CAPAS,'Z',1) THEN
ERROR.MSG = 'Upper case / lower case inversion must be disabled to run FTPICK. Try typing "PTERM CASE NOINVERT" before running FTPICK.'
RETURN
END
*
IF (INDEX(CLI.CAPAS,'D',1) + INDEX(CLI.CAPAS,'U',1)) = 0 THEN
ERROR.MSG = 'This version of AccuTerm does not support file transfers.'
RETURN
END
* FORCING SEND. CHECK TO SEE IF IT'S SUPPORTED
IF INDEX(CLI.CAPAS,'D',1) = 0 THEN
ERROR.MSG = 'This version of AccuTerm does not support file downloads.'
RETURN
END
FNAME = FILE.NAME
IF FNAME[1,5] EQ 'DICT ' THEN
OPEN FNAME TO FILE ELSE
ERROR.MSG = 'COULD NOT OPEN ':FNAME
RETURN
END
DFILE = MD
END ELSE
OPEN FNAME TO FILE ELSE
ERROR.MSG = 'COULD NOT OPEN ':FNAME
RETURN
END
OPEN 'DICT',FNAME TO DFILE ELSE
ERROR.MSG = 'COULD NOT OPEN DICT ':FNAME
RETURN
END
IF FNAME EQ 'MD' THEN DCHK = 1 ELSE DCHK = 0
END
TARGET = FILE.NAME ;* TARGET FILE NAME
IDLST = ITEM.LIST
TXT.FLG.PARAM = 0 ;* FORCE TEXT MODE OFF
AUTO = 1 ;* FORCE AUTO TRANSFER
DIR.FLG.PARAM = 1 ;* FORCE DIRECTION?
SUBNAME='FTRDPICK'
CALL FTCONECT(STAT,SUBNAME,MAT PARAMS,MAT VARS,MAT CLIENV)
ECHO ON
IF STAT EQ '' THEN
STAT = 'Successful transfer.'
END
SUCCESS.MSG = 'Transfer status: ':STAT
IF ICNT THEN
SUCCESS.MSG<-1> = 'Transferred ':ICNT:' items, ':BCNT:' bytes.'
END
IF DISC THEN
SUCCESS.MSG<-1> = 'Discarded ':DISC:' bytes.'
END
RETURN
------------------------------
Joe Goldthwaite
Consultant
Phoenix AZ US
------------------------------
Thanks Pete, I appreciate you showing me why the select didn't work for me. I do like finding answers ;)
I did already move on using the '>' and select within the data statements and that is working so I will likely not change it at this time. I did also feel calling the subroutines would be even better but I have no example of that. Do you have a simple working example I could try?
And lastly, I'd like your opinion on reporting. Right now this company is using FTD as a reporting tool. They are literally writing out each line of an excel to a work file on universe and then FTDing the lines to the spreadsheet. This seems less than ideal to me. What reporting tool do you most often use on PICK? Whether specifically accuterm compatible/ feature or not...
Thank you
Kathleen
------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------
Hi Kathleen -
Please take a look at FTEXPORTDA. There is documentation in the source code, and in the help.
SUBROUTINE FTEXPORTDA(MODE,CONTENTS,ATTRS,REMOTE.FILE,ITEM.COUNT,BYTE.COUNT,STAT)
This is a variation of FTEXPORT, which is the subroutine equivalent of FTD. The difference with FTEXPORTDA is that the data you want to export (to Excel, etc.) is passed as a dynamic array in the CONTENTS argument. This eliminates the need to store the data in a temporary file before you run FTD. Just be aware that you are passing the entire contents as an argument, so for very large exports, this might not be practical. FTEXPORT and FTD read the data one record at a time and buffer the output into manageable chunks.
Thanks, Pete
------------------------------
Peter Schellenbach
Rocket Internal - All Brands
------------------------------
