If(IsEmpty... problems

[sukisue]

I'm trying to do (what I thought should be) a simple calculation to combine two fields...

If(IsEmpty(RepositoryName),"", (RepositoryName) &"; ")&

If(IsEmpty(RepositoryPlace),"", (RepositoryPlace) & ". ")

Example Problem:

RepositoryPlace Field is empty and RepositoryName is "Private Collection"

Calculation comes out to be:

Private Collection;

How do I get rid of the ";" when RepositoryPlace is empty?

Thanks!

FileMaker Version: Dev 6

Platform: Mac OS X Jaguar

[-Queue-]

Case(Length(RepositoryName), RepositoryName & Case(Length(RepositoryPlace), ";")) & Case(Length(RepositoryPlace), RepositoryPlace & ".")

[sukisue]

Thanks- works great. But I'm not sure I understand it!

[-Queue-]

Okay, here's a little explication.

Length(field) returns either zero (if field is empty) or the length of field. If Length( ) returns zero, then the Case( ) will not return a value, since zero is interpreted as false. Any other number besides zero is interpreted as true. So, if RepositoryName holds a value, Length( ) is true and that portion of the Case( ) executes.

By putting another Case( ) inside the first one, you ensure it will only evaluate if the first one is true. So you'll only get a semicolon if RepositoryName is not empty AND RepositoryPlace is also not empty.

Does that help?

[sukisue]

I do get the length part - but the other nested Case() still confuses me.... especially when I try to apply all this to a calculation that includes additional fields. For example, I have one where I want to string together 5 fields (title, author, publisher, pub date, page#s) kind of like a simple citation.

Also- if I wanted to use the TRIM function- for each field, where would that fit it?

Thanks so much!

[-Queue-]

Okay, let me provide an extreme example.

Case( Length(A), A &

Case( Length(:, B &

Case( Length©, C &

Case( Length(D), D ))))

One way to visualize this is like a hierarchical menu.

A

--> B

--> C

--> D

Statements B, C, and D will not be evaluated unless A is true. C and D will not be evaluated unless A and B are both true. And D will not be evaluated unless A, B, and C are all true.

You can accomplish the same thing by separating each test into its own Case( ), with much more overhead:

Case( Length(A), A ) &

Case( Length(A) and Length(, B ) &

Case( Length(A) and Length( and Length©, C ) &

Case( Length(A) and Length( and Length© and Length(D), D )

Notice that each Case( ) contains a reference to Length(A). So you can, to use a mathematical term, 'factor' out this portion into its own Case( ), which becomes:

Case( Length(A), A &

Case( Length(, B ) &

Case( Length( and Length©, C ) &

Case( Length( and Length© and Length(D), D ))

similar to factoring A + AB + ABC + ABCD into A(1 + B + BC + BCD) in math.

You then notice that Length( is referenced in all Case( )s except the already factored A portion. So you pull that into the main subCase( ) of A and get:

Case( Length(A), A &

Case( Length(, B &

Case( Length©, C ) &

Case( Length© and Length(D), D )))

similar to factoring A(1 + B + BC + BCD) into A(1 + B(1 + C + CD)).

Now you can see that Length© is referenced in the remaining two Case( )s. So extract it as we have the other two:

Case( Length(A), A &

Case( Length(, B &

Case( Length©, C &

Case( Length(D), D ))))

similar to factoring A(1 + B(1 + C + CD)) into A(1 + B(1 + C(1 + D))).

And we are back to our original statement.

So the idea would be first to determine what critieria requires each result to be evaluated. Set that up as a long group of individual Case( )s. Then, piece-by-piece, group (factor) the portions that all or several Case( )s have in common into subgroups.

In your case, what determines when a punctuation mark occurs after one of the fields? I would assume it's based on whether the fields before and after it both have values, as I did above for the semicolon. You only need apply this logic to the rest of your fields to build a suitable calculation.

If you want to Trim( ) each field, then use it when calling each field, i.e. Trim(title) & " " & Trim(author) & " " Trim(publisher), for a simple example. If you want to use it when testing the length of each field, use Length(Trim(field)).

[Ugo DI LUCA]

Well,

Another approach would just use Text functions.

Trim(Substitute(Substitute(" " & A & ";" & B & ".";";.";"");" ;";""))

You might turn on the "not evaluate when empty"

FileMaker Version: 6

Platform: Mac OS 9

[-Queue-]

Good point, Ugo. With two fields, this would be smooth. With five, it could get more complicated and obtuse than a nested Case( ), unless the logic happens to be similar.

Suki, you can ask Ugo to explicate his streamlined technique.

[Ugo DI LUCA]

Right !

Actually this calc was wrong as I didn't paid attention to the space in "; "

The corrected calc would be :

Trim(Substitute(Substitute(" " & A & "; " & B & ".";"; ." ;"");" ; ";""))

If A is Empty and B is not ---> ; B.

If A is not Empty and B is ---> A; .

If both A and B are empty---> Null if you turned on "not evaluate if empties"

So we're going to substitute the occurrences of "; ." by "." and those with a leading "; " with a blank.

The first is easy.

For the second, unless A is a constant value, it is fairly impossible to substitute a leading "; " without substituting all your ";".

So we'd fake a space which leads to a new chain of " " & A & "; " & B & "."

With that leading space, we can now substitute occurrences of " ; " by "".

We'd finally Trim the calc to get rid of this fake space.

Clear enough ?

FileMaker Version: 6

Platform: Mac OS 9

[sukisue]

Got it! I'm not sure which solution I'll use- will have to play with them for a bit.

Thanks much for your time!

[Damocles]

This might do what you want, with the caveat that the first field in the string must always be present.

Title &

If(not IsEmpty(Author), "; "&Author,"")&

If(not IsEmpty(Publisher), "; "&Publisher,"")&

If(not IsEmpty(PubDate), "; "&PubDate,"")&

If(not IsEmpty(Pages), "; "&Pages,"")&"."

As with most of my solutions, this is "quick and easy" but certainly lacks the elegance of the other two solutions that were presented.

Hope this helps.

Paul

FileMaker Version: 6

Platform: Windows XP

Strung together.zip

[Rigsby]

The solutions here are all good, but the whole thing can be done in a much easier fashion. The difference is only where you choose to put your