Best Practices with SpreadSheets

= Introduction = This is a draft of what I imperfectly understand as best practices ( and tips, and gotchas ) for parametric modeling in FreeCad using SpreadSheets.

Why
For me the point of a parametric model is that measurements ( and sometimes other features ) can be changed by changing the value of an input variable or parameter. The parameters may be stored in many different locations in your model but I find that a spreadsheet is the most useful. You can just look at a table of values, their names, and perhaps some notes about them. The spreadsheet can also do some calculations like changing radii to diameters and vise versa.

How
Use the spreadsheet workbench to add a spreadsheet. Then put you important dimensions in a cell. I find it most useful to give each cell a name or alias. If you do not do this the cells have non-informative names like B5. I normally also type the alias to the left of the column with the data, so when I look at the spreadsheet I can see both the name and the value. It is often useful to tile your workspace so you can see the spreadsheet and the model as you enter formula.

= Names in the SpreadSheet = Like programming good names can really help you keep track of what you are doing. So be systematic in making up your names. Some bad names:


 * part_size   ( a radius, diameter....? )
 * distance    ( what direction, from what? )

Better names:
 * bolt_diameter
 * bolt_length
 * shaft_dia

But note that you should try to be consistent: try to use dia or diameter but not both.

Finally all names in a spreadsheet are referenced outside of it by using the spreadsheets name, so I usually rename it from the default "Spreadsheet" to the easier to type "ss".

= SpreadSheet values in the Model = Most values/measurements in the model that can be entered as numbers can also be entered as formula, and the formula can use the names from the spreadsheet as values. Suppose for example your model has several shafts in different sketches which should all have the same radius, and also that you have holes in your model for the shafts to fit into. How do you change the radius of all the shafts and holes?

Here is an approach:


 * In the spreadsheet have a cell shaft_radius, and another shaft_clearance, each with a numerical value. The clearance is the amount ( in radius ) that the hole needs to be larger than the shaft for the parts to fit.

ss.shaft_radius. Note that as you type FreeCad will try to help, if you type ss.s then FreeCad will suggest shaft_radius, and you can just click on it.
 * In a sketch where you have a circle for the shaft apply the radius: when the value box come up press the formula icon at its right hand side, or hit the = sign. Now you can enter the formula:


 * For a shaft hole try the radius ss.shaft_radius + ss.shaft_clearance

= Units = FreeCad can be fussy about units and not always intuitive. In one case I was getting errors in a sketch with measurements drawn from a spreadsheet. Getting rid of all the units seems to default FreeCad to mm, and when I cleared all the units in all the involved measurements the problem when away. Documentation on the spreadsheet seems to warn against problems with units but rather than getting rid of them recommends just consistent use everywhere. The safest units seem to be mm but others may work as well. Note that when adding ( 2 + 5 ) mm is not equal to 2mm +5mm, the units cannot be treated as a factor. Put the units on as soon as possible and as consistently as possible.

= Master Sketches =

Rather than use a spreadsheet I have seen some people use a "master sketch" The idea here is that all the important dimensions are drawn out in a sketch. This sketch is never padded, pocketed or drafted, but other sketches ( usually parallel to it ) use the connect to external geometry feature to synchronize the parameters. I have tried this and like the way it is more visual than a spreadsheet, but it also has limitations: it is harder to do math in it, it is unclear what to do if you have three dimensions to parameterize, it is harder to annotate. Despite the limitations you may find it useful. When I use it now, and I still do, I link its parameters back to the spreadsheet and more or less get the best of both worlds.

= The Topological Naming Problem ( TNP ) =

This is a problem which causes a model to break .... you do not get what you expect, and it usually includes elements in your model that report errors so they will not recompute. The problem seems to be particularly severe in highly parametric models which are fine until you change one of the parameters. I do not want to go too deeply into the problem here ( partly because I am not an expert ) but I think many of the best practices are developed for highly parametric models so they will not fail. I think the crux is that a model, like computer code, is held together by names and numbers. When you recompute an object, say even a cube, you get a new object and the names of its faces, edges.... may change or be in different places. Other objects are attached to the names and now may not be where you want them or even in a place that makes sense. This can easily come up when you attach a sketch to the face of an object. So we will recommend practices that avoid this sort of attachment.

