7 Replies Latest reply on Jun 22, 2016 10:26 AM by PeterDoern

IF THEN function question for alphabetical location

I am looking to do a calculation based on the first three letters alphabetically. I already have a field that shows the first three letters that I can reference. For example:

If ABC-GEH, return row one

If GEH-LAR, return row two

if LAR-POR, return row three

I assume this doesn't work, but just thinking out loud:

If ( Location = "ABC" < "GEH" ; "Row One" ; If ( Location = "GEH" < "LAR" ; Row Two" ; etc....) )

• 1. Re: IF THEN function question for alphabetical location

Use the Case() function instead of if()

Case(

Location "ABC" < "GEH" ; "Row One" ;

Location "GEH" < "LAR" ; "Row Two" ;

etc.... ;

"no result"

)

1 of 1 people found this helpful
• 2. Re: IF THEN function question for alphabetical location

Required something after location so i used "="...no matter the value, it is placing "Row One" as the returned value.

Case(

Location = "ABC" < "GEH" ; "Row One" ;

Location = "GEH" < "LAR" ; "Row Two" ;

etc.... ;

"no result"

• 3. Re: IF THEN function question for alphabetical location

that's because "ABC" is always less than "GEH". The first condition will always be Location = 1.

Try:

Case (

location < "GEH" ;

"Row One" ;

location < "LAR" ;

"Row Two" ;

location < "POH" ;

"Row Three" ;

// Else

"No Result"

)

• 4. Re: IF THEN function question for alphabetical location

I figured you were using something different for your actual calculation.

Text values shouldn't be used with numeric operators like <, >, >=, =< as a best practice as they can yield odd results.

So you can either split it out like:

Case(

Location = "ABC" ; "Result 1" ;

Location = "BCD" ; "Result 1" ;

Location = "CDE" ; "Result 1" ;

Location = "EFG" ; "Result 2" ;

Location = "FGH" ; "Result 2" ;

Location = "GHI" ; "Result 2" ;

etc...

or you will need a custom function to check and see if the text value matches in a list of values. Like this one:

Which would look something like:

Case(

InList( List( "ABC" ; "BCD" ; "CDE" ) ; Location ) ; "Result 1" ;

InList( List( " EFG" ; " FGH" ; " GHI" ) ; Location ) ; "Result 2" ;

etc...

1 of 1 people found this helpful
• 5. Re: IF THEN function question for alphabetical location

Text values can't be used with numeric operators like <, >, >=, =<.

FileMaker can compare text strings using these operators. I just plugged my case statement into data viewer and it works like a charm.

• 6. Re: IF THEN function question for alphabetical location

Yeah, sorry, I changed my statement to say "unexpected results". I still would only use string matching functions and not numerical operators.

I dealt with an issue a long time ago that I'm having trouble recalling at the moment, but for some reason it was due to someone using numerical operators with text strings.

• 7. Re: IF THEN function question for alphabetical location

Yes, I saw that... sorry for preserving your original sentence in quotes.

I think the list matching function is good if we're dealing with known values (like airport codes for example). If we are dealing with every combination of 3-character locations under the sun then it becomes unwieldy.

I didn't put my case statement through super-rigorous testing so I'll put my confidence in it at, say, 85%.