How to get Capitalised text in some lines in a calculation field a

[ChangeAgent]

I have a DB that pints envelopes. I have set up a calculation field that pulls the information together from other fields.

I do this with the following code:

Case( not IsEmpty( Title ) ; Title ) & " " &

Case( not IsEmpty( First Name ) ; First Name ) & " " &

Case( not IsEmpty( Last Name ) ; Last Name & "¶" ) &

Case( not IsEmpty( Company ) ; Company & "¶" ) &

Case( not IsEmpty( Street 1 ) ; Street 1 & "¶" ) &

Case( not IsEmpty( Postal Code 1 ) ; Postal Code 1 & " " &

Case( not IsEmpty( City 1 ) ; City 1 & "¶" ) &

Case( not IsEmpty( State Province 1 ) ; State Province 1 & "¶" ) &

Case( not IsEmpty( Country 1 ) ; Country 1 & "¶" ))

I would like the Country field to print in CAPITALS even though in the original field this is not written in capitals. Is this possible? And if so how?

[comment]

Use the Upper() function.

[ChangeAgent]

Use the Upper() function.

That works. Thanks.

Your suggested code improvement does not however.

TrimAll (

List ( 

Title & " " & First Name & " " & Last Name ;

Company ;

Street 1 ; 

Postal Code 1 & " " & City 1 ; 

State Province 1 ; 

Country 1

) ; 

0 ; 0 ) 

Get an error on First Name:

The specified field cannot be found.

Pity as your suggestion looks very clean compared to mine.

Thanks.

[comment]

Well, then change "First Name" to whatever your field is actually named.

[ChangeAgent]

Well, then change "First Name" to whatever your field is actually named.

Sorry that is the bug, the field is called "First Name"!

[comment]

Your computer doesn't seem to think so. Could be a copy/paste gremlin. Try deleting the pasted name and inserting your own.

BTW, I have removed my suggestion immediately after posting it, in order to avoid writing this caveat:

If the addressee has no Title, no First Name and no Last Name, then there will be an empty line at the top of the address - unlike your original formula. Same with the (unlikely) case where there is no Postal Code and no City.

This can be solved by applying TrimAll() individually to each line with multiple fields.

[ChangeAgent]

Your computer doesn't seem to think so. Could be a copy/paste gremlin. Try deleting the pasted name and inserting your own.

Gremlin indeed! Thanks.

BTW, I have removed my suggestion immediately after posting it, in order to avoid writing this caveat:

If the addressee has no Title, no First Name and no Last Name, then there will be an empty line at the top of the address - unlike your original formula. Same with the (unlikely) case where there is no Postal Code and no City.

This can be solved by applying TrimAll() individually to each line with multiple fields.

Yes, I have this problem with your script. Not sure where to ad the TrimAll, as I keep getting an error trying to insert it. Could you post an example?

Thanks.

[comment]

Try =

List ( 

TrimAll ( Title & " " & First Name & " " & Last Name ; 0 ; 0 ) ;

Company ;

Street 1 ; 

Trim ( Postal Code 1 & " " & City 1 ) ; 

State Province 1 ; 

Country 1

)

[ChangeAgent]

I did try that and no cigar. Solved it this way, not elegant maybe but works. Thanks for your help!

TrimAll (

List ( 

TrimAll ( Title & " " & First Name & " " & Last Name ; 0 ; 0 ) ;

TrimAll ( Company ; 0 ; 0 ) ;

TrimAll ( Street 1 ; 0 ; 0 ) ;

TrimAll ( Postal Code 1 & " " & City 1 ; 0 ; 0 ) ;

TrimAll ( State Province 1 ; 0 ; 0 ) ;

TrimAll ( Upper ( Country 1 ) ; 0 ; 0 ) ;

) ; 

0 ; 0 )

[comment]

I did try that and no cigar

Meaning...?

[ChangeAgent]

Britisch expression for it did not work. Sorry. The script I posted works however.

[comment]

I am familiar with the expression. Please provide an example which produces a problem.

[ChangeAgent]

OK the script as you posted does not clean up al lines in my address field.

The way I redid it does.

[comment]

Please provide a specific example, so that I can reproduce the problem.

[ChangeAgent]

Please provide a specific example, so that I can reproduce the problem.

Happy to, how would you like it? Can't send you the file as it has confidential information in it. So not sure as to how to. BTW you did get that I solved it, or?

[comment]

So not sure as to how to.

Can you fill out a record in the attached file in a way that "does not clean up al lines"?

BTW you did get that I solved it, or?

I thought the point was to simplify the calculation.

Address.zip

[ChangeAgent]

BTW you did get that I solved it, or?

I thought the point was to simplify the calculation.

[comment]

That may be what counts for you. However, I would like to know why the formula I suggested didn't work for you. I asked you four times already. If you don't want to help me with this, you don't have to.

[ChangeAgent]

That may be what counts for you. However, I would like to know why the formula I suggested didn't work for you. I asked you four times already. If you don't want to help me with this, you don't have to.

Happy to! Did not understand that this was your aim as well.

First my title and country fields are drop down fields.

I tried you sample but there it works.

[Lee Smith]

Are you using the Upper on the fields themselves, or are you using them in the calculation as you requested, or both places?

What happens if you amend comments' calculation to

