Editing a drawing with External Data

Started by nightwatch0125, July 22, 2020, 07:03:35 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

nightwatch0125

I have a drawing with linked external data that I want to find a way to allow the user to edit in Visio without making a mess.  Currently the process is: Update the spreadsheet (external data source) and Refresh Data.  The Shape Data Graphics update with the new info.  If everyone edited the spreadsheet only then I think it would work fine. 

However, users have found they can make changes in the Shape Data Pane and avoid opening excel.  It updates the Shape Data Graphic on the drawing but the first time the external data gets refreshed it overwrites their "updates".

So my thought was to:
1.) Find a way to GUARD() the Shape Data when it's linked to force users to update the spreadsheet only.
2.) Find a way to push updates made in the drawing back to the spreadsheet so they say in sync. OR
3.) Find a way to flag Shape Data that doesn't match the value in the external data source.

A longer term goal is to have the shape "inherit" this value from a container when it gets added to the container.  I've added a "Prop.Category" to the shape (which is pulled from the external data source).  I've also added a "Prop.Category" to the container which then gets picked up by the shape's "User.Category".  From there I can look at the shape's "User.Category" and if it's #REF! I know it's not in a container and that I should use its "Prop.Category".  If there is a value then I can use the shape's "User.Category" (which it inherited from the container it was dropped in).  Then I just use an IF() to set the Shape Text to the appropriate cell (User.Category or Prop.Category).  I can even flag it if they're different and provide context in the Shape Data window but I can't figure out how to get Visio to push the change back to the spreadsheet (so the values will match the next time its refreshed).

Nikolay

#1
I have an extension for this  ;D

It allows exactly this - editing external data in Visio and then pushing it back to the source.
https://unmanagedvisio.com/products/visio-back-sync/

It replaces the default refresh engine with a custom one, that:
- looks at where the value is changed and can update the other side. Like, if user changes the value in Visio, it can also update it in the source.
- allows to preview all changes before applying them and allows you to decide how to deal with conflicts if any, or skip some actions.
- can push changes back to the source (at least for the common ones, like Excel sheet, Access database, SharePoint list)

Note that for pulling changes you can still use the default one, i.e. it's built on the top of Visio data binding you already have.

You can check it out - maybe it could work for you.
I have not updated it for a while, but it should be functional, as Visio is not updated for a while also.