Formula to scrap project data project address into a worksheet?

[hollister design Studio]

I thought it would be nice to have the project street address auto fill in my planting schedules...

Maybe one of you worksheet pros can help me out here?

 

 

Here's what doesn't work:

 

I took the terms from my title block project data.

I also tried     ='Title Block Project Data'.'Project Address'

[Tom W.]

Try:

 

=FORMATFIELD('Title Block Project Data', 'Project Address')

[hollister design Studio]

That did it!

 

[Pat Stanford]

To access a Record.Field you have to specify the object(s) to get the data from. A database row automatically defines that with one sub-row for each object (until you turn on SUMmation).

 

But to use a Record.Field in a Spreadsheet cell you have to use a function that specifies the object using a criteria. You got lucky that the TBB stores the project data not only into the Record attached to the object, but also stores it as the default value for the record. If you change the address later, I am not sure that FormatField will get the change.

 

Something like:

=ObjectData(t=wall, 'Universal Value', 'My Format-1', 'data')        

Returns the 'data' field for the attached 'My Format-1' for the wall. Returns N/A if the format or the field is not available.

Give one of your title blocks a name (I am going to use Master in this example)

 

=ObjectData(N='Master', 'Universal Value', 'Title Block Project Data','Project Address')

is probably safer as it will definitely return the value attached to the TBB named Master.

[Tom W.]

Thanks @Pat Stanford very useful info.

 

7 hours ago, Pat Stanford said:

You got lucky that the TBB stores the project data not only into the Record attached to the object, but also stores it as the default value for the record. If you change the address later, I am not sure that FormatField will get the change.

 

I can confirm that FormatField does respond to changes made in the TBB when used in a spreadsheet cell in my drawing issue register worksheet. But you're saying this is an anomaly + particular probably only to TBBs? Same goes for =FORMATFIELD('Title Block Project Data', 'Project ID') + =FORMATFIELD('Title Block Project Data', 'Project Name').

 

In the description of the function it suggests that in a spreadsheet cell FormatField relies on an object or objects being selected but in the case being discussed here the TBB doesn't need to be selected for the worksheet to update...

 

I never knew any of this, I just copied the formula from the VW drawing issue worksheet + never looked at it again!

[Pat Stanford]

My belief is that FormatField "Returns the default value of a given field name of the specified record format." The worksheet formula reference does not specify any difference for this function between Spreadsheet and Database cells.

 

The ObjectData version I posted specifically requires criteria to specify the object(s) when used in a Spreadsheet cell.

 

So yes, I think it is an anomaly or feature of TBB that when you change the project data it also changes the Record Defaults. This kind of makes sense for Project Data because if you create a new TBB it should have the same Project Data as every other TBB.

 

I bet that if you use FormatField on a Sheet Data field you will get only the default and not the same data as you would get using ObjectData.

[hollister design Studio]

@Pat Stanford is there a way to make notation in a spreadsheet that does NOT show on the page, only when editing?

 

I would love to add a line that says "confirm that - ObjectData(N='title block name' - matches current title block name".

 

....that way when I use this plant schedule again in the future with a different title block I will know why it's not grabbing the data.

[Pat Stanford]

I have not figured out a way to tell when a worksheet is not open for editing. Still thinking about this.

[LenLindhout]

Hi all,

For me both options don't work..
Can't seem to figure out what's going wrong..
 

When I Type: =FormatField('Title Block Project Data'.'Project Address') , the data request formula turns red. When I replace the period for a comma with a space, then the formula turns green.
But I won't get output in the field...

Same goes for Pat's method. First I thought is was the Title Block Style I made an error with. But also with the Title Block itself it will not display any data.

 

Please help!

[Tom W.]

1 hour ago, LenLindhout said:

But also with the Title Block itself it will not display any data.

 

Not sure what you mean here. Do you have a TBB that includes linked text in the layout with the parameter 'Project Data.Project Address'?

[Pat Stanford]

Where are you located and what is your standard "decimal point" character and thousands separator character in a number?

 

In the US the decimal point character is a period ( . ) the separator in a worksheet formula is a comma ( , )Thousands Separator is a comma ( , )

 

In Europe, the decimal point character is a comma ( , ) the separator in a worksheet formula is a semicolon ( ; ) Thousand Separate is a period ( . )

 

Many of the formulas posted here are from the US and use the US separators.  If you are using different separators you will need to figure our what those are and make the proper substitutions.

 

I think you need to use the Semicolon as the separator between the two pieces of data being passed to FormatField

 

 

 

 

=FormatField('Title Block Project Data';'Project Address')

 

 

 

In the US (not sure about elsewhere) a period is used to separate the Record.Field when you want the field value in a cell in a worksheet, but most of the functions require the Record and Field to be passed as separate pieces of data and be separated by a worksheet separator (comma in the US).

 

HTH

[LenLindhout]

On 1/7/2024 at 3:25 PM, Tom W. said:

 

Not sure what you mean here. Do you have a TBB that includes linked text in the layout with the parameter 'Project Data.Project Address'?

Think this was a type-o. 
I do use a TBB with linked textfields.
We use multiple fields in the TBB from the default record style.

 

On 1/7/2024 at 8:36 PM, Pat Stanford said:

Where are you located and what is your standard "decimal point" character and thousands separator character in a number?

 

In the US the decimal point character is a period ( . ) the separator in a worksheet formula is a comma ( , )Thousands Separator is a comma ( , )

 

In Europe, the decimal point character is a comma ( , ) the separator in a worksheet formula is a semicolon ( ; ) Thousand Separate is a period ( . )

 

Many of the formulas posted here are from the US and use the US separators.  If you are using different separators you will need to figure our what those are and make the proper substitutions.

 

I think you need to use the Semicolon as the separator between the two pieces of data being passed to FormatField

 

 

 

 

=FormatField('Title Block Project Data';'Project Address')

 

 

 

In the US (not sure about elsewhere) a period is used to separate the Record.Field when you want the field value in a cell in a worksheet, but most of the functions require the Record and Field to be passed as separate pieces of data and be separated by a worksheet separator (comma in the US).

 

HTH

 

I'm located in Europe. I thought I'd tried the different characters for separation. But I'll try again.
The latter thing you write makes some sense, I haven't tried that so hopefully that will do the trick.

 

Thanks!!