Report group on 3 fields?

[Tom Cone Jr]

RE: Report group on 3 fields?

Becky, I'm sorry about that. My memory played a trick on me, cause I thought the copy genie would permit you to designate (link) fields from the source table to different fields in the destination table. It will not.

I recommend you build the intermediate table by hand, but leave it blank.

Then 'export' the chemicalA sequence to an ascii delimited text file.
Then 'import' the ascii delimited text file into your intermediate table.

Then 'export' the chemicalB sequence to a separate ascii delimited text file.
Then 'import' the ascii delimited text file into your intermediate table.

Repeat for the chemicalC sequence.

The Import routine will require you to match up the fields in the delimited file with the fields in your intermediate table.

You wind up in the same place I described before, three times as many records, but only one chemical field, but a few extra steps are required.

Good luck!

-- tom

[Selig Starr]

RE: Report group on 3 fields?

As suggested by others here's the way to go (I think).

Say yourtable is YOURTABLE.
Create a table say TEMP with the same structure as YOURTABLEs.
A simple copy within A5 will do that.

I asume that you want to do this for any chemical.

Create a global variable, say CHEM.

Then use the following Xbasic.
***************************************************************

dim t1 as p
dim t2 as p
dim RecordData as B

CHEM = ui_get_text("chemical?", "Which one?") ' That is ammon etc

t1 = table.open("YOURTABLE")
t2 = table.open("TEMP")
t2.zap 'This empties TEMP

t1,fetch_first() ' fetch first record from YOURTABLE
RecordData = t1.RecordData_get() ' capture it as a blob
while .NOT. t1.fetch_EOF()

' Check to see if CHEM is in the current record. If yes copy to TEMP
' else fetch the next record

if (fieldA="CHEM") .or. (fieldb="CHEM") .or. (fieldc="CHEM")
' I am not sure that you need the "'s around CHEM
t2.enter_begin()
t2.RecordData_set(RecordData)
t2.enter_end(.t.)
else
t1.fetch_next()
RecordData = t1.Recordata_get()
end if

end while

t1.close()
t2.close

end

***************************************************
You can put this Xbasic on a button if you choose.
Then base your report on TEMP table.

I haven't done Xbasic in a while so I can't vouch for the its accuracy.
This is the first time I have used BLob in a script so I
leave it to others to comment on it or if there is better way of doing
it.

[martinwcole]

RE: Report group on 3 fields?

jim, i'm not sure if the following calculated field, used as the filter on a table in a report, at the report detail properties level is a good example, but i humbly proffer it to one of my original mentors anyway.

it works well


cdate(Hearing_Aids->Sale_Date) >= var->bdate .and. cdate(Hearing_Aids->Sale_Date) edate

[martinwcole]

RE: Report group on 3 fields?

oops - that is not technically a calculated field - sorry

[Jim Chapman]

RE: Report group on 3 fields?

Hi Martin,

>>to one of my original mentors

[Selig Starr]

RE: Report group on 3 fields?

Becky,
I already posted this here but probably in the wrong place, So here it is again.

As suggested by others here's the way to go (I think).

Say yourtable is YOURTABLE.
Create a table say TEMP with the same structure as YOURTABLEs.
A simple copy within A5 will do that.

I asume that you want to do this for any chemical.

Create a global variable, say CHEM.

Then use the following Xbasic.
***************************************************************

dim t1 as p
dim t2 as p
dim RecordData as B

CHEM = ui_get_text("chemical?", "Which one?") ' That is ammon etc

t1 = table.open("YOURTABLE")
t2 = table.open("TEMP")
t2.zap 'This empties TEMP

t1,fetch_first() ' fetch first record from YOURTABLE
RecordData = t1.RecordData_get() ' capture it as a blob
while .NOT. t1.fetch_EOF()

' Check to see if CHEM is in the current record. If yes copy to TEMP
' else fetch the next record

if (fieldA="CHEM") .or. (fieldb="CHEM") .or. (fieldc="CHEM")
' I am not sure that you need the "'s around CHEM
t2.enter_begin()
t2.RecordData_set(RecordData)
t2.enter_end(.t.)
else
t1.fetch_next()
RecordData = t1.Recordata_get()
end if

