Concatenate records? 2

[goldfishhh]

I've got a very basic report completed which first groups by day then by application type. My database has a table with 3 fields: Application type (See above), order # and application detail.

Application type is a unique
Order number is a number from 1-10
Application detail is text (255chrs).

I can display the application detail sorted by order number and see the whole detail (some application detail is 4000 characters - resulting in the first record being "1" with the text, "2" the text and so on and so forth)

What I'm trying to do is concatenate all of the text for the application detail by order number grouped by application type.

Dean-

 

[kskid]

Try this

totext({table.order_no}),"00") + {table.application_type}

I used "00" for 2 digit formatting otherwise the group will be out of order. Namely, 1, 10, 2, 3..., 9 instead of 01,02,03,04...,10

-lw

 

[lbass]

What problem are you running into when you sort first by application and then secondly by order number? I.e., how is the text displaying in an undesirable manner?

-LB

 

[goldfishhh]

I think I need to do a better job describing the output. The output looks like this

Grouping 1 - Application Type (Field)
Details - Order number (field) Application detail (field)

So, as an actual example, here is the output:


Windows server
1 This windows server has been con
2 figured with 3 HDDs, 2 monitors, th
3 ree NIC cards and 2 power supplies

As you can see, the name of the application type (Windows server) prints out and then the line(s) below it are the application details in order. This issue is, each new order number, the system enters a <CR>. What I want is this output:

Windows server
This windows server has been configured with 3 HDDs, 2 monitors, three NIC cards and 2 power supplies

Dean-

 

[lbass]

Okay, create a formula like this:

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {table.appdetail};

In the group header use this formula:
//{@reset}:
stringvar x;
if not inrepeatedgroupheader then
x := "";

In the group footer, use this formula:
//{@display}:
whileprintingrecords;
stringvar x;

This assumes that there is not a return, but that the new lines were forced because the text was in separate records. Suppress the detail section and display the group footer.

-LB

 

[goldfishhh]

BINGO! I will certainly not loose this little snippit of code. Now the fun starts. I need to pick apart that text and display it so it looks a tad nicer.

 

[goldfishhh]

Ok, part 2. This is where it gets complicated. (sorta).

Here is my output and all of this comes from one field (rather the output from the formula above):

CONDITION: Response time is greater than 10.00 seconds DESCRIPTION: Response time was 22.31 seconds. --- LOCATION: chicago TIME: Tue Jun 10 10:44:28 AM 2008 (-0500) FROM: pm1 Group1 SCRIPT: Oracle_prod_m TRANS: Oracle PROD-M - scheduled reports ERROR: none

I want to insert a Chr(13) - new line/enter/return etc... after each specific item. As you can see, part is static (name) and the detail is variable. I'd love it to look like this:

CONDITION: Response time is greater than 10.00 seconds DESCRIPTION: Response time was 22.31 seconds. ---
LOCATION: chicago
TIME: Tue Jun 10 10:44:28 AM 2008 (-0500)
FROM: pm1 Group1
SCRIPT: Oracle_prod_m
TRANS: Oracle PROD-M - scheduled reports
ERROR: none

 

[SaintJason]

Hi,

Is each line an application detail?

If so amend the details section formula as follows

x := x & {table.appdetail} & chr(13)

 

[goldfishhh]

Unfortunately not. It is just a huge long string of text.

 

[CR85user]

Ideally you ought to start a new question for this seperate issue.

As this is a single field with the multiple entries I would ask if the entries will always contain the same labels within? For example do they all contain:

CONDITION:
DESCRIPTION:
LOCATION:
TIME:
FROM:
SCRIPT:
TRANS:
ERROR:

Also, are they always in that order? If so a quick but dirty solution would be the following formula:

//Compile string
extractstring({@field},'','DESCRIPTION:') + chr(13) +
'DESCRIPTION: ' +
extractstring({@field},'DESCIPTION: ','LOCATION:') + chr(13) +
'LOCATION: ' +
extractstring({@field},'LOCATION: ','TIME:') + chr(13) +
'TIME: ' +
extractstring({@field},'TIME: ','FROM:') + chr(13) +
'FROM: ' +
extractstring({@field},'FROM: ','SCRIPT:') + chr(13) +
'SCRIPT: ' +
extractstring({@field},'SCRIPT: ','TRANS:') + chr(13) +
'TRANS: ' +
extractstring({@field},'TRANS: ','ERROR:') + chr(13) +
mid({@field},instrrev({@field},'ERROR'))


Right click the inserted formula and select format field - Tick 'Can Grow'

'J

 

[CR85user]

(p.s. - I am confident that one of the resident gurus here can offer you a tidier solution.)

 

[goldfishhh]

Sometimes the best things in life are dirty. Heh.

Tis worked like a champ! You rock!

 

[SaintJason]

This is fine if you are dealing with the same separators in every occurrence.

I would advise that you add & chr(13) & between fields in the formula that creates the string.

 

[CR85user]

If these were seperate string concenated into a single field then you could do that, this is apparently a single field unrelated to the first query though.

btw - apologies for the typo - I am sure you spotted it in the earlier post (Line 3 of formula) ->

extractstring({@field},'DESCIPTION: ','LOCATION:') + chr(13) +

Should read:

extractstring({@field},'DESCRIPTION: ','LOCATION:') + chr(13) +

After looking at the above it might be worth tightening it up a bit as character counts may end up over 254->

//End spaces removed and tidied up
//Compile string
extractstring({@field},'','DESCRIPTION:') + chr(13) +
'DESCRIPTION: ' +
extractstring({@field},'DESCRIPTION: ',' LOCATION:') + chr(13) +
'LOCATION: ' +
extractstring({@field},'LOCATION: ',' TIME:') + chr(13) +
'TIME: ' +
extractstring({@field},'TIME: ',' FROM:') + chr(13) +
'FROM: ' +
extractstring({@field},'FROM: ',' SCRIPT:') + chr(13) +
'SCRIPT: ' +
extractstring({@field},'SCRIPT: ',' TRANS:') + chr(13) +
'TRANS: ' +
extractstring({@field},'TRANS: ',' ERROR:') + chr(13) +
mid({@field},instrrev({@field},' ERROR'))

'J