sukisue Posted April 3, 2004 Share Posted April 3, 2004 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 More sharing options...
-Queue- Posted April 3, 2004 Share Posted April 3, 2004 Case(Length(RepositoryName), RepositoryName & Case(Length(RepositoryPlace), ";")) & Case(Length(RepositoryPlace), RepositoryPlace & ".") Link to comment Share on other sites More sharing options...
sukisue Posted April 3, 2004 Author Share Posted April 3, 2004 Thanks- works great. But I'm not sure I understand it! Link to comment Share on other sites More sharing options...
-Queue- Posted April 3, 2004 Share Posted April 3, 2004 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 More sharing options...
sukisue Posted April 4, 2004 Author Share Posted April 4, 2004 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 More sharing options...
-Queue- Posted April 5, 2004 Share Posted April 5, 2004 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)). Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted April 5, 2004 Share Posted April 5, 2004 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 Link to comment Share on other sites More sharing options...
-Queue- Posted April 5, 2004 Share Posted April 5, 2004 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. Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted April 6, 2004 Share Posted April 6, 2004 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 Link to comment Share on other sites More sharing options...
sukisue Posted April 7, 2004 Author Share Posted April 7, 2004 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! Link to comment Share on other sites More sharing options...
Damocles Posted April 7, 2004 Share Posted April 7, 2004 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 More sharing options...
Rigsby Posted April 12, 2004 Share Posted April 12, 2004 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7318 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 accountSign in
Already have an account? Sign in here.
Sign In Now