WORKSHEET - controlling database criteria through worksheet cell typed in text

[drelARCH]

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 January 14 by drelARCH

[Pat Stanford]

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?

[drelARCH]

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. 😉

[Peter Vandewalle]

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.

[drelARCH]

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...

 

[Pat Stanford]

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)))

[Peter Vandewalle]

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... 🙂

[drelARCH]

What a joy!

Thanks @Pat Stanford and @Peter Vandewalle

I have been wishing to be able to do this for quite some time. It is definitely there! This gives nice flexibility working with databases...and it is so easy! 

[Pat Stanford]

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.

[drelARCH]

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 January 18 by drelARCH

[drelARCH]

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

[Pat Stanford]

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.

 

[Peter Vandewalle]

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.

[drelARCH]

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. 

[Pat Stanford]

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.

[Pat Stanford]

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.

[drelARCH]

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!

[Lien]

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. =)))