AVERAGE, MAX and MIN in database headers.

[tavbut]

Hi everyone,

 

I would like to add this functionality to the database interface inside worksheets. I would want to have control over the header mode and subrow mode, like this:

Database Header options:
    -Sum Values. This is the way the headers work right now. It adds up all the values of every subrow.

    -Average. This would show an average of all the values in subrows.

    -Min value. This would show the minimum value of all the values in subrows.

    -Max value. This would show the maximum value of all the values in subrows.

 

Now, when summarize items is turned on we only have two options: if all the values are de same we get the value as it is, and if they are different we get a "--". If the "sum values" check box is turned on all the values are added up. I suggest to expand that capability as follows:

Database subrow options:
    -Sum Values. This is the way "Sum Values" option work right now.

    -Average. This would show an average of all the values.

    -Min value. This would show the minimum value of all the values.

    -Max value. This would show the maximum value of all the values.

 

 

Let me know what you think. Maybe there is some other way of looking at the issue at hand?

 

MacBook Pro (14-inch, 2023), Apple M2 Max, 64 GB.

OS: macOS Ventura.

Vectorworks 2024, Designer

 

[tavbut]

On 9/26/2023 at 4:58 PM, tavbut said:

Hi everyone,

 

I would like to add this functionality to the database interface inside worksheets. I would want to have control over the header mode and subrow mode, like this:

Database Header options:
    -Sum Values. This is the way the headers work right now. It adds up all the values of every subrow.

    -Average. This would show an average of all the values in subrows.

    -Min value. This would show the minimum value of all the values in subrows.

    -Max value. This would show the maximum value of all the values in subrows.

 

Now, when summarize items is turned on we only have two options: if all the values are de same we get the value as it is, and if they are different we get a "--". If the "sum values" check box is turned on all the values are added up. I suggest to expand that capability as follows:

Database subrow options:
    -Sum Values. This is the way "Sum Values" option work right now.

    -Average. This would show an average of all the values.

    -Min value. This would show the minimum value of all the values.

    -Max value. This would show the maximum value of all the values.

 

 

Let me know what you think. Maybe there is some other way of looking at the issue at hand?

 

MacBook Pro (14-inch, 2023), Apple M2 Max, 64 GB.

OS: macOS Ventura.

Vectorworks 2024, Designer

 

@Pat Stanford I'm sorry to drag you into this topic, I think maybe you know how I could work around this issue I'm having. You have helped me in the past so I figured I ask directly.

Do you know of any scrip or command that converts a database into a spreadsheet, placing in each cell the formula that would get the same result as when it was a database?

It's complex for me to explain...

Thanks in advance!

Edited January 3 by tavbut

[Pat Stanford]

No problem about dragging me in, but I don't think I am going to be much help.

 

1. No such script or command exists to my knowledge.

2. While it is possible to write a script that would do something that would probably satisfy your need, by the time you include all of the error checking and edge cases necessary to make it work the way you want, I think it would be between 50 and 100 hours of work. So unless you are ready to spend thousands or tens of thousands of dollars for this functionality I don't think it is going to happen.

3. A live version of a complete spreadsheet that was equivalent to a spreadsheet with a database row would be a hard thing to do. You would have to get a handle to the object in each sub-row of the database and then generate formulas equivalent to the formulas in the database header row. And even if you made this work, you would still not have the sort ability of a database subrow. Nor would you be able to just update the spreadsheet to include items that were not in the original database subrow.

4. An "image" version where instead of pulling the actual objects and full formulas, but rather just grab the data out of each database subrow cell and store it in an equivalent spreadsheet cell would be easier, but it would still have many issues to overcome. The biggest would be how to update the "image" version so you would not have to recreate all of the custom formulas you put into that version

 

Sorry for the bad news, but unless you have a lot of time or a lot of money to throw at this, I don't think there is an easy way to proceed.

 

 

[tavbut]

Thanks again for your input!

I agree with you, it's a mayor challenge to achieve "converting" database to cell by cell formula. I'm facing all of the issues you describe trying to accomplish that.

So, from your point of view, would it be an impossible task for Vectorworks to include the drop down menu options for Database Headers and Database Subrows as I describe above? Or should I give up the idea? I'm just curious if you see some fundamental flaw in the functionality I'm proposing.

 

[Pat Stanford]

Not an impossible task, but probably not very high on the priority list.