I am trying to concatenate designations for a letter I am producing. Each spriden id has multiple designations that they give donations too, so in the letter I want to say thank you for supporting The School of Business, The School of Technology, and The School of Nursing. I have this code below

TABLE FILE AA_GIFT_DETAIL
-SET &DESG = DESIGNATION_NAME;
-REPEAT LABEL WHILE SPRIDEN_ID NE LAST SPRIDEN_ID;
-SET &DESG = &DESG||(', '|DESIGNATION_NAME);
-LABLE TYPE &DESG
PRINT
DESG
BY SPRIDEN_ID
END

I receive the Improper use of GOTO error. What am I doing wrong.

Thanks

try this,

TABLE FILE AA_GIFT_DETAIL
-SET &DESG = DESIGNATION_NAME;
-REPEAT LABLE WHILE SPRIDEN_ID NE LAST SPRIDEN_ID;
-SET &DESG = &DESG||(', '|DESIGNATION_NAME);
-TYPE &DESG
-LABLE
PRINT
DESG
BY SPRIDEN_ID
END

Hi Tracie,

Here's an example using the CAR file and the substr function to produce a string that contains all the cars within each country. It's set up to hold up to 10 occurances of the CAR field(A16) with a ',' between the car names.

TABLE FILE CAR
SUM CAR
BY COUNTRY
BY CAR NOPRINT
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
DESG/A169 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(169, DESG, 1, 152, 152, 'A152') || ',' || CAR
ELSE CAR;
END
-*
TABLE FILE HOLD
SUM DESG
BY COUNTRY
END

If this looks like something that might work and you need more info, just let me know.

Jim

Kamesh,

I get the same GOTO error with that modification.

