Concatenated String On A Report

[Kerry Jackson]

I Have This Script Below That Works Great On A Button Of The Input Form. I Am Having Problems Setting Up A Function With This Script To Do The Same On A Report.

Can Someone Help!!!!

SCRIPT:

tbl = table.current()
count =0
lineitems = ""
ct = table.get("flrpln")
ct.fetch_first()
while .not. ct.fetch_eof()
count = count + 1
if trim(ct.operid) "" "DESC" then
lineitem = lineitem + trim(ct.operid) + space(3)
end if
end while
tbl.lineitems = lineitem



All This Script Does Is Concatenate One Field In A Table Of A (1-Many) Child Table And Stores It To A Field In The Header Table. All I Wish To Do In The Report Is List One Character String With All Of That Invoice Child Items; (Invoice Item Len is 6-char.)

Thanks Kerry

[Stan Mathews]

RE: Concatenated String On A Report

I don't understand all of your post but....

why do you want to create a function to hold this script, why not place the script in the report's onprintinit event? or have this script precede a script to print the report, both on the same button?

Your script seems to be missing a

ct.fetch_next() after the "end if"

You create and increment, at least once whether or not any matching records are found, a variable "count", but you don't seem to use this variable. The increment count = count +1 would make more sense inside the if...end if loop.

[Stan Mathews]

RE: Concatenated String On A Report

Also


lineitem = lineitem + trim(ct.operid) + space(3)

should generate an error. Since you start with

lineitems = "", I believe this should be

lineitems = lineitems + trim(ct.operid) + space(3)

then

tbl.lineitems = lineitems

[Kerry Jackson]

RE: Concatenated String On A Report

Stan Using The Onprintinit Event As I Understand Will Only Fire When The Report Begins To Print.

I Have A Parent Table And A Child Table Of Many Records Connected To One Parent Table.

I Am Trying To Print A Report Of All Parent Records With One Character String Made Up Of One Field From All The Child Records That Match The Parent Record Key.

Example: Child Records --- 'A101 A102 A103 A104 A105' All In One String On The Report Of The Parent Report. I Am Only Wanting To Show Invoice Product Item In A Character String On The Report Along With Other Parent Info.

As For My Script You Are Correct That I Made Some Type-O Errors When Typing It Late Last Night.

Thanks Kerry

[Tom Cone Jr]

RE: Concatenated String On A Report

Kerry,

I'd approach this differently.

The OnPrintInit script fires once, before the report layout is initialized. It does not fire each time a new parent table record is fetched.

I'd create an intermediate table and base the report on it instead of your current set. This table would have a single character type field to hold the concatenated string for each parent table record.

Then, I'd arrange my script to:

1) zap the intermediate table
2) populate it again using your original script to build the concatenated string
3) run the report against the new table.

-- tom

[Kerry Jackson]

RE: Concatenated String On A Report

Hi Tom,

Nice To Hear From You.. Since The Conference.. Where Is V6?

I Wish That Alpha Would Change/Make Improvements In The Report Writer Function Of V5/V6. You Should Be Able To Perform The Same Tasks In A Report As You Do In A Form On Any Object Of The Report, But You Can Not. It Seems That The Report Writer Does Not Have Enough Event Controls...

My Main Problem Is That I Have A Child Table That Has About 7 Million Records And Growing. With Alpha It Is Becoming Very Dificult For My Programmers To Create Reports With Out Spending A Great Deal Of Time Watching Indexes. I Am Thanking About Using Crystal Reports Or Something Similar To Do My Reporting.

I Have Developed A C++ Program Similar To The Program I Developed In Alpha In The Spirit Of Make My Programmers Happy. I Am Hoping That V6 Will Be A Better Product. Not Knocking V5, But Producing A Output(Report) Should Be Very User Freindly And Made Possible For The End User To Accomplish And Not My Programmers.

I Have Set A Time Table For One Of My Programmers To Start Using V6 In September Of This Year Or As Soon After When It Is Released.

Nice Talking To You ---

P.S. I Was Hoping That I Did Not Have To Create Another Table To Print This Report.

