SET RELATION using conditional key

[AndrewMozley]

I am modifying an existing report which is produced from table1.

The code which generates the report dives into several other tables for each record in table1 and produces the report. Although I do not have access to this code, there is an entry-point where I can restrict the number of records from which the report is produced.

So I can say (for example)
SELECT table1
SET FILTER TO cField1 < “M”
. . . let report continue

And this will produce the required subset of the report.

However the selection of records from table1 depends on the value of a record in a related table, table2, which holds the currency for the table1 record. Unfortunately the relationship is not simple.

Table 2 has a primary key consisting of cGroup + cAccount; both these fields are present in Table 1. The rule is :

If Table 1 has its cGroup field non-space, then the relevant record in Table 2 has a key :
Table1.cGroup + SPACE(8)

If Table 2 has its cGroup field as spaces. then the relevant record in table 2 has a key:
SPACE(3) + Table1.cAccount

I have tried writing this code to put a filter onto Table1 before the report does its thing :

LOCAL lString
lString = "cGroup + IIF(EMPTY(Table1.cGroup,cAccount,SPACE(8))"
SET RELATION TO &lString INTO SPRFLS
SET FILTER TO EMPTY(Table2.currency)

However this fails with the error message :

Too many arguments

I would be grateful for suggestions. As mentioned I have to work with the structure of the existing program.

Thanks. Andrew

 

[Mike Lewis]

Andrew,

Did you copy and paste the code directly from your application into your post? If so, the error probably is simply a missing parentheses.

You have this expression:

IIF(EMPTY(Table1.cGroup,cAccount,SPACE(8))

You need a closing paren for the EMPTY function, like this:

IIF(EMPTY(Table1.cGroup[b])[/b],cAccount,SPACE(8))

But, even if that's not the correct solution, the error is almost certainly down to a simple typing error on your part, not to anything fundamentally wrong with the way you are trying to set a relation or a filter for the report.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

this will produce the required subset of the report.

Wouldn't it be MUCH easier to not use a complex Relation on the two tables at all for your report, but instead use a SQL Query to gather only those records from Both tables that you want and then use that Query Result for your report?

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thanks very much Mike. That does the trick.

I had in fact edited the code before posting on this site, but had made exactly the same mistake of omitting the closing bracket!

No doubt you are correct in the matter of SQL, JRB. However, as mentioned, I am having to modify the data sources for a report where I do not have control over the way in which the original report program works. I do not have access to the code.

Andrew