Keynote data base Worksheets.

[Boh]

I'm looking for a clever way that a worksheet can report on certain objects in a particular sheet layer even after the sheet layer number changes. (That is without having to manually update the database criteria).

 

I have a bunch of keynotes on a sheet. Instead of using a keynote legend to list the keynotes I would like to use a worksheet as the keynote legend. I can easily create a database worksheet that lists all the keynotes on a particular sheet, however to do this, as far as I'm aware, I need to use the criteria "L=[enter sheet layer number]". So when the sheet number is changed (as it invariably does) the worksheet criteria also needs to be updated.

 

That's not a big deal if there are only a few sheets, but I am looking at doing this for dozens of sheets and I don't want to have to edit the criteria of dozens of worksheets every time a sheet numbering system is changed.

 

The reason for wanting to use worksheets in this way, (as some background), is that I have been getting increasingly frustrated with keynote legends. When you have a lot of them in a file (as I often do) they get slow to update and also randomly add additional unused note lines. I repeatedly have to "remove gaps" from the legends each time a note was edited or moved to a different legend. I had a day of spinning balls and crashes last week so I decided to try a different way.

 

So I'm test driving a system that uses database worksheets as legends for keynotes instead of keynote legends. I would have to have at least one actual keynote legend somewhere but on the actual drawings sheets the keynote text would be represented via worksheets. So far it is working really well. Each sheet needs it's own unique legend worksheet but that is easy to do.

 

It seems the deal breaker for this system is have the worksheet criteria update automatically to changes in the sheet number.

 

Any clever people out there have an answer to this??

 

If anyone else use a similar system any advice appreciated.

 

Cheers.

Edited September 20, 2021 by Boh

[Pat Stanford]

I think you are out of luck. Criteria are basically hard coded. Change the layer name and they will not follow.

[Peter Vandewalle]

Pat is right.

But I think a script could make it semi-automatic.

If the worksheets have a name beginning with something recognizable, you could select them, get the instance layer and update the database formula.

Also, have the layer name in a cell and have the database formula use that cell as a reference. Like “(L=A1)”. That’ll make it easier to update your worksheet.

[Boh]

NIce @Peter Vandewalle. Thanks!

 

I'm not sure how to call up the worksheet instance layer but I was thinking along the same lines of your other suggestion and got a system that works. The problem is it is very slow... so not practical. I did the following as a trial:

 

I figured if I don't want to have to manually edit the criteria of the "keynote legend worksheet" if the sheet layer name changes then I can't simply use "L=[enter layer name]" as criteria in the database formula. So I needed to find another way for the worksheet to know which layer to look on.

 

What I did was create a "Sheet Identifier" symbol and placed an instance of it on a test sheet layer. 

 

In the "keynote legend worksheet" I put a formula in an empty spreadsheet cell that called up the sheet number of the "Sheet Identifier" symbol.

 

I then edited the database formula to use this cell's contents as the layer location instead of using the location of the actual keynotes.

 

i.e. Database formula:

=DATABASE((INVIEWPORT & INSYMBOL & (R IN ['Callout']) & ('Callout'.'Place As Keynote'=TRUE) & (L=(B1))))

 

Where the cell B1 has the formula "=LAYER(S='sheet identifier')"

 

So this works - it will report any keynotes which are on the same sheet as the "Sheet Identifier" symbol. It doesn't matter if the sheet number changes as the spreadsheet cell will update if the layer name changes and thereby updating the database formula.

 

The problem is it takes takes one or two minutes to recalculate. Is there some way to make this sort of thing go quickly? Is there a better technique that might make things run a bit quicker?

 

I also realise that I would need to tweak this a bit if I'm going to use it across multiple sheets. If I have a "Sheet Identifier" symbol on each sheet I couldn't use the symbol name as criteria for the B1 cell.

 

I have a feeling the database formula could be smarter and edited with some conditional part that would be like:

"If a keynote is on the same layer as an object with [enter unique record field value] then list keynote"

 

Then I would just need an identifier object (such as my symbol) on each sheet with a unique record field value. I wouldn't need the B1 cell formula to call up the layer number.

 

In the screen shot below I recalculated the trial worksheet after adding a "Test" Keynote" and it does work (just slowly)

 

 

 

[Peter Vandewalle]

You can make database formulas smarter by using a "Databasebyscript" formula. Of course you'll need to script the list of shown objects yourself.

[Boh]

Well scripting is beyond me so I'm going to try what you suggested earlier and simply having a spreadsheet cell that I type in the sheet layer number and having the database formula just calling up that cell's contents as the layer location. The worksheets recalculate quickly, but would need manually editing if the sheet layer number changes.

 

At least if I have the spreadsheet cell with the sheet number visible in the spreadsheet it will clearly display which sheet the legend is for.

 

Thanks for your help.