Automate colour of worksheet to match object/plant fill.

[tomtomtom]

Hello, I am making a planting plan, and want the background colour of each cell to match the fill colour of its corresponding plant. I want to automate this, but am not sure where to start. Thanks in advance!

 

i.e. I want to combine the ID and colour columns, but have the whole cell background the correct colour... - see image.

[Pat Stanford]

I don't think you can accomplish what you are asking in VW right now.  There is not a way to format one row of a database separately from all of the others.

 

The only possible work around I can think of would be to edit your symbols to include a Rectangle and Linked Text to Record of the ID is a separate class in each symbol and then use an Image of the symbol in the field. If the Rectangle in every symbol was the same size you should be able to set the image size to basically fill the entire cell.

 

If you turned on only that one class and Recalculate the worksheet they the image would only have the rectangle/text.

 

The problem then is that if you happen to recalculate with other classes turned on you will end up with a completely different image than what you want to see.

 

Sorry.

[tomtomtom]

Thanks Pat, it's a real shame as automating cell colour is not possible at this point in time as it would save a lot of time and massively improve the readability of planting schedules. Is there an option with scripting, or is that more effort than it's worth? This sort of thing is currently being done manually at my work - which isn't not ideal, especially when the plans have 80+ species.

[unearthed]

8 hours ago, tomtomtom said:

Thanks Pat, it's a real shame as automating cell colour is not possible at this point in time as it would save a lot of time and massively improve the readability of planting schedules. Is there an option with scripting, or is that more effort than it's worth? This sort of thing is currently being done manually at my work - which isn't not ideal, especially when the plans have 80+ species.

@tomtomtom  LA's have been hoping for something like this for a decade or more, lot of basic functionally lacking in vw, but unfortunately for LA's it's still the best of the worst. Just not enough LA's in the world to justify writing good software for us.

[Jeff Prince]

@tomtomtom  While I would prefer for this information to just report automatically to the schedule without having to employ tricky workarounds, it is possible to achieve what you desire.

 

Here is an example of how to achieve what you are trying to do, but with an emphasis on bloom color.  Same principle, easier in your case though.

 

Just pay attention to the first plant, I didn't set all of them up in this legend yet.

 

Sounds like a lot of work, but really only took about 20 minutes to figure out and prototype.

GRAPHICS

This step not required if you just want to show the background fill of your plant.  Add a bloom graphics to the plant symbol itself (I used a 3"x3" square).  Put it on a class for bloom color so you can turn it off in other places if required.

**Place this graphic at the center of the bounding box for the plant symbol show it self centers in the plant list cell.

LEGEND/WORKSHEET

Create a plant list on a unique design layer, create a column that is a duplicate of the plant's "image".  This is to be used for bloom color.

select the cell for the duplicate image and then.... format cells -> images -> Scale = 1:1 (this zooms in on your bloom graphic)

set the attribute fill of the worksheet to 'none' so you can see other items that will be placed behind it soon.

VIEWPORTS

create a viewport to display the plant legend

duplicating that viewport to display the bloom color column only

cropping the viewport for this bloom color column so it only show that column, leave plenty of room at the bottom in case the legend expands.

placing the duplicated viewport to the rear of the viewport for the uncropped plant legend viewport.

Happy Dance

Best performed in a highly public area of your office or during a zoom meeting.

 

So, all this being shared, it is a bit ridiculous to jump thru these hoops for some thing so simple.  It's possible if you really need it though.

 

By using this method, your color fill will stay coordinated with changed to the plant worksheet since it is all just one worksheet with stacked viewports doing the tricky bit.  It is possible to do the same using multiple worksheets, but that requires recalculating two worksheets each time you make a change.  This viewport trick applied to worksheets has all kinds of other creative uses....

[Pat Stanford]

You could script something like this (maybe), and you could probably build formulas that would recalculate, but I don't think there is any way to do it and have new plants automatically added to the list. Instead of a database, it would have to be a separate spreadsheet row for each plant type. And if you added or deleted a plant type, you would have to manually run the script again to get the correct plants.

 

My guesstimate is that this script would require at least 20 to 40 hours to make completely stable and usable. 

 

https://imgs.xkcd.com/comics/is_it_worth_the_time.png

[tomtomtom]

Thanks guys, will come back to this on a quiet day! It is a lot of hoops to jump through!

[Yonina]

On 7/6/2021 at 3:27 PM, tomtomtom said:

Thanks guys, will come back to this on a quiet day! It is a lot of hoops to jump through!

Hello @tomtomtom,

 

I am trying to figure out how to make planting plan like yours now.

how can i have circle symbol together? what tool did you use and how did the display been set up 🙂

 

Many thanks

 

 

Yoni

[alluvium]

On 12/7/2021 at 6:31 PM, Yonina said:

Hello @tomtomtom,

 

I am trying to figure out how to make a planting plan like yours with the merged symbols - Can you provide instructions?