Lookup, Locate, or Scan on multiple fields within an FRX report

[dantheinfoman]

Hi all,

I'm trying to have a field that looks up a field in another table, but I don't know how to perform a lookup on multiple fields, for example:

In the field within the FRX form there would be something like this: LOOKUP(table2.serv_unit,QUERY.sales_type+QUERY.job,table2.sales_type+table2.job)

but, alas,it doesn't work.

Imagine the cursor QUERY is what's populating the fields in the report, but we want to look up something that's in table2 where the query's job and sales_type match the table2's job and sales_type.

Should I use SEEK, LOOKUP, LOCATE, something else - and what's the syntax to lookup using multiple fields to find the info? This one doesn't have a unique identifier, it uses those 2 fields to find what you need.

Thanks

Dan

 

[Mike Lewis]

Dan,

The syntax of your LOOKUP() call doesn't look right to me. I think the third parameter has to be a simple field name, not an expression (I'm not 100 percent sure about that). You might need to do two loookups - one to return sales_type and the other to return job - and then concatenate the two results together.

That said, in general, the best way to solve this kind of problem is to gather all the data you need into a single cursor before you do the report. Use SQL SELECT to create a cursor that contains all the values you need, with one record corresponding to one line of the report. That way, you avoid the report itself having to fetch data - something it's not really designed to do.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

I suggest a completely different approach. Driving a report from a table directly can cause all kinds of problems, especially if more than one user is involved, and should be avoided. Instead you create a cursor for your report, and do all the lookups up front. Sometimes you can do all the work in one big SQL Select statement, sometimes you need some code to process the cursor. But what's important is that your life will become much easier by doing it that way. Also, during development you will be able to Browse the cursor and get a good idea of how your report will look like.

 

[dantheinfoman]

I will make an update to the current cursor with the lookup data then, instead of attempting a function in the FRX to do it.

Thanks!

Dan

 

[Mike Lewis]

Sounds good, Dan. Let us know if it solves the problem. (I think it will.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Increasingly, I do what the others here are describing, using a temporary cursor or table to hold
all the possible data BEFORE the report is run.

But sometimes I like to use a UDF to present something, it doesn't seem to slow the native report generator
down, but does things like xfrx.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​