Gossamer Forum: Products: DBMan: Customization: Print certain fields once then table of matching records

Jan 31, 2002, 2:17 PM

pwojcik

Novice (8 posts)

Jan 31, 2002, 2:17 PM

Post #1 of 13

Shortcut

Print certain fields once then table of matching records

Hi, I'm hoping someone can help me with my problem or point me to a mod that can assist.

I have been using DBman for several years now utilising it in a variety of funtions by modifying code or applying mods. However I am now stumped with the current situation.

I have some delimited files that are created from a COBOL program running against IDMS on our mainframe and ftp'd to a network drive, from there I read it with DBman to produce various outputs.

I have successfully produced all required outputs bar one. I need to produce a report in the following style:-

STConame......................BTConame
STAddress.......................BTAddress
STAddress1.....................BTAddress2
STCity............................BTCity

STUID.............................BTUID

ProdID | ProdDesc | Amount | InvNbr | UID| etc | etc
ProdID | ProdDesc | Amount | InvNbr | UID| etc | etc
ProdID | ProdDesc | Amount | InvNbr | UID| etc | etc
ProdID | ProdDesc | Amount | InvNbr | UID| etc | etc
ProdID | ProdDesc | Amount | InvNbr | UID| etc | etc

The data file is in the following format:-

STConame ; STAddress; STConame; BTAddress; ProdID; ProdDesc; Amount; InvNbr; UID; etc; etc; etc; (goes on for 52 columns)

So I need to search on BTConame or STConame, print out BTConame/Address and STConame/ Address once (although it exists in all records) and then print out a table of records that contain the matching BTConame or STConame.

I am able to print the table but cant figure out how to print the Name and Address just the once.

Hope someone can help

Peter

Feb 1, 2002, 11:01 AM

Watts

Veteran (1141 posts)

Feb 1, 2002, 11:01 AM

Post #2 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

Poke around in this forum, and also check the 'unofficial FAQ' (see any of LoisC's responses for the URL) there is a MOD you want, but I cannot remember what it is called.... Someone was asking about this recently, it prints "headers" once and then "details" - I'm pretty sure this is what you are looking for. This is what we used to refer to as "grouping" in foxfire.

Sorry I couldn't give you the exact name, - Mike.

Feb 1, 2002, 11:15 AM

pwojcik

Novice (8 posts)

Feb 1, 2002, 11:15 AM

Post #3 of 13

Shortcut

Re: [Watts] Print certain fields once then table of matching records In reply to

Mike

Thanks for the pointers I'll try searching on these headings.

I can get it to print out the field I searched on as a header but not other associated fields.

I'm still trying to figure this out myself but no luck yet.

Thanks once again.

Peter

Feb 1, 2002, 11:21 AM

Watts

Veteran (1141 posts)

Feb 1, 2002, 11:21 AM

Post #4 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

Perhaps you could adapt (hack) the short/long mod to suit your needs?

Feb 1, 2002, 12:26 PM

pwojcik

Novice (8 posts)

Feb 1, 2002, 12:26 PM

Post #5 of 13

Shortcut

Re: [Watts] Print certain fields once then table of matching records In reply to

I'm sure I've seen a mod similar to what I need but I think that it just prints one category field.

I dont think the short/ long mod will do it as I think it print loops through the record array.

Peter

Feb 4, 2002, 1:25 PM

pwojcik

Novice (8 posts)

Feb 4, 2002, 1:25 PM

Post #6 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

OK I spent the weekend trying to figure this out but still no luck.

Can anyone help

Thanks

Peter

Feb 4, 2002, 3:08 PM

Watts

Veteran (1141 posts)

Feb 4, 2002, 3:08 PM

Post #7 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

One place to try... notice how the html.pl works.

You get a "header" (see something like html_add_form) that only prints the <HTML><HEAD> tags etc, once. Notice how it contains the "upper" part of an html page?

And then down a bit it has this part:

&html_record_form (&get_defaults);

This pulls in the middle of the page (it 'calls' html_record_form) and prints 'one' for each record.

And then you have something like:

&html_footer

or so that prints the footer part once.

You could do the same thing by either creating all new subs or by using something like this to print your customized stuff:

if ($in{'view_records'}) {
print qq|$rec{'f00_bFirst'} $rec{'f00_bLast'}, $rec{'f00_pStreet'} $rec{'f00_pCity'} $rec{'f00_pState'} $rec{'f00_pZip'}|;
}

I can give you more detailed examples if this doesn't make since. Right now I'm suffering from "cedar fever" and my head is full of crud and I can't quite think clearly, nor can I breathe. Yuk.

Feb 5, 2002, 10:30 AM

pwojcik

Novice (8 posts)

Feb 5, 2002, 10:30 AM

Post #8 of 13

Shortcut

Re: [Watts] Print certain fields once then table of matching records In reply to

Sorry to hear that you are not feeling too good, hope that all is well soon.

I tried the last suggestion, also tried it in a new sub but nothing printed to the display on both counts.

Any more suggestions?

Peter

Feb 5, 2002, 5:05 PM

kellner

Enthusiast (606 posts)

Feb 5, 2002, 5:05 PM

Post #9 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

I've done something like this (there was a thread started by dianarae some weeks ago which covered this), and here's the general idea.

Suppose you have a database like this:

Name/Age/Job

John/23/Teacher
Joe/25/Consultant
Carol/87/Teacher
Rudy/45/Consultant

You want to print out search results sorted by jobs, and use jobs as category headers.

Teacher

John, 23
Carol, 87

Consultant

Joe, 25
Rudy, 45

1) Do a search with "*" in the field "Job", and use the field "Job" as field to sort on. If you want the individual listed items sorted as well, you need to use the multiple sort mod.

