Hi

I am trying to find options where I can do the following:

If I have a list of last names in my database, I want to be able to create an alphabetical list of only the alphabets that have last names. For example, if my last names in the database are Brown, Jackson, Jones, Williams and Young then I want a list(index) of B, J, W and Y.

Then when I select "J" in this index I want to go straight to Jackson and from there if I select "B" I want to skip straight to Brown.

Any ideas?

Thanks

you can do a define with

DEFINE FILE NAMELIST
FCHAR/A1=EDIT(LASTNAME,'9');
END
TABLE FILE NAMELIST
COUNT LASTNAMES
BY FCHAR
END

Now you will get a basic list

The second report is

DEFINE FILE NAMELIST
FCHAR/A1=EDIT(LASTNAME,'9');
END
TABLE FILE NAMELIST
PRINT LASTNAMES
WHERE FCHAR EQ '&FCHAR';
END

Create the drill down from the first one or you can build a PDF report with both reports in one.

Search here on "Drill Through" by Francis Mariani to see an example

DEFINE FILE CAR
CHAR/A1 = EDIT (CAR,'9')
END
TABLE FILE CAR
PRINT
     CHAR NOPRINT
     CAR
     BODYTYPE
WHERE CHAR EQ '&CHAR.CHAR.';
ON TABLE NOTOTAL
END




 DEFINE FILE CAR
CHAR/A1 = EDIT (CAR,'9')
END
TABLE FILE CAR
SUM
     CNT.CAR AS ''
BY CHAR
ON TABLE NOTOTAL
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TIMES NEW ROMAN',
     SIZE=10,
$
TYPE=DATA,
     FOCEXEC=app/drillcar(CHAR=N1),
$
ENDSTYLE
END 

Above the example of the drilldown as referenced in the above. I did it on the fly so to speak in just a few minutes.

Actually, you can achieve this in a single pass with the little used facility of BYTOC in HTML (this will give you a different result in EXL2K) -

APP PATH IBISAMP
DEFINE FILE EMPDATA
  LASTNAMEINIT/A1 = EDIT(LASTNAME,'9$');
END
TABLE FILE EMPDATA
PRINT LASTNAME
      FIRSTNAME
      MIDINITIAL
      TITLE
   BY LASTNAMEINIT NOPRINT
ON TABLE PCHOLD FORMAT HTML BYTOC
END

When run, the output will have a small "control" that looks like a table of contents icon (because that's what it provides ). Click on this to open the "control" and you will find your alphabetic listing. Click on an initial letter to show that portion of the report.

You can move the "control" around just like any other window so that you can see all of your data. The "control" default position on load is the top left hand corner of your report window.

T

Tony, this can work in HTML but in PDF?

Quite right Frank, but the original question did not ask for a specific output format.

T

Tony

how about this variant?

APP PATH IBISAMP
DEFINE FILE EMPDATA
  LASTNAMEINIT/A1 = EDIT(LASTNAME,'9$');
END
TABLE FILE EMPDATA
PRINT
     LASTNAME
     FIRSTNAME
     MIDINITIAL
     TITLE
BY LASTNAMEINIT NOPRINT
HEADING
"<LASTNAMEINIT <LASTNAMEINIT "
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='TAHOMA',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     TOC=1,
$
ENDSTYLE
END

Very good Frank, another one of those useful facilities often overlooked by the wide WF community.

Now tell me, what's your favorite bedtime reading? (apart from the style manual)

T

I harly read in bed, only do a sudoku...

Thanks everyone for your suggestions. I will try them out and see what works.

Frank,

Very interesting. I've never done that before, now I'll try to find a practical use for this technique.

Thanks,

where are you trying to do this? In other words is this in a report or do you want to do this in a self service front end so that you have a list box that will do it.

If it's the report I once wrote one that essentially turned a large amount of data into an expandable HTML document with the plus/minus thing. Click on on of them and it expanded or contracted. This was a combination of FOCUS and DHTML. If this is what you want I'd have to look up the code. If it's in the front end then it's straight DHTML you'll need to use.

quote:

expandable HTML document with the plus/minus thing

do you mean the ACCORDION

TABLE FILE CAR
BY COUNTRY
BY CAR
BY MODEL
BY BODYTYPE
BY SEATS
BY DEALER_COST
BY SALES
ON TABLE SET PAGE-NUM OFF
ON TABLE SET EXPANDABLE ON
END