Jump to content

General Note Column in Worksheet


Recommended Posts

Going batty with the basics again...

How can I create a column in a worksheet where I can type anything of a general nature in any cell in the column.

A column without database relevance within a database worksheet.

So, for example,  I can have all my window and door data reported on the sheet but then have a column where anything can be typed in any given cell.

Is there some special command I can place in the database row where that cell resides?

Offering desperate thanks,

bc

Edited by bc
Link to comment

Any column in a worksheet where you want to the able to change data must be a Record.Field combination. 

 

It can be a PIO Parameter Record  ='Door'.'User Field 9'

 

or a user defined field

 

"myRecord"."MyField'jt hou

 

(apologies for any typos. I have been drinking for a long time.)

 

But you can not enter data into a field that is not a Record.Field combination.

Link to comment
10 hours ago, bc said:

A column without database relevance within a database worksheet.

Any column within a database sheet is by definition a record field. Create a record field for this kind of text and set it to text and you should be able to type any text into it.

As long as you don't link this field to any text object in a symbol it should not show up in your drawing, only in the worksheet and the data tab of the object info palette.

Link to comment

Still having trouble. 

 

9 hours ago, Art V said:

Any column within a database sheet is by definition a record field. Create a record field for this kind of text and set it to text and you should be able to type any text into it. 

As long as you don't link this field to any text object in a symbol it should not show up in your drawing, only in the worksheet and the data tab of the object info palette.

 

Any elaboration would be greatly appreciated

Is there some manual someone can point me to where I can learn this stuff that is a step or two beyond what is presented in Help/Worksheets?

Link to comment
On 5/18/2018 at 8:35 AM, bc said:

Still having trouble. 

 

 

Any elaboration would be greatly appreciated

Is there some manual someone can point me to where I can learn this stuff that is a step or two beyond what is presented in Help/Worksheets?

@Art V statement that all columns must be Record.Field is not quite true. It is completely possible to have columns with built in Worksheet Functions and columns with other formulas, but Record.Field of PIOs is the most common use of databases.

 

Try the following. I recommend you try it in a test file or on a copy of your file in case you don't like the results.

 

1. Go to the Resource Browser and create a New Record Format named "MyRecord".  In that record format create a field of type TEXT names "MyNote"

2. Attach the record to several objects in the drawing.

3. Create a new worksheet. Make a Database Row and set the Criteria to "MyRecord" is Present

4. In the database header row (i.e. 3, not 3.1, 3.2, etc) put in a formula of "='MyRecord'.'MyNote'

5. Type data into one of the database rows (3.2, 3,5 etc) in the MyNote column.

6. Right Click in the database Row Header and choose Select Item.

7. Goto the OIP and select the Data pane. You should see that MyRecord is attached and that the data you typed into the worksheet is now stored in the MyNote field.

 

Once you figure out how the above works, you can change the record and field names as you wish. If you already have a record attached, you can just edit the record format to add the "MyNote" field.

 

If you have a schedule of PIOs that is already based on the PIO record (i.e. Door or Window or ...) you can not edit that format. You will have to create a custom format and attach it to each instance of the PIO in the drawing.  Or better, use one of the Custom Fields (at least in Doors and Windows) in place of MyNote so you don't have to manually attach the record.

 

If you have an object in the schedule that does not have MyRecord attached, you will not be able to store any data you type into the worksheet. You MUST manually attach the record format to the objects to be able to store notes.  

 

If it is something that you have to manually attach a record to, consider making a "Blue" or "Red" symbol out of it so that when you place the symbol it reverts to the object with the record already attached.

 

Link to comment

Thanks alot Pat. 

I have been in touch with MichaelK and he has generated some scripts to serve my needs (what-a-guy).  I will publish our exchange with his kind permission later on in this thread.

Right now, Pat, your info is greatly appreciated and is working except for one bit of criteria I would like and can't seem to locate.

I can't seem to get my Worksheet to report my door elevation.

