Combining "broken up" records 1

[jkupov]

I am using CR 9 to access a database via ODBC to a piece of propriety software. One of the fields that I am needing to include in the report is a varchar character field limited to 255 characters. The developer wanted to let the front end users have (virtually) unlimited room for their documentation, so the record gets broken up in the database and stored as separate records (with the other fields in this table being present and populated for use as unique key to reference each chunk of narrative).

My problem is getting these chunks of narrative combined into a neat paragraph for for the report. The largest combined narrative that I have seen so far (examined over 2000 records) is about 3900 characters with the average one being about 1200 characters... so we're not talking about a large number of records to combine to get one record.

Is there an easy way to do this?

 

[lbass]

Have you tried collecting the comments in a variable? First group on whatever field clusters these comments together, let's say it's {table.ID}. Then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar comment := "";

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

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar comment;

You would format {@display} to "Can grow" by going to format field->common->can grow. You could then suppress the detail section.

-LB

 

[jkupov]

Thank LB. This is getting me closer. I hadn't thought of that. I had tried dragging the {table.ID}field into a text box.. which worked but was ugly. Your suggestion is getting me closer to what I need. However, a new paragraph is started right at the 256th character. Is there a way to fix this? I'll include an example below of what it looks like using the USDOI as the text:


When in the Course of human events, it becomes necessary for one people to dissolve the political bands which have connected them with another, and to assume among the powers of the earth, the separate and equal station to which the Laws of Nature and of Natu
re's God entitle them, a decent respect to the opinions of mankind requires that they should declare the causes which impel them to the separation.
We hold these truths to be self-evident, that all men are created equal, that they are endowed by their Cre
ator with certain unalienable Rights, that among these are Life, Liberty and the pursuit of Happiness. --That to secure these rights, Governments are instituted among Men, deriving their just powers from the consent of the governed, --That whenever any Fo

 

[lbass]

It looks like the comment field must end with a return and/or line feed. First, it looks like you don't need the space in the formula, so remove that. Create a separate formula {@comment}:

replace(replace({table.comment},chr(13),""),chr(10))

...and then use {@comment} in the {@accum} formula instead of {table.comment}.

Not sure this will work, but it's worth a try.

-LB

 

[jkupov]

That was a good thought... but it didn't work. I suppose it may be as good as it is going to get. It's not pretty but it certainly is functional for what I need and it is better than it was when I started. Thanks LB!

 

[jkupov]

I have been looking at some other tables in this DB and have discovered a field in which each segment of the narrative is assigned a narrative sequence number every time the record is broken up. For instance, in the example below, record ID 1 is broken up into three records to accommodate the limitation of 255 characters per record in the varchar character field. Each broken record is assigned it's own ID designating where it falls in the sequence of the entire narrative.

Is there an easier way to combine them now that I have found this narrative sequence field? It seems as if the designer intentionally put this field in here so they can be combined into one block of narrative. I just can't think of how this would be done. I will need to separate the NarrativeID and then combine the NarSeqID for each Narrative ID... easy for me to see what needs to be done but not so easy for me to see how to do it. Maybe it's time for a CR level 2 class.

Here is an example:

NarrativeID NarSeqID NarText
1 1 (text through 255 characters)
1 2 (cont. of txt for next 255 char)
1 3 (cont. of txt for next 255 char)
2 1 (text through 255 characters)
2 2 (cont. of txt for next 255 char)