Filter Values from Area Schedule

[ewalker]

Is it possible to add a filter to the database header so that a column only shows the values for the objects that have the required field value attached to it?

For example, I used the formula =('Space'.'Area') which listed the net area for ALL the spaces in the file. I want all the spaces to be listed. However, in a particular column, I only want to show the values for the spaces that have the field value Enclosed Liv in the attached record Space Schedule as Yes. So I tried the formula =('Space'.'Area') & ('Space Schedule'.'Enclosed Liv'='Yes') but it only gave either True or False as the result, instead of the numerical value for the net area.

I am looking for a result similar to the image shown.

Edited March 17, 2009 by ewalker

[islandmon]

'Enclosed Liv in the attached record 'Space Schedule' as Yes ... which is text...

but you are requesting "the numerical value for the net area"

Hence the boolean True or False ... indicating that the field queried 'Enclosed Liv' contains data.

[Pat Stanford]

Try this as the formula for the column.

=IF('Space Schedule'.'Enclosed Liv'='Yes', space.area, ' ')

You probably will not get the proper summation in the column header as it seems to add 1 for each blank row.

[ewalker]

Pat:

That worked beautifully! The only problem is that in the SQ. M. column, I cannot multiply the result (the empty cell) by a number (0.0929), as shown in the database header in the image. I keep getting #VALUE! as the result, instead of an empty cell.

This also messes with the end total, with it also giving the result as #VALUE!.

[Pat Stanford]

I think you are going to have to live with zeros in those cells instead of blanks. Change the formula to:

=IF('Space Schedule'.'Enclosed Liv'='Yes', space.area, 0)

this is a zero at the end.

That will make sure that every cell has a number in it instead of some having numbers and some having text. You are getting the Value result because you are doing arithmetic on a text field (the '') instead of a number. That bad result is then propogating to you final columns.

The only other possibility would be to modify your formulas in the m2 columns to use an if and not calculate if the ft2 column is blank. Not tested, but maybe something like:

=if(D2='','',D2*10.78)

I don't know if this will work for your grand totals or not.

[ewalker]

Pat:

The if function still works, but I got the SQ. M. column to also work by using =D4*0.0929 as the formula. Thanks a lot for the quick response.