List ( 

TrimAll ( Title & " " & FirstName & " " & LastName ; 0 ; 0 ) ;

Company ;

Street ; 

Trim ( PostalCode & " " & City ) ; 

Province ; 

TextStyleAdd (Upper (Country) ; Bold )

)

to obtain the Bold and the Upper;



or 

List ( 

TrimAll ( Title & " " & FirstName & " " & LastName ; 0 ; 0 ) ;

Company ;

Street ; 

Trim ( PostalCode & " " & City ) ; 

Province ; 

Upper (Country) 

)

to obtain just the Upper

Lee

[ChangeAgent]

Hi Lee,

only want UPPER, so your last one works.

[ChangeAgent]

What happens if you amend comments' calculation to

List ( 

TrimAll ( Title & " " & FirstName & " " & LastName ; 0 ; 0 ) ;

Company ;

Street ; 

Trim ( PostalCode & " " & City ) ; 

Province ; 

TextStyleAdd (Upper (Country) ; Bold )

)

to obtain the Bold and the Upper;



or 

List ( 

TrimAll ( Title & " " & FirstName & " " & LastName ; 0 ; 0 ) ;

Company ;

Street ; 

Trim ( PostalCode & " " & City ) ; 

Province ; 

Upper (Country) 

)

to obtain just the Upper



Lee

PS Lee, Some ";" missing, here is what I used:

List ( 

TrimAll ( Title & " " & First Name & " " & Last Name ; 0 ; 0 ) ;

Company ;

Street 1 ; 

Trim ( Postal Code 1 & " " & City 1 ) ; 

State Province 1 ;

Upper ( Country 1 ) ;

) 

[LaRetta]

Hi ChangeAGent,

"First my title and country fields are drop down fields. I tried you sample but there it works. "

In using the file provided by Michael (Comment), it worked as expected except for the field names and I also made the fields drop-down and that would have nothing to do with the calc not working.

If you paste his calculation into your calc box and when you leave the calc, it will tell you a field name is incorrect and highlight it. If you immediately double-click your correct field name, it will correctly replace the incorrect field. This is the benefit from typing a calculation as Comment has taught us all ... with spaces. Without those spaces, auto replacement by this technique would not work.

So after you double-click the first incorrect field name, attempt to leave the calc again. It will highlight the second incorrect field name and repeat (double-click your actual field name). This would correct his calc to match your fields. The only difference is you then wanted to wrap the country with UPPER().

Your suggestion that ending in semi-colon solved the issue cannot be correct. The only reason you didn't get an error was because the ending semi-colon was within a List() calculation). :smile2:

[Raybaudi]

The only reason you didn't get an error was because the ending semi-colon was within a List() calculation).

I'm curious... Was it known by you ?

[comment]

I tried you sample but there it works.

And what does that tell you?

[ChangeAgent]

Hi LaRetta]

Thank you, clear.

[comment]

This is the benefit from typing a calculation as Comment has taught us all ... with spaces.

I can't take the credit for that. I just follow the convention established by the way Filemaker assembles the formula when you double-click fields and functions. BTW, when I joined this forum, moderators used to admonish people for leaving out the spaces. Sadly, this practice is no longer followed.

In any case, I believe I've been cheated out of a cigar... :P

[ChangeAgent]

[LaRetta]

I'm curious... Was it known by you ?

Daniele. I'm unsure what you are getting at. I am going by the calculation presented by ChangeAgent that they said worked which was:

List (

TrimAll ( Title & " " & First Name & " " & Last Name ; 0 ; 0 ) ;

Company ;

Street 1 ;

Trim ( Postal Code 1 & " " & City 1 ) ;

State Province 1 ;

Upper ( Country 1 ) [color:red];

)

No wild guesses. It wasn't known to me ... it was known to everyone. It is the only difference between what was said to not work vs. work.

[Raybaudi]

It wasn't known to me ... it was known to everyone.

I was referring to the fact that a "lone" semicolon can be put into a List ( ) function w/o any error... that wasn't known at least by me.

[LaRetta]

Nor to me. But when I saw (and was surprised) that he didn't get a syntax error, I tried it. I then deduced it must be logical that there could be a semi-colon followed by null (as long as there was at least one field or text value within List().

I could be wrong - it wouldn't be the first time nor will it be the last but it was the only thing that fit (and the only thing that I could replicate) without syntax error. :wink2:

[Raybaudi]

I could not see any logic.

List ( Field ; ) gives no error

but

List ( "some" ; ) or List ( "some" )

List ( 1 ; ) or List ( 1 )

aren't accepted ( as expected )

However,

List ( 1 ; 2 ; )

is accepted.

[comment]

I am afraid you are mixing two unrelated issues. The List() function (like all Aggregate functions) has several syntaxes:

List ( Field ) lists the repetitions of Field;

List ( arg1 ; arg2 ) lists the specified arguments. An argument can be a field or a constant.

List ( "text" ) has no meaning and is not a valid syntax.

[Raybaudi]

So each aggregate function accepts a spurious semicolon, not only List ( ).

And even these are accepted:

Choose ( 0 ; 1 [color:red]; )

Case ( 1 ; 1 [color:red]; )

but not:

If ( 1 ; 1 [color:red]; )

[comment]

And even these are accepted:

Choose ( 0 ; 1 [color:red]; )

Case ( 1 ; 1 [color:red]; )

But not:

Case ( 1 ; 1 ; 2 ; )