Jump to content

If(IsEmpty... problems


This topic is 6490 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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), B ) &

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

Case( Length(A) and Length(B) 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), B ) &

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

Case( Length(B) 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(B) 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), 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), 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)).

Link to comment
Share on other sites

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. wink.gif

Link to comment
Share on other sites

Right ! smirk.gif

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 ?

crazy.gif

FileMaker Version: 6

Platform: Mac OS 9

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6490 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.