Calculation Help.....

[macmangler]

Hi... I am certain there is the easiest solution here, I can't figure it out.

I am dealing with mailing addresses. If I have a calculation where I am composing full mailing addresses....

Someones Name

Address 1

Address 2

City, State Zip

When address 2 is blank, how do I rid of that space?

Many Thanks!

[CoZiMan]

If you place multiple fields holding the different values on the layout you can use the 'sliding/printing' option under the 'format' menu.

However,

I usually use a calc field to hold a label.

That's right, one text field holds everything.

You calculate the field by combining the others (with appropriate spaces) and use the trim function to get rid of extra spaces and the substitute function to get rid of extra paragraph returns (Blank Lines).

Use the paragraph symbol in quotes, preceded and followed by the ampersand symbol to break to the next line in the calculation.

I like this better than sliding printing because the same layout with only one variable name change and one set of style changes can be used for different styles of labels.

[Søren Dyhr]

I guess I would make a string of mergefields that either because you're on fm6 is genuine calcfields or as autoenteroption updating in each field with these calcs:

Name & Left("¶";Count ( name ))

Address1 & Left("¶";Count ( Address1 ))

etc.... This means that only if a value exist in the field is a CRLF made at the end of the field and the content of the mergefield is wrapped into a new line.

Now usually would this postfixing be something to avoid in regular fields - but with fm7/8 could the autoentering function be a CF that also takes the current layout into consideration as well.

--sd

[macmangler]

Hi... I appreciate your fast support. However, I am still confused and am all googled out. Maybe this would help you help me...

Specifically... I have a database with the following fields:

Company

First Name Last Name

Address 1

Address 2

City, State Zip

Each of the above has it's own field. (Zip is it's own field)

I am making a calculation on the above fields.

This is my formula....

COMPANY & ¶ & FIRST NAME & " " & LAST NAME & ¶ & ADDRESS 1 & ¶ & ADDRESS 2 & ¶ & CITY & ", " & STATE & " " & ZIP

This works well, however, often there is no info in the address 2 field. So this formulas appears as such:

Company

First Name Last Name

Address 1

City, State Zip

my code needs one detail to remove that unwanted ¶ after a blank field.

I do appreciate your input and advice. I migrated to FM 8 as well, if that helps... same problem in either version.

Many many many many thanks.... !!

Mike

[IdealData]

Try this..

COMPANY & ¶ &

FIRST NAME & " " &

LAST NAME & ¶ &

ADDRESS 1 & ¶ &

Case(ADDRESS 2 <> "", ADDRESS 2 & ¶) &

CITY & ", " &

STATE & " " &

ZIP

[Søren Dyhr]

But it doesn't deal with this address:

Lizzie, Bucks Palace

I would instead, after learning that mergefields doesn't render the value of a pilcrow!

...use this:

Substitute (

Company & Left("¶";Count(Company)) &

First_Name & " " & Last_Name & "¶" &

Address_1 & "¶" &

Address_2 & "¶" &

City & Left(", ";Count(City)) & State & " "& Zip

; ["¶¶" ; "¶"];["¶¶" ; "¶" ])

It deals with missing company name, as well as having the address in Address_2 only.

--sd

[macmangler]

You Rock!!! It works!!!!

Now the next challenge for my File Maker future is what does this mean and why did it work? Case(ADDRESS 2 <> "", ADDRESS 2 & ¶)

I have learned Filemaker very fast. Most of it is so easy, but where do you learn this stuff? I would love to know all the codes and how to use them, etc. What do you recommend? Is there a book I can buy?

Many Thanks! You solved many hours of fustration!!!!

[Søren Dyhr]

I would gues that the majority here can't set their finger on one particual spot, from where they've learned the ropes ... the unfortunate truth is that some are good at it and others not.

I would love to know all the codes

This doesn't make sence, similar to passing an exam in math not always shows the mathematical abilities in say abstract thinking the person really possesses, but more says that a certain curriculum is known adequately, perhaps by cheating or by pulling a series of easy questions.

This has nothing to do with intelligence, some have a sense of direction and propper map reading, others are more inclined to hear tiny details showing something fishy is going on. Someone owns the grace of mingling and saying the right things in the right moment.

--sd

[macmangler]

I very much agree, no one person will know every formula for every situation, perhaps I was misunderstood. I am simply looking for the next step into further understanding calculations. I venture to guess a book... perhaps it is a class room. I am open to oppinion on the topic.

Thanks for your input! I appreciate your help also! Until I discover this book or next step, I have this forum.

Mike

[Lee Smith]

Well, there are some examples in FileMaker's Online Help.

Take a look at [color:blue]FileMaker Reference file by John Mark Osborne, Here I think it will be of help for you. You can also create your own examples when you see one posted in the Forum that you like.

HTH

Lee

[Fitch]

Now the next challenge for my File Maker future is what does this mean and why did it work? Case(ADDRESS 2 <> "", ADDRESS 2 & ¶)

To answer your specific question, the "<>" is notation for "not equal to" and is followed by a pair of quotes with nothing in between them which is notation for "blank" or "empty."

Another way to write this (my preference) would be:

Case( not IsEmpty( ADDRESS 2 ), ADDRESS 2 & ¶ )

Same thing but to my eye a little more legible.

Translation: if there's data in address 2, then use it and add a paragraph. That's it. I.e. if the field IS empty, then ignore it.

[Søren Dyhr]

Same thing but to my eye a little more legible

It's the NULL vs. Empty debate, where Filemaker is pretty liberal ... felt that tiny stitch as well - when seeing it!

--sd

[IdealData]

Personally I have never bothered with any 3rd party books so I can't comment on these references. My knowledge has been built up over 10 years - AND THE FMP ONLINE HELP SYSTEM.

It may be a little terse at times, but it is the authoritative documentation - start there first, every time.