end while

t1.close()
t2.close

end

***************************************************
You can put this Xbasic on a button if you choose.
Then base your report on TEMP table.

I haven't done Xbasic in a while so I can't vouch for the its accuracy.
This is the first time I have used BLob in a script so I
leave it to others to comment on it or if there is better way of doing
it.

BTW change everything to fit your case.
Hope it helps solve your problem.

[Becky McNichols]

RE: Report group on 3 fields?

Tom-
The Import/Export works great - gave me just what I need. I haven't written the report or routine to run it for the report, but looks like it'll be fine. Thanks so much!

[Becky McNichols]

RE: Report group on 3 fields?

Thanks for all of the comments. Tom wins in prize today.

[Selig Starr]

RE: Report group on 3 fields?

Tom,
Any comments on my proposed solution involving Blobs?

[Tom Cone Jr]

RE: Report group on 3 fields?

Your approach would filter out all the records which contain the specified chemical in any of the three fields, ChemicalA, chemicalB, chemicalC.

However, the result table would still have the wrong structure to conveniently produce a report grouped on each chemical, since the chemical might be present in any of the three possible fields.

I understood her to be looking for a report which would be grouped by a single chemical, but which would not necessarily be limited to a single chemical. This led me to think it was necessary to somehow 'reduce' the three possible fields to a single one for report purposes.

If I were to code it, it would look like this in pseudo code:
1. zap or create the intermediate table.
2. Open the source table, read the first record.
3. Examine, chemicalA, chemicalB, and chemicalC, storing their values in
three variables.
4. Copy chemicalA and other necessary fields to a new record in the
intermediate table.
5. Copy chemicalB and other necessary fields to a new record in the
intermediate
table.
6. copy chemicalC and other necessary fields to a new record in the
intermediate table.
7. fetch next record
8. repeat steps 3 through 7 until all have been processed.

See what I mean?

-- tom

[martinwcole]

RE: Report group on 3 fields?

Many of us have awarded tom first prize many times

[martinwcole]

RE: Report group on 3 fields?

your lessons in the phorum

[Selig Starr]

RE: Report group on 3 fields?

Tom,
Thanks for the clarification. I see that my approach
would not allow an easy way to produce the report that
Becky wants.
Selig

[Tom Cone Jr]

RE: Report group on 3 fields?

No problem. Actually, your approach is similar to 'rolling your own' copy operation, with a filter based on multiple fields thrown in for good measure. Will be useful to some, I'm sure. I know I'm keeping a copy.

-- tom

[Selig Starr]

RE: Report group on 3 fields?

Becky,

Thanks for posting an interesting problem.
As Tom pointerd out to me. my proposed solution would not satisfy your
needs.
Sooo, after reading all the comments,especially those of Tom, and
if I understand your problem now, I offer the following.

Add a character field, say CHOICE, to your table.
Create and save an update operation, say INIT, that updates
CHOICE so that all its entries are blank (or "NONE" if you prefer).

I asume that you want to do the report for any chemical.

Create a global variable, say CHEM.

Then use the following Xbasic.
***************************************************************

dim t1 as p

CHEM = ui_get_text("chemical", "Which one?") ' That is ammonia etc

t1 = table.open("YOURTABLE")
:update.run ("INIT") 'this initializes the CHOICE field. Don't know if : is
' necessary

t1,fetch_first() ' fetch first record from YOURTABLE
while .NOT. t1.fetch_EOF()

' Check to see if CHEM is in the current record. If yes set flag to TRUE.
' else fetch the next record

if (fieldA="CHEM") .or. (fieldb="CHEM") .or. (fieldc="CHEM")
' I am not sure that you need the "'s around CHEM
CHOICE = CHEM
else
t1.fetch_next()
end if

end while

t1.close()
t2.close

end

**************************************************
EG
If CHEM = Ammonia, the CHOICE field will have "Ammonia" wherever
any one of your 3 chemical fields has "Ammonia" and "" (or "NONE" if you
chose that option) elsewhere.

You can put this Xbasic on a button if you choose.
Then base your report on a filter, ie, CHOICE = CHEM.

Will that do the trick?