= Placement of Sketches =

You should place sketches on objects that will not move, or at least will not move in a way that changes it name. Here are some ways.

Planes Through Origin
A sketch attached to any of the 3 planes passing through the origin is safe from the TNP as these are named invariant "things" If you can use these planes, if you are making holds all the way through an object you can start from the bottom as well as from the top.

Move the Sketch Placement
When the drawing of the sketch is "closed" there are still a lot of changes that can be made Data tab of the sketch in particular see:

Base -> Placement -> Position ( x, y, z )

The directions x, y, and z are not the x, y, and z of the overall model but of the plane of the sketch. z is always out of plane and x and y in the plane so the whole thing is a right handed coordinate system. Move z to get the plane to move perpendicular to z. This amount can ( and should ) be entered from the spreadsheet. Here is an example of the idea.

Suppose you have a cube constructed from a sketch sitting on the xy plane and extending up in the z direction ( lets this be cube_z_size in the spreadsheet ). You want to "drill" some cylindrical holds on the top face half way down through the cube. The simple thing to do is attach a second sketch sitting on the top face. Don't. It is very liable to break on resizing. Instead place you sketch on the xy plane then set the sketches z position to cube_z_size. It now moves with the cube as if it were attached but is not subject to TNP. You set the depth of the holes ( in the pocket ) to cube_z_size/2 and now one parameter in the spread sheet changes everything in the model that needs changing.

Datums
A datum is another way of moving a sketch placement. A datum ( for planes ) is simply a plane that you can position anywhere in a model body at any angle ( all probably controlled by a spreadsheet ) and then you can attach sketches to the datums. I am not sure of what the trade offs are between "placed" sketches and datums so can not tell you when to use one or the other. It seems a bit more work to construct a datum, but then it is a bit more obvious how the sketch is being placed.

= Name the parts of the Model =

After I have constructed a model I am often a bit confused as to what is what in the model and how the pieces relate to each other. Because of this I often name parts of the model and think that this is a best practice for some parts of some models. Say you have bolt holes based on a sketch. You could name the sketch bolt_holes, then pocket them and name this bolt_holes.... well not quite two things with the same name is one route to insanity. So I prefix them po_ for pocket (pd- for pad ), s_ for sketch. The names are then s_bolt_holes and po_bolt_holes. As usual with names try to pick a scheme and stick to it.

= In the Sketch =

Center around the Origin of the Sketch, if it is in the wrong place use the sketch

= Order of Operations = I have seen some stuff recommending you model in a certain order of operations. I do not understand this well enough to make recommendations.

= What Ends the Parametric Model Chain = When you add some features to a parametric part of a model, you may get a result that is not longer parametric: Say feature A is the basis for feature B. If changes in feature are not reflected in a change to feature B then the parametric nature of the model is broken at the transition A -> B breaks the model chain. In most cases operations do not break the chain, the following table documents some modeling operations and effects:

= Models that Illustrate these Practices =

All these models may not be perfect, but attempt to show best pratices. The links may within this wiki or to external sites.

Microscope Adapter Model

= Videos =

