Extracting total data values from Database Header rows

[Marc Davies]

I am trying to find percentages of areas in database rows against the total value in the header for all areas.

However I don't seem to be able to extract the total area values without a circular referencing problem. i.e. I cannot use the total value from the database header row to use in a formula in the percentage values column. This is surely a simple issue, but I am scoring well below "SIMPLETON" for this task. Anyone else solved this recently?

[Silvano]

Until the experts arrive:
Click on the % column header. Type it:
 =(click on the areas header)*100/(TYPE the total area)

[Pat Stanford]

Not at all simple. I have filed this as a Bug against VW2023.

 

There is no really good work around.

 

The best answer is to use a function using criteria in a Spreadsheet row and use that for the calculation.

 

What are you trying to get the percentage of and I can probably help you come up with a work around.

 

 

[Marc Davies]

Thanks, Both.

Silvano, your solution works for Spreadsheet cells but not for Database header row’s unfortunately as it sets up a circular reference. 
 

I have tried to reference the Area cell into a non-database row, as a Value, but no joy there either. My temp solution is to ask users to manually type the header Area value into the formula
= Area / <<typed total Area value>>

which works for each database row with % column set to “Percentage” format.

 

i need to be able to extract the total Area as a value into a spreadsheet cell. I’m sure you will have a solution Pat.

 

thanks,

Marc

[Marc Davies]

Ah, sorry Silvano. Yes, i missed the word “TYPE “ in your reply. That is exactly what I have had to do. 🙂

[Pat Stanford]

Are you looking at a cell that actually has a formula of =Area?  Or are you working with something like a Space object and referencing a Record.Field (ie 'Space'.'Gross Area')?

 

Makes a difference in the solution.

[Marc Davies]

Hi Pat, 

formula (=AREA) at the moment, in a database header.

 

The database has criteria looking at a series of polygons, in this instance, with a particular Record set. I might well be looking at Records attached to symbols on another occasion, but my Database criteria will hold the reference to the Records, not the cell formula like your second example.

 

Would it help if I changed my criteria/formulae

 

M

 

 

Edited February 12, 2023 by Marc Davies

[Silvano]

5 hours ago, Marc Davies said:

Ah, desculpa Silvano. Sim, perdi a palavra “TYPE” na sua resposta. Isso é exatamente o que eu tive que fazer. 🙂

Marc Davies, as you saw, this solution would be temporary, just to work, because you have to type again every time you change areas. Your problem is important and I would also like a better solution.

[Pat Stanford]

So since you are using the =Area function it actually makes it easier. But you will need to put the SUM of the areas into a Spreadsheet cell.

 

Most of the Worksheet functions use the criteria of the database if they are used in a Database row. Those same functions can be put into a Spreadsheet cell and have a Criteria passed to them as a parameter.

 

So right click on the Database header row and choose Edit Database Formula. The formula for the database will display in the Formula bar. Copy everything inside the =DATABASE() function. That is the actual formula for the criteria of the database.

 

In a spreadsheet cell enter a formula of =Area( and paste the copied criteria and put the final closing parenthesis.

 

You should now have a formula that will give you the total area and that will update when the drawing changes that you can use as the denominator of your percentage formula without making it a circular reference.

 

HTH.  Ask again if not clear enough.

[Marc Davies]

Brilliant Pat.
That is great. i understand the concept and   knowing how i can get Database info directly into a spreadsheet cell will help me in other areas also no doubt. As is so often the case with your advice, it feels like another Vectorworks door has opened to me.

Many thanks.