Worksheet Sum of specific Record Fields

[Elite Exhibits]

1. Create a Record Format. 2. Attached to Symbols. 3. Assign Values to fields based on specific symbol (Example Cost in dollars - Weight in pounds) 4. Place symbols on drawing. 5. Create Report / Worksheet. 6. Database Report lists Symbols in drawing.  7. Add Database headings to Show Symbol name, specific Record fields etcetera. 8. As expected, the Record Fields show correctly (... for example) Cost in dollars for each symbol. 

 

So ... Used the Sum Functioning for the Record Field "Cost"  and it returns a simple count - i.e.: Number of Symbols in the column, NOT (... for example) the total Cost in Dollars. (The number at the top in the Database Header is also a similar "count") 

 

Question to answer:

    It would appear that the Sum function would need to be =Sum(E2.1 .. E2.18) to add the specific values in those Database E2 cells? Or am I thinking like this is Excel.?

There are lots of Worksheet / Summation discussions in the Knowledge Base / Community, none that I have found to show how a list of specific Record Field Values are summed.

 

Somewhere I am missing something. 

 

Intent is to create a Group of Symbols, with attached Costs in a Record Field, then have a DataBase Worksheet, show the Symbols, in the drawing, the per Symbol price, and a TOTAL price (or Total Weight, Total Shipping Volume etcetera) of the project as it proceeds in the design phase. The designer gets a quick sense of where they are compared to the budget.

 

Any Suggestions (Thanks ... )

[Pat Stanford]

In your cost and weight fields did you include characters other than numbers and number separators ('.', ',')? Are the fields formatted as Text or as some form of Number?

 

If the fields have other characters, then they have to be Text type fields. VW can not do math on text, only on numbers. It can Summarize (collapse subrows that have the same value into a single subrow), but is can not do a mathematical sum.

 

Text fields are always counted (or math summed) as individual instances.

 

You may be able to create a function using SubString and Value functions that will extract the numerical data and then do a math sum on that.

 

Or, you may have to go back and edit your record format to use numerical fields. The problem then is when someone else uses your format and enters prices in Canadian Dollars and weights in Kg instead of pounds, your sums will be off.

 

I keep hoping that VW will add additional "known units" (like length and area are now) to the program so you could define a field as currency or as weight and have the conversions between different units done for you.

 

HTH.

 

 

[michaelk]

7 hours ago, Pat Stanford said:

I keep hoping that VW will add additional "known units" (like length and area are now) to the program so you could define a field as currency or as weight and have the conversions between different units done for you.

YES!  VW could use formats for units of weight, electrical current, currency, and luminance.  

 

And instead of using document unit formats it would be nice if the unit format was selectable in each worksheet cell.

[Pat Stanford]

I think this actually needs to be settable at both the document level and worksheet or other display level.

 

If you have a field that contains weights, I would like the field to know that and be able to do 22# + 10 KG and have it properly give me an answer of either 44# or 20 kg depending on the document units.  In the worksheet I would then like to be able to configure a cell to display that data in either # or Kg and not have to depend on what the document settings are.

[MullinRJ]

I am attaching a sample file that contains two Worksheets that are able to convert weight fields with units to numeric values that can be used directly in calculations. The weight values can exist in WS cells or in record fields attached to objects in the drawing. I did not venture too far into the plethora of units available worldwide, but this example does show how to convert between kilograms (Kg) and pounds (Lb) for displaying or summing weights in a single unit base. As a bonus, ounces (oz avoirdupois) can be used as an input value, but I did not include a function to convert to ounces as an output value. I'll leave that as an exercise to anyone who is feeling cerebrally sedentary.

 

Note, though it is possible to mix weight units for separate objects and the worksheet will convert them accordingly, mixing units for a single object is not yet supported. That is, it is not possible to have a weight field contain "3 lb 2 oz".

 

This is not a silver bullet to the task of summing weights in VW Worksheets, but it does provide a way to retrieve weight values attached to objects, possibly with mixed units, and perform calculations in either pounds or kilograms. A similar approach could be applied to monetary values. If anyone needs help with similar functions, please PM me or contact me offline. If anyone finds this useful, please let me know.

 

HTH,

Raymond

WS Weights.vwx

[michaelk]

Nice!!!