FTD SORTING

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'

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.

i did already try that Joe - it didn't work for me.

It looks like with FTD you're using a network share path but in the FTWIZARD you're writing to drive C:  I don't know if FTD understands network shares.  Try mapping your share to a drive letter and see if that works.

Both the commands work, one sorts the items one does not. Has nothing to do with the network.

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'

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'

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

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

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

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