Hi all,

We have a file that contains comments for orders. There can be many comments for an order and these each have their own record on the file, with their own sequence and related order number.

Example:

Order = 1, Sequence = 1, Comment = ABC
Order = 1, Sequence = 2, Comment = DEF
Order = 1, Sequence = 3, Comment = GHI
Order = 1, Sequence = 4, Comment = JKL

Order = 2, Sequence = 1, Comment = AAA
Order = 2, Sequence = 2, Comment = BBB

I need to report all the comments on ONE line per Order in my report, so:

Order Number, Comment 1, Comment 2, Comment 3, Comment 4.

I have created a summarised Comment file using an ACROSS statement. This gives me 1 record per order, with several Comment columns. I can then print this file using their Alias names:

TABLE FILE
Order
E02
E03
E04
E05

This works fine if the user enquires on Order 1 (with 4 comments), but if they only enquire on Order 2 (with just 2 comments) then columns E04 and E05 won't exist. Is there therefore a way I can check these columns exist before I print them?

Many thanks

Mark

MArk

If I understand this well, you put the result of the across report and use that as the source for your printing report.
Why not print the report direct from the original source something like

TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
WHERE ORDER EQ &ORDER
END

Another possibility is the use of the variable character option.
Like the next example:

DEFINE FILE MFD
TOTCOMM/A400V = IF ORDERNR EQ LAST ORDERNR THEN TOTCOMM || (', ' | COMMENT) ELSE COMMENT;
END

TABLE FILE MFD
SUM TOTCOMM
BY ORDERNR
END

This will print all order comments on one line, with a separator of ', ' (or any other of your choice).

Hope this helps ...

quote:

Originally posted by FrankDutch:
MArk

If I understand this well, you put the result of the across report and use that as the source for your printing report.
Why not print the report direct from the original source something like

TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
WHERE ORDER EQ &ORDER
END

Hi Frank, The problem is that this is part of a much larger complex report that retrieves data from many different files. The users can select which columns they want in their reports and we only do the processing required depending on what they have requested. So through the program we create the neccessary hold files and then join them all up at the end on Order Number.

quote:

Originally posted by GamP:
Another possibility is the use of the variable character option.
Like the next example:

DEFINE FILE MFD
TOTCOMM/A400V = IF ORDERNR EQ LAST ORDERNR THEN TOTCOMM || (', ' | COMMENT) ELSE COMMENT;
END

TABLE FILE MFD
SUM TOTCOMM
BY ORDERNR
END

This will print all order comments on one line, with a separator of ', ' (or any other of your choice).

Hope this helps ...

Hi Gamp, This was my first suggestion to the users - to append all the comments into one large text column....but apparently they would prefer to see the Comments seperately and may only request to see 'Comment 3' for example!

If all else fails I will use this approach.

My one thought was to try and add a 'dummy' order with 4 empty comments to the Comments File, with a made up Order number – say 999999. I can force this dummy order into the query by also querying Order 99999 along with what the user is querying. This way the across statement will always create 4 comment columns. Therefore when the user queries an Order that only has 2 comments, this dummy would have been in the file and all 4 columns will exist...

Order = 999999, Sequence = 1, Comment = ‘ ’
Order = 999999, Sequence = 2, Comment = ‘ ‘
Order = 999999, Sequence = 3, Comment = ‘ ‘
Order = 999999, Sequence = 4, Comment = ‘ ‘

The only thing is I can't think how to easily add these dummy records to my Comment file before summarising with the Across?!? Hopefully this is possible?

Mark

Is the number of replies per order limited to 5 or 6?

If so you can create some fields static in the define otherwise you should create them on the fly by reading the hold master file.

Only 4...

ok you can do this

TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
COLUMNS 1 AND 2 AND 3 AND 4
END

Now you force the use of 4 columns if it holds data or not.

quote:

Originally posted by FrankDutch:
Only 4...

ok you can do this

TABLE FILE SOURCE
SUM COMMENT
BY ORDER
ACROSS SEQUENCE
COLUMNS 1 AND 2 AND 3 AND 4
END

Now you force the use of 4 columns if it holds data or not.

Whoaa! I think that is just what I needed!! It works fine in my small test Fex and I will now into the main program where I am sure it will be good too.

Many thanks all

and there's always this:
for a variable count of acrosses

TABLE FILE CAR BY COUNTRY ON TABLE HOLD
END
-RUN
-SET &howmany = &LINES ;
TABLE FILE CAR
SUM SALES ACROSS COUNTRY
BY CAR
ON TABLE HOLD AS HDATA
END
-RUN
 
TABLE FILE HDATA
PRINT E01
-* set to how many BY fields you start with
-SET &kounter = 1 ;
-REPEAT endloop &howmany  TIMES
-SET &kounter = &kounter + 1 ;
E0&kounter
-endloop
END

if you have more than 9 comments, you gotta start messing with the concatenation.
It becomes E&kounter, not E0&kounter.

Have you considered doing a MATCH on the file (you can even MATCH it to itself) which would then produce one output file with all fields rolled up into one row? And it will generate the necessary MFD, as well.