JimRice,
I get this error (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DESGS

This is my define

DEFINE FILE HOLD
DESGS/A569 = IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN SUBSTR(569, DESGS, 1, 552, 552, 'A552') || ',' || DESIGNATION_NAME
ELSE DESIGNATION_NAME;
END

The field DESIGNATION_NAME is A60.

Let me know if you have any other ideas. Thanks! tbj

Hi Tracie,

Here's an example using a CAR60 (A60) field.

DEFINE FILE CAR
CAR60/A60 = CAR;
END
-*
TABLE FILE CAR
SUM CAR60
BY COUNTRY
BY CAR60 NOPRINT
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
DESG/A609 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(609, DESG, 1, 548, 548, 'A548') || ',' || CAR60
ELSE CAR60;
END
-*
TABLE FILE HOLD
SUM DESG
BY COUNTRY
END

Let me know if this helps.

Jim

Tracie, the example that Jim provided assumes you know the maximum number of designation names you will encounter for one letter. The length of the DESGS field must be the total length of the maximum number plus the separator characters you will use. In the substring, the number should be the length of the DESGS field minus the separator and minus one DESIGNATION_NAME.

So, if we assume that you will get a maximum of 10 designation names and are separating them with a ',' then the code should be:

DEFINE FILE HOLD
DESGS/A610 = IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN SUBSTR(610, DESGS, 1, 549, 549, 'A549') || ',' || DESIGNATION_NAME
ELSE DESIGNATION_NAME;
END

Well, OK!

Yep! That worked! Thank you so much!
Do you know how to block a letter? Now I am running into the problem where the DESG line is running off the PDF page. I am doing the letter in the Header. I want to wrap the line so the letter will look even. If I select the text in the painter and click the block icon, it doesn't allow it. Just left, center, and right justify.

thanks...

DEFINE FILE AA_GIFT_DETAIL
FLG/A1=IF SPRIDEN_ID NE LAST SPRIDEN_ID THEN 'T' ELSE 'F';
END

TABLE FILE AA_GIFT_DETAIL
-SET &DESG = DESIGNATION_NAME;
-REPEAT LABEL WHILE FLG EQ 'T'
-SET &DESG = &DESG||(', '|DESIGNATION_NAME);
-LABEL TYPE &DESG
PRINT
DESG
BY SPRIDEN_ID
END

Hope this will work

Tracie, I'm not sure how to do it with the GUI, but I think an entry in the stylesheet will do the trick, here's an example:

DEFINE FILE CAR
HEAD_VAL/A1000 = 
'1wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ' |
'2wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ' |
'3wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ' |
'4wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ' |
'5wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ' |
'6wwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww Wwwww ';
END
-RUN
-*
TABLE FILE CAR
SUM MODEL NOPRINT
HEADING
"LARGE VALUE ON HEAD TEST"
""
"Remarks: <HEAD_VAL"
""
WHERE ( COUNTRY EQ 'ENGLAND' );
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM NOPAGE
ON TABLE SET NODATA ''
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='LEGAL', SQUEEZE=ON, ORIENTATION=LANDSCAPE,
LEFTMARGIN=0.3, RIGHTMARGIN=0.3, TOPMARGIN=0.3, BOTTOMMARGIN=0.3, $

TYPE=REPORT, GRID=OFF, TOPGAP=0.02, BOTTOMGAP=0.02,
FONT='HELVETICA', SIZE=8, COLOR='BLACK', BACKCOLOR='NONE', STYLE=NORMAL, $

TYPE=HEADING, LINE=3, ITEM=2, WRAP=7.5, COLOR=GREEN, $ <=========================
ENDSTYLE
END

That worked Francis! However, the line that I am wrapping is tabbing over and is not in the format of the rest of the letter. The margin is off by about an inch.

Back to the original question, how can I add 'and' to the last Designation in my string once it gets to the last designation??

Tracie,

To fix the TABBING issue, try using POSITION=#.##

Here is the code change to the example Francis provided:

TYPE=HEADING, LINE=3, ITEM=2, POSITION=1.00, WRAP=6.5, COLOR=GREEN, $

Hi Tracie,

How about this for putting the character '&' in front of the last desg(CAR60) field.

DEFINE FILE CAR
CNT/I5 = 1;
CAR60/A60 = CAR;
END
-*
TABLE FILE CAR
SUM CNT
BY COUNTRY
SUM COMPUTE CNTR/I5 = IF COUNTRY EQ LAST COUNTRY THEN CNTR + 1 ELSE 1;
BY COUNTRY
BY CAR60
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
DESG/A627 = IF COUNTRY EQ LAST COUNTRY
THEN
(
IF CNTR EQ CNT
THEN SUBSTR(627, DESG, 1, 564, 564, 'A564') || (' & ' | CAR60)
ELSE SUBSTR(627, DESG, 1, 564, 564, 'A564') || (', ' | CAR60)
)
ELSE CAR60;
END
-*
TABLE FILE HOLD
SUM DESG
BY COUNTRY
END

Let me know if this helps.

Jim

Yes that helps, but I am getting the sort field error b/c I have more fields that under that last hold file from different tables....

This solution was working fine until there was a gift that had 12 designations. When the concatenation was finished, the result was this:

" Annual Fund, College of Arts & Sciences, College of Engineering, Computer Science Department, University Choir, Victory Club, Theatre Scholarships, Avery-Collins Endowed Scholarship Fund, Richard E Moore Endowed Scholarship, WNAA Radio Station, Information Technology & Telecommunicationsormation Technology & Telecommunicationsormation Technology & "

The 12th designation is supposed to be Mathematics Department, but its repeating the 11th one and cutting it off for some reason.

This is what I am using:
DESGS/A1660=
IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN SUBSTR(1660, DESGS, 1, 500, 500, 'A500') || (', '|DESIGNATION_NAME)
ELSE DESIGNATION_NAME;

I keep playing with the numbers, but I haven't figured out what is making the difference. The number of characters in the above output is only 349 by the way.

Help please!

just a thought tracie, but could those & be messing it up?
Can you set up a simpler loop...to see if you're actually reading the data you think you are..forget formatting into pdf...just read and write in ordinary html to see if you get the results you're expecting.

you seem to be using the same fieldname in your define that you are in your substring.
defining DESGS as a substring of DESGS
That may be where your overlapping is happening.
Do a test run that is simpler
DEFINE DESGS2 ...
SUBSTRING(DESGS ...
and then see what your individual inputs are:
i.e. DESGS and DESIGNATION_NAME and SPRIDEN_ID
compared to your output DESGS2

Tracie

I have seen this behaviour before, but like you cannot see if it is a bug or not. My workaraound would give you something like this:

DEFINE FILE Fn
CNTR/I3 WITH DESIGNATION_NAME =IF SPRIDEN_ID EQ LAST SPRIDEN_ID THEN CNTR + 1 ELSE 1;
END
TABLE FILE Fn
SUM CNT.DESIGNATION_NAME NOPRINT
BY SPRIDEN_ID
PRINT
CNTR NOPRINT
COMPUTE DESIG/A110 =  IF C1 EQ 1 
                      THEN 
                       DESIGNATION_NAME || '.'
                      ELSE
                      IF C1 EQ C2 
                      THEN 
                        (' & ' | DESIGNATION_NAME) || '.'
                      ELSE
                       IF SPRIDEN_ID EQ LAST SPRIDEN_ID
                        THEN 
                          ', ' | DESIGNATION_NAME
                        ELSE DESIGNATION_NAME; NOPRINT
COMPUTE DESGS/A1500 = IF SPRIDEN_ID EQ LAST SPRIDEN_ID
               THEN SUBSTR(1500,DESGS,1,500,500,'A500')||DESIG
               ELSE DESIG;
BY SPRIDEN_ID
BY CNTR NOPRINT
WHERE TOTAL C1 EQ C2;
.
.
.

This message has been edited. Last edited by: Alan B,

Hi Tracie,

I agree, there seems to maybe be a bug here....
Anyway, another workaround that seemed to work for me is:

-----------------------------------------------------------------------
TMP_DESIGNATION_NAME/A62 = IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN ', ' | DESIGNATION_NAME ELSE DESIGNATION_NAME;
-*
DESG/A1548 = IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN SUBSTR(1548, DESG, 1, 1486, 1486, 'A1486') || TMP_DESIGNATION_NAME
ELSE TMP_DESIGNATION_NAME;
-----------------------------------------------------------------------

This example should work for up to 25 designation names. I have a working example using the CAR file if you want to see it.

Let me know if this helps.

Jim

That worked beautifully JimRice! Thanks so much!

" Annual Fund, College of Arts & Sciences, College of Engineering, Computer Science Department, University Choir, Victory Club, Theatre Scholarships, Avery-Collins Endowed Scholarship Fund, Richard E Moore Endowed Scholarship, WNAA Radio Station, Information Technology & Telecommunications, Mathematics Department"

quote:

Back to the original question, how can I add 'and' to the last Designation in my string once it gets to the last designation??

I see no 'and', which is what I was demonstrating in my response, with one pass of the data, complete with a final period.

Yes I do need the 'and' but I couldn't get yours to work Alan, also what is the c1 and c2? I don't see it defined anywhere....

Tracie

The C1 and C2 are column notation references to fields in the internal matrix. So C1 refers to the CNT.DESIGNATION_NAME, the total number of DESIGNATION_NAMEs in each SPRIDEN_ID and C2 refers to CNTR, the numeric list of DESIGNATION_NAME within each SPRIDEN_ID. If you have more fields then you will have to change them to C3 or C4 etc.

The code for DESIG, basically looks for:
If there is one DESIGNATION_NAME, C1 eq 1. Put a period on the end.
If this is the last DESIGNATION_NAME of multiples, C1 eq C2. Put an & (or and) and a period on the end.
Check that the SPRIDEN_ID is the same as the last one, precede with a comma.


Keeping the SUBSTR on its own without the extra concatenation seems to help the list breaking up as you saw, don't ask why, I don't know.

Hope that helps, but if you need a hand, post more of your code.

Hi Tracie,

Here's an example using the CAR file that kinda shows what is going on with this SUBSTR function. You can change the PARM_CNT to whatever you want. This is using Alan's example with a couple changes.

-*******************************************************************************************
-*
-SET &PARM_CNT = 12;
-*
DEFINE FILE CAR
CNT/I5 WITH BODYTYPE = 1;
CNTR/I5 WITH BODYTYPE = CNTR + 1;
-*
SPRIDEN_ID/A8 WITH BODYTYPE = 'IBIFORUM';
DESIGNATION_NAME/A60 WITH BODYTYPE = DECODE CNTR (
1 'Annual Fund'
2 'College of Arts & Sciences'
3 'College of Engineering'
4 'Computer Science Department'
5 'University Choir'
6 'Victory Club'
7 'Theatre Scholarships'
8 'Avery-Collins Endowed Scholarship Fund'
9 'Richard E Moore Endowed Scholarship'
10 'WNAA Radio Station'
11 'Information Technology & Telecommunications'
12 'Mathematics Department'
ELSE '--- more than 12 ---');
END
-*
TABLE FILE CAR
SUM CNT
BY SPRIDEN_ID
PRINT
COMPUTE DESIG/A66 =
IF CNT EQ 1 THEN DESIGNATION_NAME || '.'
ELSE IF CNT EQ CNTR THEN (' and ' | DESIGNATION_NAME) || '.'
ELSE IF SPRIDEN_ID EQ LAST SPRIDEN_ID THEN ', ' | DESIGNATION_NAME
ELSE DESIGNATION_NAME;
-*
COMPUTE DESGS/A1650 =
IF SPRIDEN_ID EQ LAST SPRIDEN_ID
THEN SUBSTR(1650,DESGS,1,1584,1584,'A1584') || DESIG
ELSE DESIG;
BY SPRIDEN_ID
BY CNTR
WHERE RECORDLIMIT EQ &PARM_CNT
-*WHERE TOTAL CNT EQ CNTR;
END
-******************************************************************************************

Alan,
Your example worked great, but there is one thing I'm confused about. I didn't expect the one space before the 'and' in the last DESIG to be retained after the strong concatination(||). It is there just like we really want...Can you explain?

I don't know if this will be of any help, but if so, great.

Jim

Jim

The strong concatenation (||) strips the spaces at the receiving end of the string to the left (the host string) only, it does not affect the string on the right (the concatenated string), so retaining the leading space in the concatenated string.

The rule is that with strong concatenation (||) trailing blanks are removed and leading blanks retained. With weak concatenation (|), spaces are left intact.

Also SUBSTR is only required so that one string can be reduced in size to accept another string concatenated to it and not get a format error. This was older code, 4.3 as it happens, in fact the correct way with 7.1+ is to use a V attribute on the alpha field. From my original code:

DEFINE FILE Fn
CNTR/I3 WITH DESIGNATION_NAME =IF SPRIDEN_ID EQ LAST SPRIDEN_ID THEN CNTR + 1 ELSE 1;
END
TABLE FILE Fn
SUM CNT.DESIGNATION_NAME NOPRINT
BY SPRIDEN_ID
PRINT
CNTR NOPRINT
COMPUTE DESIG/A500V =  IF C1 EQ 1 
                      THEN 
                       DESIGNATION_NAME || '.'
                      ELSE
                      IF C1 EQ C2 
                      THEN 
                        DESIG || (' and ' | DESIGNATION_NAME) || '.'
                      ELSE
                      IF SPRIDEN_ID EQ LAST SPRIDEN_ID
                      THEN 
                       DESIG || (', ' | DESIGNATION_NAME)
                      ELSE DESIGNATION_NAME;
BY SPRIDEN_ID
BY CNTR NOPRINT
WHERE TOTAL C1 EQ C2;
.
.

as the V format will accept a concatenation of itself with another string without the format error that you would get with a fixed A format, without the V attribute. Just make sure that the field is long enough to display all the DESIGNATION_NAMES you could have without truncating it.

Alan,

You are correct about the concatenation. Thanks

I don't have version 7.1+ and haven't ventured into using V format yet, but thanks for the info.

Jim

I GOT IT WORKING WITH THE 'AND'! THANKS ALAN!