2) When you have search results, they are passed to html_view_success. In there, you have a for (0 .. $numhits-1) { .... &html_record ... } loop. This loop means that all found records are idividually passed to html_record for printing. What needs to be done is this: Print a record with a certain category value once. When the next record has the same category value, don't print the category again, just print the record.

3) Now the actual code. *Before* the for .... loop in html_view_success, add the line:

"my $prev_cat";

4) At the beginning of html_record, after "my %rec = @_;", add this code:

unless ($prev_cat eq $rec{'Job'}) { print qq|$rec{'Job'}|; $prev_cat = $rec{'Job'};}

print qq|<table><tr><td>$rec{'Name'}</td><td>$rec{'Age'}</td></tr></table>|;

For the first record, $prev_cat will have no value. The condition "unless" therefore evaluates true, and the job value "teacher" is printed. Also, $prev_cat is set to "teacher". For the next record, which in our example will also have "teacher" in the field "Job", the "unless"-condition evaluates false, because $prev_cat DOES have the value "teacher". Therefore, the job value will not be printed. This goes on until job has a different value, say, "consultant". And so on. It is important to sort the search results on that field which you use as a category header, otherwise, you won't get a proper list.

That's the basic idea.
kellner

Feb 5, 2002, 7:25 PM

pwojcik

Novice (8 posts)

Feb 5, 2002, 7:25 PM

Post #10 of 13

Shortcut

Re: [kellner] Print certain fields once then table of matching records In reply to

Thanks for the suggestions on this but I pretty much managed to do this back in post#3.

I need to be able to search on the db field BTConame (Bill to company name) and retrieve just the records that match this - print out the BTConame and STConame name and address fields (they occur in all records) just once - then loop through the remaining records and print out the other values in a table below.

I think I will need to build a new sub for this but cant figure out how to do this, every time I think I am close I hit a stumbling block and am now getting pretty angry with myself for not being able to resolve this.

Thanks

Peter

Feb 6, 2002, 3:48 AM

kellner

Enthusiast (606 posts)

Feb 6, 2002, 3:48 AM

Post #11 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

So did you manage to do this or didn't you? I'm confused.

I understood what you want to try, and the code I gave you is designed for doing it.

The only difference is: You want to print THREE field values as a category header, and not one.

So this should work in html_record:

unless ($prev_cat eq $rec{'firstfield'}) { print qq|$rec{'firstfield'}, $rec{'secondfield'}, $rec{'thirdfield'}<br>|; $prev_cat = $rec{'firstfield'};}

print qq|$rec{'fourthfield'}, $rec{'fifthfield'}|;# for all records
kellner

Feb 6, 2002, 7:50 AM

pwojcik

Novice (8 posts)

Feb 6, 2002, 7:50 AM

Post #12 of 13

Shortcut

Re: [kellner] Print certain fields once then table of matching records In reply to

Apologies, I was getting confused too.

Let me try the code today and I will post later to let you know how I got on (I have to attend Project Management training this morning- AAAAAAAAAAAgh).

Thanks for your help and patience.

Peter

Feb 12, 2002, 8:27 PM

pwojcik

Novice (8 posts)

Feb 12, 2002, 8:27 PM

Post #13 of 13

Shortcut

Re: [pwojcik] Print certain fields once then table of matching records In reply to

Sorry for the delay in replying to your post, afraid that I was struck down with the flu the day after my last message and have just managed to catch up with things.

I'd like to offer my heartfelt thanks for your help with my problem and can confirm that everything works just as I needed it to.

I will tidy up the code and post it back to this thread just in case it is of future use to any other DBMan users.

Thanks again

Peter