[Tom Cone Jr]

RE: Concatenated String On A Report

An alternative would be to change the structure of your existing parent table to include a new field to hold the concatenated string. This could be populated instead of using a different table. It could even be kept "current" as new child table records are entered. Lots of options for you.

-- tom

[DaveM]

RE: Concatenated String On A Report

Kerry,

Hope I am not out of line, but Crystal Reports is not more friendly than A5V5. There is more power to some things, but considering the cost???

It will be an experience for you if you never used it before.

I AM accomplished at CR and adequate at VB.

Luck.
Dave

[Kerry Jackson]

RE: Concatenated String On A Report

That Option Was Explored, But Not Fesiable Because The Child Records Are Changed Too Often.

At The Time Of Table Creation We Did Not Understand The Limited Capacity Of The Report Module. If We Did We Would Have Structured Our Tables For Parent Level Reporting Only.
Now Any Time We Need A Report That Requires A Different Table We Must Add To Or Create A New Set First -- Why!

At The Time We Had About 4 Or 5 Million Records That Needed Importing Into Alpha And Those Records Did Not Have This Extra Field. It Took My Programmers About 2 Weeks To Import All This Data And Another Week To Design All The Input Screens. They Like Me Are Main Frame Programmers That Thank As I Do That Any Data In A Data File (Table) Should Be Reported At Will With Out Controls Or Limitations. Besides, The Only Important Factor Of Automation Is What You Can Produce In Real Time Reporting.

Most Of My Customers Design Airplanes And Airplane Parts Which Requires A Great Deal Of Reports/Reporting. Every Day New And Different Reports Must Be Designed With A Minutes Notice. With More Than 50 Different Tables(MAX) It Is Becoming A Full Time Job For One Of My Programmers.

Thanks Again, Kerry

[JohnZaleski]

RE: Concatenated String On A Report

Make a user defined function out of your program - pass the parent id to the function and return the concatenated line item string.

[DaveM]

RE: Concatenated String On A Report

Kerry,

I worked for a Small company with a program written in VB that all reports were done in CR. I did most of those reports. We had 38 tables with hundreds of reports and more each week that kept me busy full time and more. We had 2 other people that were able to make some of the simpler reports. This program was/is sold nationally to do Auto/Boat/RV Dealerships.

The reports have to be perfect and to the penny because a lot of the reports were bank contracts and lease contracts.

There is no report that I ever wrote in Crystal Reports that I cannot duplicate in Alpha5V5.

Dave

[Tom Henkel]

RE: Concatenated String On A Report

Maybe I'm being naive, but, instead of using a script, why not create a calculated field in the report which is a concatenation of all of the child invoice numbers, and then place this calc field into the Group header?

allinvoic allinvoic+trim(child-"invoice)+" "

The report should do a pre-calc and place the required information in the header, and then list all the detail in the format that you want.

Again, maybe I'm not seeing everything you want to do, but this should work.

Tom

[Tom Cone Jr]

RE: Concatenated String On A Report

Kerry,

Even though I have no mainframe experience, when one arranges the data as you chose to do one must make two passes through the data to generate your report by hand, right? First pass would collect the field values needed to build the concatenated string for each parent table record. Second pass to print the report, line by line.

Generating a temporary table to hold the results of the 'first' pass through the data, and then basing the report on the temporary table is the Alpha Five equivalent.

-- tom

[Kerry Jackson]

RE: Concatenated String On A Report

Reading Other Messages; It Is Apparent That I Must Use A UDF in Order To Accomplish This Task, But I Can Not Find Any Documentation On How To Use UDF's In Reporting. Can Someone Explain By Example How To Use UDF In A Report. Perferably Alpha Or Someone That Does This All The Time.
I Would Like To Know How To Fetch Data From More Than 10 Tables And Create One String On A Report.

[JohnZaleski]

RE: Concatenated String On A Report

There is no trick to using a UDF on a report.It's no different than using a UDF anywhere else.
Define a UDF, let's say, Lines_string() and put the code into the global function - then the function will be available to use anywhere on a form or report in a calculated field - just like Alpa's built in functions.