Spreadsheet values do not update when the Master Spreadsheet is changed

Post here for help on using FreeCAD's graphical user interface (GUI).
Forum rules
and Helpful information
IMPORTANT: Please click here and read this first, before asking for help

Also, be nice to others! Read the FreeCAD code of conduct!
PAS_Drafter
Posts: 338
Joined: Mon Aug 02, 2021 6:29 am
Location: California, USA

Spreadsheet values do not update when the Master Spreadsheet is changed

Post by PAS_Drafter »

Code: Select all

OS: Windows 10 Version 2009
Word size of FreeCAD: 64-bit
Version: 0.20.29177 (Git)
Build type: Release
Branch: releases/FreeCAD-0-20
Hash: 68e337670e227889217652ddac593c93b5e8dc94
Python 3.8.10, Qt 5.15.2, Coin 4.0.1, Vtk 8.2.0, OCC 7.6.2
Locale: English/United States (en_US)
Installed mods: 
  * Assembly4 0.12.0
  * CurvedShapes 1.0.3
  * Curves 0.4.4
  * fasteners 0.3.45
  * fcgear 1.0.0
  * MOOC 2022.4.21
  * sheetmetal 0.2.49
PedjaS wrote: Thu Jul 21, 2022 1:59 pm example file?
openBrain wrote: Mon Jul 25, 2022 12:45 pm OK, here are my findings.
I originally put this in the following post as I was following that thread as I had similar problems with Spreadsheet WB. However, after thinking about it. I decided that this is a different issue and decided to give it, its own topic.

Changing value in Spreadshet does not update linked Constraints

I was finally able to put together some files that cause me the problem with the spreadsheet values I have not updating properly. I don't think this is the same issue as reported in the above post, but it might be related.

In my case, I'm working with Assemblies. I have a Master Spreadsheet that I use to set and send values to other spreadsheets for the various parts I'm working with on my assembly. The Master acts as a control location for changes I want to make to move various parts as well as stores common measurements that I might need on two separate parts. This allows me to keep my parts parametric and reduce the chances I run into the TNP. It is a bit of work to do it this way, but I rarely have problems. It is advisable to load the Master file first when loading my assembly, although it normally works if you don't too.

In my example, I'm building a telescope mount. I've built the parts and assembled them. I have 4 parts that make it up so far. 01_Pedestal_Base, 02_Level_Mount_Bottom, 03_Level_Mount_Top, and 04_Horizontal_Adjustment_Plate. The assembly is held by Telescope_Assembly.

So to duplicate the problem I am having, first, open all the files and navigate to the Telescope_Assembly file to show the top view of the mount.

TopView.PNG
TopView.PNG (20.57 KiB) Viewed 1172 times

Notice that this is built so that the top plate can be rotated 15 degrees to the left or right with two bolts that allow fine adjustment. The three inside bolts, when loosened, will slide on a curved slot.

I control the rotation of this top plate in the Master, SSS_Master spreadsheet. You can change the value of the "Side to Side Degrees" value shown highlighted in Red.

Master.PNG
Master.PNG (21.33 KiB) Viewed 1172 times

So here is the problem. When I change the value in the Master Spreadsheet to something between -15 to 15 degrees, it updates the spreadsheet. If you go back to the Telescope Assembly tab and look at the top, when you hit the refresh key, the top moves as expected. However, the fine adjust bolts do not. They are controlled by another spreadsheet but should be updated at the same time this other value is updated.

Rotated.PNG
Rotated.PNG (23.51 KiB) Viewed 1172 times

If you go to the 03_Level_Mount_Top file and then open the SSS_LevelMountMount spreadsheet, you will see the following info.

TopLevelSheet.PNG
TopLevelSheet.PNG (45.62 KiB) Viewed 1172 times

The "Polar Alignment Adjustment" value is imported directly from the Master Spreadsheet and should have changed to the correct value after I entered in the master spreadsheet. If I right-click on the cell and "Recompute", the value updates to the change. However, there are two more values on the spreadsheet that are supposed to update when this cell changes (Polar Align Bolt Left and Polar Align Bolt Right). However, they do not change.

I can hit the Refresh button (as it is active), but that doesn't force those cells to recompute. I have to manually go to each cell, right-click, and Recompute. They then update as expected.

If I go back to my Telescope Assembly tab to look at the to view, I can hit the Refresh button and not the fine-adjust bolts now move to where they are supposed to.


Moved.PNG
Moved.PNG (22.46 KiB) Viewed 1172 times

Now if I want to change the value to another one, I have to repeat this whole process, change the Master value, recompute the three cells in he SSS_LevelMountMount spreadsheet, and then recompute and it will move again.

This is a relatively simple assembly here with only a few moving parts where it is not too hard to find the right cells to update. However, most of my assemblies are much more complicated with a lot more parts tied together and the only way I can get them to update any links if I change the Master is to save the file, close it, and then open everything back up. MOST of the time as each page is loaded, it then correctly updates the values. However, I have ran into a few situations where that didn't happen and I've gone on to keep building and not have the latest information. When It does update later, I find that I have to go back and fix things.

Anyway, I have noticed that version 20 is MUCH less reliable when it comes to updating links. I did not have this problem often with version 19. when I did, it was usually because I had an error somewhere in a spreadsheet that was holding up the recalculating.

I'm hoping that those of you that understand that back side of this can figure out what is going wrong and come up with a fix for me. If you have any other questions, please let me know. Now that I have a smaller set that I can provide it is easier for me to give examples.

I'll upload the files in the next post as I've run out of upload files on this post.
PAS_Drafter
Posts: 338
Joined: Mon Aug 02, 2021 6:29 am
Location: California, USA

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by PAS_Drafter »

