Jump to content
  • 0

Calculations in Database sub-rows when summarized.


5600KDesigns

Question

Recently I have been working on building a template file for my drafting work in Spotlight. I feel like I have stumbled into an issue with the limitations with the built in Worksheet functionality of Spotlight and possibly the base Fundamentals as well. Basically what I am trying to do is pull information from the databases into the worksheet, summarize and count by Symbol, Cable Length & Type, etc. I then would like to use the count function to get a total of that particular item so I can do some math with it to calculate the spare and total counts  for my pull sheets.

 

What I have found is that the worksheets don't work this way when using the database row/sub-rows in 2022 SP2.1. For example, on my Hoist Pull Sheet worksheet, when I summarize the symbols listed in A2 and then do an =COUNT in D2 I get the proper count of the hoists in the drawing. In E2 I do the function =ROUNDUP((D2*0.2), 0) but it only calculates to 1, no matter if I have 1 or 15 as the count in a sub row of D2. in F2 I did a =SUM(D2, E2) and I somehow got it to say 11 + 1 equals 2.

 

What I am hoping to see in a future service pack update is for worksheets to recognize when a database row and it's sub-rows have been summarized by a column and to allow math to be done in columns following a summarized entry as if it is a spreadsheet. I know some may suggest that I export my worksheets to Excel, but I feel like that defeats the purpose of what I am trying to do with this template file and making my paperwork generation quick, easy and having a consistent look for my vendors and clients.

Screenshot 2022-01-12 212537.png

Link to comment

11 answers to this question

Recommended Posts

  • 0
4 hours ago, michaelk said:

Worksheets will definitely do that.  In column E and F:  make sure that you have those columns set to Sum Values.

Hey,

 

Thanks for the quick reply. I did just try that this morning and it still is doing the wrong math. It seems that it is doing the math on the original sub rows before doing the summarization in Column A. I may very well be doing something wrong, but I am not sure where. I have attached screen shots of the sheet as of this morning along with the formulas I am using. If I export to Excel I can get it to work as I want it to, but I am trying to eliminate that step and use Vectorworks without Excel.

F2 Formula.png

FullSheet.png

E2 Formula.png

Excel.png

Link to comment
  • 0
8 hours ago, michaelk said:

I night not be understanding exactly what you are trying to do.

 

Can you post a file with a worksheet and enough objects to be an example?

Yeah no worries, here's an example of what I am trying to do.

 

I did email with someone from Vectorworks Training and they did get it to work sort of. The difference between what I have here and what they sent me is they set the Roundup function to go to one decimal place instead of the nearest whole number. Their equation is in Column F while mine is in Column E on the worksheet.

MotorPullSheet_Example.vwx

Link to comment
  • 0

I am trying to remember the details, but there is certainly an issue with some situations with subrows and formulas.

 

I believe it is that the SUMmarize adds up the results of each subrow and does the calculation on those summarized values to get the summarized output.  Or maybe it is the opposite.

 

In any case I know there was a condition in the past where you could not get database subrows to SUMmarize and calculate the way I wanted them to work.

  • Like 1
Link to comment
  • 0

I found the bug report and the response (Working as Designed) the last time I fought an issue like this. I'm not sure if this is the issue we are fighting here or not.

 

Bug Report

Quote

In the attached file, multiplication and division are not working properly in the database rows.

The worksheet is very simple. The criteria is Type is Rectangle. Columns A and B are the AREA and Count. The database is SUMed based on the area.

The original intent was to get the average of the areas (I understand that having it summed by area the results should just equal the area. Eventually the criteria would be different so you could have multiple objects with different areas summed together).

I can not come up with a formula using either multiplication or division in the database rows that works correctly.

A formula of =A2/B2 appears to be multiplying the values instead of dividing.

A formula of =Area/Count gives the same result as =A2/B2

Even a simple division by a constant (Column E) of =A2/2 appears to not divide and returns the value in A2.

=A2 * 2 appears to return the value from A2*4.

=A2 div 2 (Column G) returns seemingly random results.

Tested this back to VW2015 and saw similar results.

 

 

Reply from VW

Quote

This appears to be working as designed. 

The provided file, which has a database row with the criteria, =DATABASE((T=RECT)), has a summarize operator applied to a single column, A. Columns other than A display the sum of each columns subrows. If an additional summarize operator is added to another column, that column will also be summarized to show a single value rather than the sum of the values.

Removing all summarize operators shows that the specified database row cell formulas then work as expected.

The forum user is expecting that the displayed summarized values will be used in calculation with non-summarized values, which is not the case. This behavior, using summarized values, cannot be changed.

Online help documentation:
http://app-help.vectorworks.net/2016/eng/VW2016_Guide/Worksheets/Database_Row_Sort_and_Summary_Functions.htm#XREF_81806_Database_Row_Sort

 

  • Like 1
Link to comment
  • 0
On 1/14/2022 at 10:50 AM, michaelk said:

If you don't use a database it works as expected.  

 

Hmmmmmmm.

 

 

MotorPullSheet_Example_1.vwx 3.32 MB · 2 downloads

Yeah on my template file I had been able to get the '=ROUNDUP' function to work and even have it round to the nearest whole number when not using a database row. For that particular sheet I was generating a pull sheet for a rigging package and had known item types that would not change based on the drawings.

 

Unfortunately that defeats the purpose of what I am trying to do with making dynamic drawing based worksheets for my template file.

Link to comment
  • 0
On 1/14/2022 at 12:46 PM, Pat Stanford said:

I found the bug report and the response (Working as Designed) the last time I fought an issue like this. I'm not sure if this is the issue we are fighting here or not.

 

Bug Report

 

 

Reply from VW

 

Pat, that may very well be the same issue I am looking at here. As the reply from VW said, when I don't summarize a column, the math works as expected.

 

If possible I still think this should be reported as a feature request or bug because as michaelk pointed out, the math works when using a spreadsheet and not database row.

 

In the meantime I did a workaround to get the worksheet to display correct numbers. As the VW training guy had shown in his example to me I took the Roundup function to the nearest tenth and then added 1. E.x. '=ROUNDUP((D2*0.2)+1, 1)' This did give me such things as 13.2 motors, but that is remedied by changing how that column displays and changing it from "General" to "Decimal" and then going to 0 decimal places.

Link to comment
  • 0
9 hours ago, 5600KDesigns said:

Pat, that may very well be the same issue I am looking at here. As the reply from VW said, when I don't summarize a column, the math works as expected.

 

If possible I still think this should be reported as a feature request or bug because as michaelk pointed out, the math works when using a spreadsheet and not database row.

 

In the meantime I did a workaround to get the worksheet to display correct numbers. As the VW training guy had shown in his example to me I took the Roundup function to the nearest tenth and then added 1. E.x. '=ROUNDUP((D2*0.2)+1, 1)' This did give me such things as 13.2 motors, but that is remedied by changing how that column displays and changing it from "General" to "Decimal" and then going to 0 decimal places.

It's still not working quite right because of the Roundup function issue, but it is at least closer to what I am looking for.

MotorPullSheet_Example_PartialFix.vwx

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...