Report group on 3 fields?

[Becky McNichols]

I need to group a report by "chemical". However, the same chemical can occupy any one of three different fields:
Chemical A
Chemical B
Chemical C

For example, the chemical "ammonia" may appear in a record as either Chemical A, B, or C, combined with 2 other chemicals. I want my report to summarize ALL of the ammonia data, regardless of the field it occupies. How can I do this?

[ROBERT SULLIVAN]

RE: Report group on 3 fields?

Try filtering by using the LEFT function.
LEFT(compound,5)for ammonia and ammonium compounds it would filter using "ammon" for the group. Compound would be your field.
I've been in the lab field for quite awhile (22 yrs) and use A5v4 for all lab applications. I haven't found an application that alpha can't perform.

Bob Sullivan

[Tom Cone Jr]

RE: Report group on 3 fields?

Becky, if Robert's suggestion doesn't solve your problem you might consider using an intermediate table.

You could make three passes through your source table.
On first pass, copy the necessary fields to the temporary table if Chemical A field equals Ammonia.

On second pass, copy the necessary fields to the temporary table if chemical B field equals ammonia.

On third pass, copy the necessary fields to the temp table if chemical c field equals ammonia.

Then base your report on the temp table, grouping by the single chemical field.

Hope this helps.

-- tom

[whanigsberg]

RE: Report group on 3 fields?

Becky,

Just to be perfectly clear (as Nixon used to say) each of your three fields can only hold a single chemical but a given chemical can be in any one of the fields.

Please confirm that this is the setup.

Thanks,
Bill

[Becky McNichols]

RE: Report group on 3 fields?

YES. The same chemical name may be listed in different records under EITHER the ChemicalA field OR the ChemicalB field OR the ChemicalC field.

[Becky McNichols]

RE: Report group on 3 fields?

I guess I don't understand. How do I incorporate all 3 fields into this expression? I'm actually working with labeled combinations of herbicide trade names - for example: Roundup + Banvel + 2,4-D OR Banvel + Tordon + Roundup (in the ChemicalA, ChemicalB, and ChemicalC fields).

From these 3 chemical combinations, I need a report giving totals of each chemical distributed.

[Becky McNichols]

RE: Report group on 3 fields?

Tom-
I'll need to think on this a bit. Are you suggesting setting up a Copy or Post Operation? I appreciate your advise.

-becky

[Jim Chapman]

RE: Report group on 3 fields?

Hi Becky,
If I'm understanding you right, then in your report a given record could potentially need to be included in 3 different groups on your desired report. You have 3 fields and if each was populated then that record would need to be included 3 times. I'm thinking you'd need an intermediate table, zap the intermediate table, then step through your records via xbasic and write a record to the intermediate table for every field that was populated, then base the report on the intermediate table.

Jim

[Jim Chapman]

RE: Report group on 3 fields?

Hi Becky,
After re-reading your post, I'm thinking you only want to have the report show one chemical at a time. Is this right? If so, make a calculated field. called calc_1 on the report with the expression: field1+field2+field3 (assuming character fields). Then you could set a global variable with the name of the chemical you want your report based on, lets call the variable 'chem_name'. Now in the detail properties of your report set the filter something like: chem_name $ calc_1.

I've had trouble getting A5 to filter records based on calculated fields on the report, so you may have to create a field for this purpose in the table, same expression should do.

Jim

[Tom Cone Jr]

RE: Report group on 3 fields?

Becky,
Assume for the moment that your source table has the following fields:
chemicalA
chemicalB
chemicalC
Cost
Age
Birthdate

(I know it's silly, but humor me!)

The goal is to copy records to an intermediate or temporary table with the
following fields:

chemical
Cost
Age
Birthdate

And then base your report on the intermediate table, grouping only on the chemical field.

To do this, using my 'brute force' approach, define three operations.

In the first, copy fields from source table:
ChemicalA
Cost
Age
Birthdate

to fields in intermediate table:
Chemical
Cost
Age
Birthdate

In the second, copy fields from source table:
ChemicalB
Cost
Age
Birthdate

to fields in intermediate table:
Chemical
Cost
Age
Birthdate


In the the third, copy fields from source table:
ChemicalC
Cost
Age
Birthdate

to fields in intermediate table:
Chemical
Cost
Age
Birthdate


A bit tedious to be sure, but you wind up with a new table, containing fields
chemical
Cost
Age
Birthdate

and *importantly* you now have three times as many records, but *only* one chemical field in each...

Now for the good part, base your report on the new table, grouping or filtering on the one chemical field.

You can repeat this sequence repeatedly. Just remember to zap the contents of the interemediate table beforehand each time.

All of these steps could be included in a single xbasic script, but you can do it manually, too.

Remember to think about what fields you'll need in your report as you design the copy operations. If a field isn't copied to the intermediate table it won't be available to you when you start designing your report after the copies are finished.

Good luck.

-- tom

[martinwcole]

RE: Report group on 3 fields?

Jim, if she only wants the report to run on one chemical, couldn't she put conditional objects on the form and use something like this:
chemical is a global variable
field=var->chemical
put this on each field, and then use calculated fields to sum the values of each field-column and another to sum them all??

[ROBERT SULLIVAN]

RE: Report group on 3 fields?

BECKY,
Can you make another table listing the pest/herb with the chemical name (or at least a field with the chemical of interest),chemical % and then add a field or two to the original table (Tammonia,ect)that would be a calculated field using a lookup function. I.E. compounda*lookupN + compoundb*lookupN + compoundC*lookupN.
If you can do the above you would be able to group by "ammonia",ect.

Bob Sullivan

[whanigsberg]

RE: Report group on 3 fields?

Hi all,

Martin raises a point I had been wondering about: whether she needs to see products for a single chemical or whether she needs a full grouped report which includes every chemical. Obviously, the former is easier. I had assummed the latter.

So far nobody has brought up with I think is the fundamental issue here. Of course, I don't know all about the data but it seems that the table structure doesn't map the data structure closely enough.

If the data were stored in two tables (product, ingred) and product was the parent with a one to many link to ingred, then designing the report would be trivial.

An inverted set of ingred linked one to many to products would make simple a screen display of products which share an ingredient.

The structure would also be able to handle cases of a products with four or more ingredients if they were to arise.

So before involving myself in relatively strenuous efforts to produce a single report, I would assess the benefits of revising my tables. It wouldn't be difficult and data probably would not have to be reentered.

Bill

[Becky McNichols]

RE: Report group on 3 fields?

Tom-
Once again, you seem to have provided the answer I need. THANKS! I'm sure your approach will work, but I'm having a small problem implementing it. When I'm defining the copy operation (say, the first one you've outlined above), I first choose the table to copy from, then the table to copy data to (either through the copy genie OR the copy card definition). I choose the fields I want to copy over, but how do I copy them to fields of a different name (example: "ChemicalA" to "Chemical"). I can only get it to copy to a field of the same name.

Thanks again for your help. It's greatly appreciated.

[Jim Chapman]

RE: Report group on 3 fields?

Hi Martin & William,

To Martin, yes I think your idea would work well.

>>it seems that the table structure doesn't map the data structure closely enough