Tricky problem with multiple fields in one table

[Drakhan]

Situation: SQL 7.0 DB

Data Table: {OPMRG}

Fields: OPID, RECID, COMPANY, STATUS

Construct of DB:

OPID RECID COMPANY STATUS
--------------------------------------------
9AXF OC ABC Co. <NULL>
8ZWB OC XYZ Inc. <NULL>
8ZWB OI 200 84/Trucks
9AXF OI 250 12/Plant Equip.
5GSM OC ZZZ Ltd. <NULL>
5GSM OI 175 <NULL>

Output that I would like to generate:

Company Imporant Number1 Important Info1
------------------------------------------------
ABC Co. 250 12/Plant Equip.
XYZ Inc. 200 84/Trucks
ZZZ Ltd. 175

How would I go about creating a formula to tackle this. I am not the best of programmers, but am a technical user. Any help here is greatly appreciated. Thank you.

 

[synapsevampire]

That's a bizarre table you have there, first, fire the DBA

Group by OPID

You have options from there, here's a simple one:

Group Header Section
Create a formula with this:
whileprintingrecords;
stringvar CompanyName:={Company};



Group Footer Section
Has 3 fields:
The first:
Create a formula containg this:
whileprintingrecords;
stringvar CompanyName;
CompanyName

The second:
{company}

The Third:
{STATUS}

This assumes one Company row which contains the company name, and one company row which contains importantnumber1 (which is why the DBA gets to go back to flippin' burgers).

If there are more than 2 rows per OPID, this won't work.

Let's hear otherwise.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SmpTraining]

Another solution is to join the table to itself using the OPID field. Although I have not tested this, it should work.

Select table OPMRG twice, the second time, give it an ALIAS of OPMRG_2. Link from OPMRG.OPID to OPMRG_2.OPID.

On the report insert fields:

OPMRG.COMPANY, OPMRG_2.COMPANY, OPMRG_2.STATUS

Edit the record selection criteria to select only those records where OPMRG.STATUS is null AND OPMRG_2.STATUS is not null.

This WILL work with the sample data you've listed.

Good Luck. Steve Phillips, Crystal Consultant

 

[Drakhan]

Yeah, I'd love to fire the DBA that created that table, but I don't think FrontRange Solutions, the makers of GoldMine, would do that for me!

If anyone has experienced the joys of working with GoldMine, I pitty you as we are in the same form of slow torture that seems to have no end.

Anyway, I did as you instructed and the results were as I expected:

XYZ Co. XYZ Co. <NULL>
200 200 84/Trucks

Apparently it is only keying on the first instance of the OPID...

 

[synapsevampire]

You couldn't have done as I sugeested because if it's grouped on OPID, you would always get at least 3 rows.

The sort order might cause problems, make sure it's at original order.

Use Insert->Group, and select the OPID field. To change the order use the box below the field where you chose the field.

This should resolve, but Steve brings up an interesting point, you could use a self join.

I've had the misfortune of working with Goldmine, it used to be a .dbf product, though I can't recall this particular problem, but it was ugly to work with...

Good luck with it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Drakhan]

SMPhilips,

Thanks for the reply. I attempted your solution, but my ability to program has limited me successfully following through...I guess I'll just have to come up with a different solution. Thank you all for your help!!!

 

[Ngolem]

don't give up yet it isn't so bad really

a question: for a given OPID is there only one OC and IC combination?

if so then create an alias of the table : {OPMRG}
You do this by selecting this table twice. when asked call the second one OPMRG_OI.

Link them together by OPID...if there is a possiblity that the OI record may be missing then make it a left outter join

Now in your record selection formula

Make sure that the following is in there along with other stuff you want

{OPMRG.RECID} = &quot;OC&quot; and
{OPMRG_OI.RECID} = &quot;OI&quot;

Now you are most of the way there

Now Group on OPID

in the detail section you would have simple fields....no formulas required.

Company Imporant Number1 Important Info1
{OPMRG.COMPANY} {OPMRG_OI.COMPANY} {OPMRG_OI.STATUS}


That's all there is to it....no programming required at all.

BTW...I vote for fire the dba as well





Jim Broadbent

 

[SmpTraining]

To expand on my comments made earlier, linking to the same table twice IS the solution, in fact I have a working report based on the sample data you've given. Tell me your email address or email SMP@Training.co.uk and I'll send you the working report.

(Should warn you its 23:21 with me right now - I'm off to bed and tomorrow I'm off to work so if I don't get your email by the morning you'll have to wait till at least 18:00 GMT)

Happy reporting, and good night. Steve Phillips, Crystal Consultant

 

[synapsevampire]

Steve: I agree.

I have several reports on the current contract using self-joins, but I probably won't suggest doing it in these forums, it can be a tad complicated to explain, and I mainly just explain theory.

What I had proposed also works (albeit a quick hack), and I too tested the theory.

Drakhan didn't follow my example or he/she coldn't have returned only 2 rows. I suspect that they allowed the group to use ascending order too, AND did not correctly use the variables (or I didn't explain them correctly, which is just as likely )

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Steve - I agree with you totally that aliasing the table is the way to go....but I do not agree with the use of Status as the way to filter the data in the record select.

&quot;Edit the record selection criteria to select only those records where OPMRG.STATUS is null AND OPMRG_2.STATUS is not null.&quot;

the proper record selection criteria should be based on the
RecID as in my post above:

{OPMRG.RECID} = &quot;OC&quot; and
{OPMRG_OI.RECID} = &quot;OI&quot;

for the value &quot;IC&quot; refers to the Important info exclusively where as the value of STATUS being NULL doesn't as shown in these line of data

5GSM OC ZZZ Ltd. <NULL>
5GSM OI 175 <NULL>




Jim Broadbent