The coders never thought this would present an issue but both the door height (how tall the door is) and the door elevation (it's z location) are called "height".

Do you or anyone know how I can call up the door elevation in  my schedule.

 Currently I have:

 

=IF('Door'.'IDLabel'<>'', 'Door'.'Height', IF('Window'.'IDLabel'<>'', 'Window'.'Height', 'ERROR'))

 

This calls up both the window elevation (set to sill in this case) and the door height.  NOT door elevation. I want the door elevation.

 

Thanks

Bradley

Link to comment

This is because the height of the door is data in the record 'Door' in the field 'Height'

 

The distance from the bottom of the door to the layer plane (otherwise known as the floor 🙂) is

 

=ZCOORDINATE

 

It's a property of the door symbol itself.

 

Try this:

 

=IF('Door'.'IDLabel'<>'', ZCOORDINATE, IF('Window'.'IDLabel'<>'', 'Window'.'Height', 'ERROR'))

 

The really questions is:  What kind of buildings are you designing with doors floating in walls?

Link to comment

It's a long story but the short version is that it is a rehab of and existing old "heritage" house that has numerous doors used as fixed sidelights that are above floor level and some doors that are above floor level that go out to a tiny roof deck and one door on a level all it's own 7 3/4" below the main that heads out to the main floor door hdr ht.  At any rate, both doors and windows are on the same schedule in the same column and this further helps delineate their "doorness".

I know what you must be thinking but this is not my call. Se la vi. Que sera, sera. Whatever, dude. A code in the hand is worth two unscripted.

Thanks for all...hopefully I won't need to pester any one for a while.  Will try and post our exchange.

 

Also, the first data box in a door OIP entitled "height" should be renamed to "elevation" or "zcoordinate".

 

 

 

Edited by bc
Link to comment

Remember the Z-Coordinate is from the Layer Plane, not from the wall, so that might not work for you either. Best option might be to create a separate column with a custom Record.Field and Copy/Paste from the column with the formula and then hide that column. Manually adjust the few that are not correct. Or put in a column to manually correct the few and if there is data in the "corrections" column use that data otherwise use the regular calculation.

 

Link to comment

Here's the exchange over the Worksheet issue for those willing to wade thru it.

Or just skip to the sample file for importing the scripts and record formats and the great video Michael made at the airport:

 

HI MICHAEL,

I WAS WONDERING IF I COULD HAPPEN TO GET YOUR THOUGHTS ON MY QUESTION HERE:

I'M DESPERATE SO I AM ASKING DIRECTLY.  THANK YOU VERY MUCH. I'VE TRIED SOME FUNCTIONS BUT CAN'T GET ANYTHING TO WORK. 

------------------------

Hi Bradley

 

I've sometimes wanted to do that, too!

 

The only way to do it universally - any object no matter what criteria the database is using - is to create a record format with one field and attach it to everything in the drawing.

 

It's probably pretty easy to create a script that attaches the record format to either every object in the drawing or all selected objects or all objects on the active layer, etc.  Then you could just add the call ='my universal record'.'super note' to any worksheet database for any object.

 

The question is do you want add it to objects in DLVPs, groups, symbols, parametric objects, etc.

 

Let me know what you think.  This is actually within my limited scripting abilities (famous last words!).

 

Are you coming to Phoenix in November?

 

MK 

-------------------------

Michael

WOW, that would be fantastic.  Thanks so much for the offer.

I think I understand the process you're proposing but just to be sure, and in my lay terms:

 

1     The script would attach a record to, let's say, all doors,windows and skylights (what I would need at this juncture) or whatever objects one chooses. Right now it only needs to be acting on objects within Design Layers of choice.

 

2     One could then create a column in a database worksheet and then, in the cell in the database header row, one would install a certain function that would call up these attached records. The attached record is really an empty text object?

3     Then one could type anything into any cell in that column. Is that it?

 

OR (and this might kill it's usefulness for me)

4     One would have to "enter into" the data tab of the OIP and  type the general info into the record format of ea object after selecting it. Not good.

 

If it is not the condition 4, then this would be great.

Is this a relatively quick and easy process for you?

Although I live relatively close in California, I don't expect to be able to attend the conference in Phoenix, sorry to say.

Did I make my issue clear enough?

Bradley

---------------------------------

I think I see what you're trying to do.  

 

Let me take a stab at it.  I'm traveling for a week. Plane trips are always good times for scripting.

 

mk

-------------------------------

But if you've never been to a Design Summit, they are usually pretty fun.

 

Pat and I are doing 2 two-hour sessions on worksheets.  Then we are going to try to add another day long conference on scripting for those who are really gluttons for punishment.

 

----------------------------------

 

Bradley

 

This might take a while to upload from a plane, but let's give it a try.

 

Needless to say:  Make a duplicate of a file and try it there first.  It's a very simple script that should have no unforeseen side effects, but use caution!

 

Let me know if you have any questions.

 

mk

 

 

By the way, I should point out that doors and windows (and most parametric objects) have between 6 and 10 user fields built in.  Usually called something like ='Window'.'UserFld1' through ='Window'.'UserFld10 that you can use for exactly this purpose.

---------------------------------------

Yes this is where I started to go initially but wanted both door and window object notes to be able to be in the same column and had trouble combining  ='Window'.'UserFld1' with  ='Door'.'UserFld1'.

Holy guacamole you have just matched Pat in Community Board Heroism.

That video was great.  I'll see what I can do with it and get back with any possible questions

----------------------------------------

The user field trick can be done, but you need to use an IF/THEN/ELSE statement in the database header.  And then it only displays.  You can't type directly into it because it's the result of a formula.  So you need different columns or different worksheets to enter data.

 

I like your idea better. 

 

Hope it works for you.  Let me know if you have any questions or would like any tweeks to the scripts.

 

-----------------------------------------

 

WOW! When I installed everything and got it to work it was (almost) like when I first walked into the foyer of the Gamble House.

A quasi-religious experience. 

Near epiphanally moving.

Not as good as sweet corn on the cob but.....you can guess my meaning.

It's to say thank you to the max.

 

---------------------------------------------

 

This is exactly the reason Pat and I want to do our scripting conference the day after the Design Summit.  Those scripts are really only about 4 to 6 lines.  And I just grabbed modified one that I was using to do another cool job that literally saves me hours per week. 

 

I'm pretty bad at scripting, but I've managed to boost my productivity a LOT with a few poorly written scripts.

 

 

 

 

----------------------------------------------

 

 

Universal_Test.vwx

Link to comment
19 minutes ago, Pat Stanford said:

Remember the Z-Coordinate is from the Layer Plane, not from the wall, so that might not work for you either. Best option might be to create a separate column with a custom Record.Field and Copy/Paste from the column with the formula and then hide that column. Manually adjust the few that are not correct. Or put in a column to manually correct the few and if there is data in the "corrections" column use that data otherwise use the regular calculation.

 

I did understand that the z coord. is relative to the layer plane and Michael's set-up will work for me as is.

As intriguing as your reply is, it is a bit beyond my ken. Don't even know how to "hide a column", ha!

Link to comment

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.

×
×
  • Create New...