Making one formula control another

[dison]

Hi.

I have two tables 'streets' and 'units'. as you can probably guess I have many street lighting units in any one street. In my units I have a field called 'renewal'. I want my report to select every unit in a street if any one of the units in that street have 'IARP' in the renewal field.

Anybody know how to do this?

Thanks.

 

[Madawc]

Add the 'units' table twice, the second time just for units that have have 'IARP' in the renewal field.

Link 'units' to units by 'IARP' by 'Street', assuming they have such a field. This will suppress units that don't have at least one 'renewal' in their street. It will also mean multiple 'rows' when there are two or more being renewed for a street, so you'd be st group by unit-code within street and suppress the detail, putting the unit details in the lower group header or footer.

If that doesn't work, have a main report just for street and units with renewal. In the group header or footer, put a subreport that shows all units for that street.

Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.


Madawc Williams (East Anglia, UK)

 

[synapsevampire]

Group by the Street.

In the details palce a formula such as:

IARPCount formula
if {units.renewal} = "IARP" then
1
else
0

Go to the Report->Edit Selection Formula->Group and place:

sum({@Iarp},{streets.street}) > 0

Now the report will only display those that meet your criteria.

However the aggregate formulas may require further filtering to also allow for this criteria as other rows will not display in the report, but they'll still be there.

Since you don't have any aggregates in your description, perhaps this isn't an issue.

-k