drelARCH Posted January 14, 2021 Share Posted January 14, 2021 (edited) 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, 2021 by drelARCH Quote Link to comment
Pat Stanford Posted January 14, 2021 Share Posted January 14, 2021 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? Quote Link to comment
drelARCH Posted January 14, 2021 Author Share Posted January 14, 2021 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. 😉 Quote Link to comment
Peter Vandewalle Posted January 14, 2021 Share Posted January 14, 2021 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. Quote Link to comment
drelARCH Posted January 14, 2021 Author Share Posted January 14, 2021 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... Quote Link to comment
Pat Stanford Posted January 15, 2021 Share Posted January 15, 2021 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))) 2 Quote Link to comment
Peter Vandewalle Posted January 15, 2021 Share Posted January 15, 2021 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... 🙂 1 Quote Link to comment
drelARCH Posted January 15, 2021 Author Share Posted January 15, 2021 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! Quote Link to comment
Pat Stanford Posted January 15, 2021 Share Posted January 15, 2021 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. Quote Link to comment
drelARCH Posted January 16, 2021 Author Share Posted January 16, 2021 (edited) 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, 2021 by drelARCH Quote Link to comment
drelARCH Posted January 19, 2021 Author Share Posted January 19, 2021 Anyone with idea how to correctly use FormatField function in example in previous post...? Quote Link to comment
Pat Stanford Posted January 20, 2021 Share Posted January 20, 2021 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. Quote Link to comment
Peter Vandewalle Posted January 20, 2021 Share Posted January 20, 2021 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. Quote Link to comment
drelARCH Posted January 20, 2021 Author Share Posted January 20, 2021 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. Quote Link to comment
Pat Stanford Posted January 20, 2021 Share Posted January 20, 2021 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. Quote Link to comment
Pat Stanford Posted January 21, 2021 Share Posted January 21, 2021 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. Quote Link to comment
drelARCH Posted January 21, 2021 Author Share Posted January 21, 2021 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! 1 Quote Link to comment
Lien Posted January 21, 2021 Share Posted January 21, 2021 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. =))) Quote Link to comment
Recommended Posts
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.