John:
I really miss the 6.5++ "line compression" in labels. I would often
create a report as a 8 1/2 x 11" label to be able to easily do this.
Although a stored procedure and ifnull/ifexists is easy enough for
an address label (because you have at most 3 or 4 lines), what would
you do if you had 12? I'm doing a list for my high school reunion
where every student will have a name, but then any of the next 12
data items could be blank. Could you imagine the "if" construction for
that?
I decided to create a temp table with the name field, then 12 columns
named Line1, Line2, etc. with a NOTE datatype. My program steps
through each piece of data and fills in the "next" column of data. I'll
post my code below in case anyone would ever find this useful.
Karen
-- code is stripped of usual beginning and ending cleanup, initialization, etc.
DECLARE C1 CURSOR FOR SELECT +
StudentID,Fullname,MarriedName,Address,email,+
Hobbies,Occupation,GrandChildren, <etc., etc.> +
FROM students
OPEN c1
WHILE 1 = 1 THEN
FETCH C1 INTO vid, vfullname, vmarriedname, vaddress, vemail, +
vhobbies, voccupation, vgrand, <etc.>
IF SQLCODE = 100 THEN
BREAK
ENDIF
SET VAR vloop INTEGER = 2
INSERT INTO tmpbooklet (studentid, line1) VALUES .vid, .vfullname
IF vaddress IS NOT NULL THEN
SET VAR vcolumn = ('Line' + CTXT(.vloop) )
UPDATE tmpBooklet SET &vcolumn = .vaddress WHERE studentid = .vid
SET VAR vLoop = (.vLoop + 1)
ENDIF
IF vemail IS NOT NULL THEN
SET VAR vcolumn = ('Line' + CTXT(.vloop) )
UPDATE tmpBooklet SET &vcolumn = .vemail WHERE studentid = .vid
SET VAR vLoop = (.vLoop + 1)
ENDIF
IF voccupation IS NOT NULL THEN
SET VAR vcolumn = ('Line' + CTXT(.vloop) )
UPDATE tmpBooklet SET &vcolumn = .voccupation WHERE studentid = .vid
SET VAR vLoop = (.vLoop + 1)
ENDIF
-- YOU GET THE IDEA!
ENDWHILE
> Thanks Razzak, I am familiar with the process of
> using IF statements to
> filter out the blank lines in reports and labels.
> What I meant to ask is....
> Does version 7 label designer have a checkbox (like
> 6.5++) that will
> automatically suppress blank lines?
John, what I generally end up doing is creating a
stored procedure:
FormatAddress(pAddr1, pAddr2, pCity, pState, pZip)
RETURN NOTE
or whatever arguments are appropriate for the project
I'm working on. I put my IF logic in there, and call
the procedure whenever I need an address. I use the
procedure in labels (naturally), but also forms and
reports where I want to show the mailing address.
The advantage is that if I decide to change my address
formatting logic, I only do it in one place.