Some Briefly Reviewed Videos
Reviews take a lot of time, these pretty much make sure the video is on topic, but not a whole lot more.


 * Designing with spreadsheets in FreeCAD - YouTube -- seems on topic
 * Designing a small cabinet using FreeCAD and spreadsheets - YouTube --- follow up to above, a real world example.  Connecting to Excel.
 * FreeCAD - Using the Spreadsheet for Parametrized Sketch based Parts. - YouTube -- seems on topic
 * FreeCAD 0.18 Link Spreadsheets to Part Design - YouTube -- seems on topic
 * How to use Variables and Equations to Design in FreeCAD|JOKO ENGINEERING| - YouTube -- nice basic intro.
 * FreeCAD - Basics - Spreadsheet Alias - YouTube -- nice basic intro.
 * FreeCAD Tutorial 2 | Cylinder Parametric Model using Spreadsheet (Basics) - YouTube -- nice basic intro.
 * FreeCAD for Woodworkers 03 - Parametric Routing Template with Spreadsheets - YouTube -- basic intro + alias manager + draft application.
 * FreeCAD - Intro to Parametric Modelling - YouTube -- nice intro, uses RealThunder version of FreeCad
 * Making a FreeCad File Parametrized from a Spreadsheet - YouTube -- covers the basics
 * FreeCAD Tutorial | Exercise 5: Design of Parametric Model of Gear Pairs Using Spreadsheet Workbench - YouTube -- intro with gears
 * FreeCAD: Make Your Own Part Library |JOKO ENGINEERING| - YouTube -- nice
 * FreeCAD Tutorial - Framing BasicShed Window - Sketch Driven, Part Attachment and Spreadsheet Driven - YouTube -- fairly complex with 2 spreadsheets
 * FreeCAD Excel CSV import 2019-05-13 - YouTube -- seems on topic, csv is nice addition
 * FreeCAD 0.18 Expressions - YouTube -- expressions are important, this covers in detail
 * FreeCAD Beginner - #13 Spreadsheet for dimensions - Drawing tutorial not only for 3D print - YouTube -- basic intro
 * FreeCAD spreadsheet / parametric design - YouTube -- some basics, no verbal narration
 * (59) FreeCAD | Spreadsheet, Part Design, Part Workbench - YouTube -- basics for spreadsheets with several workbenches.
 * FreeCAD Tutorial Part 3/3 | Creation Parametric Assembly of Wind Turbine Using Spreadsheet - YouTube -- a lot of content about assembly, but includes use with spreadsheets.
 * FreeCAD Parametric Modelling - YouTube -- spreadsheets with sketches
 * FreeCAD Tutorial 27 - Spreadsheet Workbench - YouTube -- looks like a good intro -> in German.
 * FreeCAD Spreadsheet Parametric Modeling Failure. - YouTube Sometimes things go wrong.
 * FreeCAD For Beginners p.2 - Parametric Modelling - YouTube
 * FreeCAD Tutorial - Basics - NEMA Stepper Motor - YouTube
 * FreeCAD - Parametric Design - YouTube -- from basics to a macro making setting the alias easier, use a "2D" approach in spreadsheet
 * BearingPeg 2.0 - FreeCAD Customization Example - YouTube a second video to "FreeCAD - Parametric Design - YouTube" a realistic part being designed.
 * FreeCAD spreadsheet - parametric design - YouTube a jewellery design application with 2 spreadsheets.
 * 046 FreeCAD Part Design Draft and new Spreadsheet Alias Control - YouTube -- new alias control, a feature I missed ( v.19 )
 * How to create a ifcRoom from a Excel /CSV List with FreeCAD - YouTube -- integration with Excel/CSV is not commonly covered. Also an interesting macro.

Some to Watch and Review
This may be very hit or miss, would like to move to the category above.
 * freecad spreadsheets - YouTube A youtube search, and yes there are other video sources.


 * 5# Tuto 3 Freecad l'atelier tableau (Spreadsheet) - YouTube
 * (59) Crazy Simple 3D Printed Enclosure with FreeCAD 0.18 Parametric Design Part 1 - YouTube -- real world example, nice.  First in a series.

