Jump to content
drelARCH

WORKSHEET - controlling database criteria through worksheet cell typed in text

Recommended Posts

Hello vectorworks community,

 

I wonder how to achieve something like this:

 

Have one symbol that its text parts are linked to one record format fields. It act as a container for storing of informations about any specific project that somebody in office was working on. Its about logging working time on daily bases...filling calendar. Informations consist of: Project ID, tasks worked on, time spent on working task, drawn by etc. 

 

Now I want to collect data through worksheet database particularly overall time spent on specific project for specific project phase.

 

I wish I could type into one designated cell within worksheet field value (Project ID) and that way control criteria of database results. So the results show only items (symbols) where same Project ID is checked. Then whenever I need to type in different Project ID and get different results.

Everything happening in one worksheet.

 

I see that in some Preformated Reports materials quantity - take off you can type name of material directly into database header and get quantity of that material...yeah this is something closer...but i wish to have one cell apart of database where i could type in Project ID and control one database or even few databases (differ by project phase for specific project) results same time.

 

Any idea how to achieve something like this. Is it at all possible?

Thanks a lot for any input.

Edited by drelARCH

Share this post


Link to post

This is probably possible. Can you post a sample file with a few of the symbols and your current worksheet so I don't have to mock something up?

Share this post


Link to post

Hi Pat,

Thanks for quick response.

I have sent you direct message with the file.

 

Hopefully everything is clear even though it is mainly in slovak language. 😉

Share this post


Link to post
51 minutes ago, Peter Vandewalle said:

I do this pretty often. You should just edit the database formula and replace the field content string in that formula with the cell reference.

 

Thanks @Peter Vandewalle,

Can you be more specific how to exactly do it.

 

Existing formula:

=DATABASE(((S='Projekt Box') & ('Projekty'.'Stupeň'='AŠ') & (LOC='oblast kalkulacie')))

/Stupeň = Phase/

 

New formula

=DATABASE(((S='Projekt Box') & ('Projekty'.'Stupeň'='AŠ') & ('Projekty'.'Projekt'='C2') & (LOC='oblast kalkulacie')))

/field with specific project name added/

 

I wish to create separate databases for same project (cell: C2). Each database for different project phase (Stupeň).

I suppose my cell reference is not written correctly in database formula...

 

Share this post


Link to post

You were so close. You don't need (or want) the quotes around the cell reference to C2.

 

=DATABASE(((S='Projekt Box') & ('Projekty'.'Stupeň'='AŠ') & (LOC='oblast kalkulacie') & ('Projekty'.'Projekt'=C2)))

  • Like 2

Share this post


Link to post
2 hours ago, Pat Stanford said:

 

You were so close. You don't need (or want) the quotes around the cell reference to C2.

 

You took the words right out of my mouth... 🙂

  • Like 1

Share this post


Link to post

The only downside to this is that you have to manually edit the =Database formula and can no longer use the criteria builder. But for anyone who is deep enough into worksheets to want to do something like this, that is a minor inconvenience.

Share this post


Link to post

Aha...Is that so? Criteria builder would be definitely something more cleaner, professional.

 

I have one more question.

As I know by now how to control database trough regular cell It would be nice to take it one step further and create in that cell kind of drop down menu or pop-up menu of items from format field 'Projekt'. Then I dont have to type in but rather select it from menu.

I am trying to do it spreadsheet way using FORMATFIELD function with formula like this:

=FORMATFIELD(((T=SYMBOL) & (N='drop-down-menu')), 'Projekty', 'Projekt')

 

Projekty = Name of record format

Projekt = format field

 

...but it doesnt work.

Any idea what I am doing wrong?

Thanks

Edited by drelARCH

Share this post


Link to post

Anyone with idea how to correctly use FormatField function in example in previous post...?

Share this post


Link to post

I have not used that before and won't have time to play until probably this weekend, but I don't think it does what you are hoping it does.

 

My reading of the description is that the criteria portion only applied to data associated with the Record specified.

 

I can't see where this is a useful function as you can already just use the Record.Field combination to get effectively the same thing.

 

Share this post


Link to post

I think your formula should be ...S=‘drop-down-menu’...  N is for an object name, S for a symbol definition name.

And you’ll need to have an instance of the symbol in the drawing.

Then, maybe, it’ll work.

Share this post


Link to post

Thanks guys,

 

No luck here...

I indeed placed instance of symbol (symbol name =Projekt Box) on individual layer (Support-DO NOT EDIT-) and formula goes like this:

=FormatField(((S='Projekt Box') & (L='Support-DO NOT EDIT-')), 'Projekty', 'Projekt')

but unfortunately doesn't bring 'Projekt' field pop-up menu.

 

@Pat Stanfordcan I use Record.Field as a spreadsheet function? I haven't tried it yet. 

Share this post


Link to post

The documentation for FormatField is wrong and the name is misleading.

 

A better name would be FieldDefault. What it does is returned the default value for the field specified in the record specified.

 

It is not a criteria function, so you can't pass it a criteria.

Share this post


Link to post

I don't think you can report record.field combinations in a spreadsheet cell. Hopefully someone will let me know I am wrong [ @michaelk ].

 

It can probably be done with a worksheet script, but transferring criteria can be problematic. I will try and take a look at that this weekend also.

Share this post


Link to post
7 hours ago, Pat Stanford said:

The documentation for FormatField is wrong and the name is misleading.

Before asking questions I was looking at help manual too and got same feeling about FormatField.

 

To be honest if this is not straightforward as controlling database through regular cell I would probably give up. 

Definitely It would be really nice to have it but am bit sceptic about script and all the maintenance it might require in future. But maybe I am wrong...never used script within worksheet. I am certainly opened to learn new stuff on my vectorworks journey.

Thanks for all your effort!

  • Like 1

Share this post


Link to post
4 minutes ago, drelARCH said:

Before asking questions I was looking at help manual too and got same feeling about FormatField.

 

To be honest if this is not straightforward as controlling database through regular cell I would probably give up. 

Definitely It would be really nice to have it but am bit sceptic about script and all the maintenance it might require in future. But maybe I am wrong...never used script within worksheet. I am certainly opened to learn new stuff on my vectorworks journey.

Thanks for all your effort!

Yep, I'm realy consufused when I try to keep the correct coordinates. In My country - Viet Nam, I can't find anyone who knows about this software. =))) 

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×
×
  • Create New...