Here are the files to duplicate the problem.
Attachments
Telescope.zip
(563.14 KiB) Downloaded 32 times
PAS_Drafter
Posts: 338
Joined: Mon Aug 02, 2021 6:29 am
Location: California, USA

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by PAS_Drafter »

Code: Select all

OS: Windows 10 Version 2009
Word size of FreeCAD: 64-bit
Version: 0.20.1.29410 (Git)
Build type: Release
Branch: releases/FreeCAD-0-20
Hash: f5d13554ecc7a456fb6e970568ae5c74ba727563
Python 3.8.10, Qt 5.15.2, Coin 4.0.1, Vtk 8.2.0, OCC 7.6.3
Locale: English/United States (en_US)
Installed mods: 
  * Assembly4 0.12.0
  * CurvedShapes 1.0.3
  * Curves 0.4.4
  * fasteners 0.3.45
  * fcgear 1.0.0
  * MOOC 2022.4.21
  * sheetmetal 0.2.49
I just noticed that 20.1 had been released at some time. I upgraded to it and confirmed the same issue is going on.
Syres
Veteran
Posts: 2893
Joined: Thu Aug 09, 2018 11:14 am

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by Syres »

Have a look at the commits listed in https://github.com/FreeCAD/FreeCAD/issu ... 1248589919 , it will be very easy to prove if it's the root cause for this regression also.
Syres
Veteran
Posts: 2893
Joined: Thu Aug 09, 2018 11:14 am

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by Syres »

I tried it with build 0.20.26733 and it's no different but if I create two new files each with a spreadsheet with one as a driver and the other as driven in order to replicate a simple example, it works as expected with the cell in the driven file going to #PENDING before doing a Recompute.
PAS_Drafter
Posts: 338
Joined: Mon Aug 02, 2021 6:29 am
Location: California, USA

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by PAS_Drafter »

Syres wrote: Wed Sep 28, 2022 11:33 am I tried it with build 0.20.26733 and it's no different but if I create two new files each with a spreadsheet with one as a driver and the other as driven in order to replicate a simple example, it works as expected with the cell in the driven file going to #PENDING before doing a Recompute.
This is something I have noticed as well and one of the reasons it took me some time to find/create an example where the problem showed itself. When I tried to make a simple two-file Master/Read example of the problem in the past, it would actually work as expected as you have proven. It is like the complexity has to reach a certain level before it starts to fail.

There is another bug/problem I run into a lot of times when I delete/insert rows and have joined cells. For some reason the joined cells properties get shifted to the one besides it after a delete. Additionally, the cells below that one do not get shifted up properly and I have to go back in an manually shift things around to get it straightened out. I have not been able to duplicate that one to submit yet, but I wonder if when this happens might have something to do when this other error happens. I'll continue to try and find an example of this other error I can reproduce and submit that when I find it.
User avatar
Shalmeneser
Veteran
Posts: 9474
Joined: Wed Dec 23, 2020 12:04 am
Location: Fr

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by Shalmeneser »

(( I don't answer any question :
(( * you should use different colors for different parts
(( * all these Bodies are different objects ?
(( * a proposal for the plate sketch (Sketcher_CompCreateRegularPolygon)
Attachments
telescopePlate_SHALM.FCStd
(9.7 KiB) Downloaded 18 times
PAS_Drafter
Posts: 338
Joined: Mon Aug 02, 2021 6:29 am
Location: California, USA

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by PAS_Drafter »

Shalmeneser wrote: Wed Sep 28, 2022 3:46 pm (( * you should use different colors for different parts
I understand the suggestion as it would make it easier to see where each part on the assembly goes. I've never organized my parts that way as when I color parts, I normally I color each part as it would appear in real life like I'm building a model. I didn't bother coloring this one since it was a small assembly and it was primarily for my benefit. However, your point is a good one and I can see some real value of doing it your way. I am considering changing my ways. :-)
Shalmeneser wrote: Wed Sep 28, 2022 3:46 pm (( * all these Bodies are different objects ?
Right or wrong, the way I have been doing things is that each "Body" represents a different piece I have to send to our plasma table to cut out or a piece we cut from metal stock. Each "Body" is then welded together as a completed "Part". I then use Assembly to put the "Parts" together. I have no drafting training so that might not be the right way to do things, but it has worked for me. I've done the best I can in the last year to figure out how all of this works and I'll be the first to admit I might not be doing it right.
Shalmeneser wrote: Wed Sep 28, 2022 3:46 pm (( * a proposal for the plate sketch (Sketcher_CompCreateRegularPolygon)
Funny you should mention that. I actually did start off with a Regular Polygon when I first designed that part. However, after I curved the edges, changed ideas multiple times, etc. that regular part ended up disappearing. I never thought about adding the equilateral triangle to the middle to make it easier to find my 120 degree marks. That was a great suggestion to help me improve my design skills.

Your input is always welcome as I always enjoy/learn from your comments when I read through these posts. Thanks!
User avatar
Shalmeneser
Veteran
Posts: 9474
Joined: Wed Dec 23, 2020 12:04 am
Location: Fr

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by Shalmeneser »

Color : was a point of view of somebody discovering your file
Bodies : welding ok
Sketch : It's always easy to comment when you have the final form. But when you are thinking about the future design, the good way is not something you are thinking about.
User avatar
adrianinsaval
Veteran
Posts: 5541
Joined: Thu Apr 05, 2018 5:15 pm

Re: Spreadsheet values do not update when the Master Spreadsheet is changed

Post by adrianinsaval »

Std_RandomColor comes in handy when you just want to differentiate separate components
Post Reply