temporary draft line

 * (59) FreeCAD Tutorial - Parametrischer Möbelbau mit der PartDesign und Spreadsheet Workbench - YouTube
 * (59) FREECAD #122 - L BAR - SPREADSHEET - YouTube
 * FreeCAD - Part variation with spreadsheets and Inventor Workbench - YouTube
 * (59) Howto model the Mortice and Tenon joint in FreeCAD - YouTube
 * "Fast 3D Model Creation Using FreeCAD" - Anool Mahidharia (KiCon 2019) - YouTube
 * BIM with FreeCAD - Introduction - YouTube
 * (59) FreeCAD - Parameterized Lead Screw using Construction Geometries - YouTube
 * (59) FreeCAD 0.18 Modeling Wooden Dowel Pins - YouTube
 * (59) Tuto || FreeCad 0.18 : apprendre à utiliser les tableurs - YouTube
 * (59) FreeCAD Tutorial Part 1: The Part Workbench - YouTube
 * (59) Woodworking with FreeCAD - YouTube
 * (59) Crazy Simple 3D Printed Enclosure with FreeCAD 0.18 Parametric Design Part 6 - YouTube
 * FreeCAD Tutorial | Exercise 7: Design Parametric Model of PVC Pipe Fitting Tee in PartDesign - YouTube
 * BIM with FreeCAD - Expressions - YouTube
 * (59) Design and Print an Arduino Box with FreeCAD - YouTube
 * What's new in FreeCAD dev.(LinkStage3) - December update. - YouTube

= Macros For Better SpreadSheet Use = Just starting this section, a draft inside a draft. Macros may or may not help you keep to best pratices. Here are a few. Some little reviews here, just looking at defining and clearing macros.

General

 * "Spreadsheet tools" site:forum.freecadweb.org at DuckDuckGo
 * (2) tools for the spreadsheet - Page 2 - FreeCAD Forum
 * (2) tools for the spreadsheet - Page 2 - FreeCAD Forum

FreeCAD_AliasManager pgilfernandez
Review: ( russ_hensel, dec 2020, Version: 0.19.23141 (Git) )
 * Macro Alias Manager - FreeCAD Documentation
 * pgilfernandez/FreeCAD_AliasManager: This macro helps managing aliases inside FreeCAD Spreadsheet workbench. It is able to create, delete, move aliases and create a 'part family' group of files
 * Just want to create alias's from names in one column to another column.
 * Installed with Addon Manager, this went fine. +
 * Window Drops behind main window -
 * Has Help +
 * Use of "from" which seems to mean "to" was confusing to me. This may just be me. -
 * Hint: names must be in column A
 * Alias were created +++++

SheetProperties uribench

 * uribench/SheetProperties: FreeCAD Macro for manipulating properties of spreadsheet cells

Review: ( russ_hensel, dec 2020, Version: 0.19.23141 (Git) )
 * Just want to create alias's from names in one column to another column.
 * Manual install, this went fine following directions at github but not sure about inclusion of __init__.py +
 * Window Drops resists dropping behind main window ?
 * Help no, but some error messages are useful ?
 * Hint.. if not working close and reopen seems more useful
 * Works... a bit fussy but works, never figured out how custom or refresh were useful.

Macro SpreadsheetTools
Review: ( russ_hensel, dec 2020, Version: 0.19.23141 (Git) )
 * Macro SpreadsheetTools - FreeCAD Documentation
 * Just want to create alias's from names in one column to another column.
 * Installed with Addon Manager, this went fine. +
 * Window Drops behind main window -
 * No Help -
 * Does not work for me, throws many errors which seem to indicate it is written in Python 2.x

Macros recipes

 * Macros recipes - FreeCAD Documentation

= Links to Related Material =
 * Spreadsheet Workbench - FreeCAD Documentation
 * Manual:Using spreadsheets - FreeCAD Documentation
 * Customisable Parametric Design using Spreadsheets - FreeCAD Forum
 * Forum Search and Specific Pages: best pratices site:forum.freecadweb.org at DuckDuckGo []
 * - Intro to Parametric Modelling - YouTube
 * Feature editing - FreeCAD Documentation


 * Best practice: fuselage + wing - FreeCAD Forum
 * Some Tutorials on Methodology (v0.17) - Master Sketch, Skeleton Geometry, Spreadsheets - FreeCAD Forum