Having difficulty with If function of FileMaker

[saraswati]

Hi ! I am having problem using the If function of filemaker pro.

I have defined a text field called Fin.Aid Student which has value pop list YES, NO. I have defined another field called FinaidSecond which is a calculation field. The formula used is

If( Fin.Aid Student = "YES", "No Second Extension Allowed", " ")

I have made the calculation result option box as text.

This does not work when i test it. Whether the Fin.Aid Student field selected is YES or NO it always displayes No Second Extension Allowed in the FinaidSecond field. What could be the problem with this ?

[Vaughan]

Does the field get "YES" entered in uppercase or lowercase or a mixed case entry?

Try this calculation instead of Fin.Aid Student = "YES":

PatternCount(Trim(Lower(Fin.Aid Student)), "yes") > 0

This trims all leading and trailing spaces and converts everything to lowercase, making it much more reliable.

I've found an even more robust method is not not use "yes" and "no" options for stuff like this. Instead, use a single check box with a single option (in your case it might be "First Extension Allowed". Either check the box or leave it empty. No need to fart around with yeses and nos. A cleaner interface too: the option explains itself.

This makes subsequent calculations much easier because you only need to look and see if the field is empty ot not. The calculation would then look like

IsEmpty(Fin.Aid Student) to see if it is empty, or

IsEmpty(Fin.Aid Student) = 0 to see if it is selected.

[CobaltSky]

Hi saraswati,

It's not obvious what is causing your problem.

The 'If' test is not case sensitive, so using the Lower( ) function in your test is unlikely to solve it.

If the Fin.AidStudent field were defined as a number field, that would cause the calculation to fail, but in the other direction (ie it would always be blank, even when 'yes' was entered into it).

In fact I can think of a dozen problems that would make it fail to produce the 'yes' result, but not too many that would make it *always* produce it. In fact I was so puzzled by your problem that I tested it by creating a db with two fields in it exactly matching your description, and I copied and pasted your 'If' formula from your message. It works perfectly (see attached copy).

So, I'm afraid that whatever it is that is causing your problem, it must relate to something that you haven't mentioned in your post?! What else can you tell us??

FinAidTest.zip

[saraswati]

Hi ! Hey even I tried making a dummy file with just 2 text fields and with one field having a drop down box of values yes and no. & tried using the if function it works absolutely fine but in my main project file it is not working.

[saraswati]

the word YES is actually selected from a drop down box. I have made a value list as a drop down box containing values YES & NO I cannot change the layout as it is the way my boss wants me to do.

Thanks

Sara

[CobaltSky]

Hi Sara,

Whether the Yes is a drop-down list should have no bearing on whether the 'If' formula works.

The fact that both your and my test files work perfectly clearly shows that the problem has nothing to do with the logic or the structure of your If( ) formula. Therefore you need to look elsewhere to find the problem in your main file.

The next thing to check is that the field on your layout that you are entering the 'yes' into (with the drop-down list) is in fact the same field which is being referenced by your 'If' function (and is sourced directly from the current file, not via a self-join relationship or a relationship to another file).

[tallboy755]

Why not just delete the field and add 2 new ones ... If you can make if work in the test file ... why not just duplicate what you did in your real file ... Also it might be caused by the "." (period) in your feld name .. try using an underscore

[Vaughan]

"Whether the Yes is a drop-down list should have no bearing on whether the 'If' formula works."

It could if the field has some other value entered into it other than yes or no. Put a normal field on the layout, expand it to display a couple of lines, and take a look see what is really entered into the field. My guess is it'll have a carriage return and some other text in it.

[BertBoye]

I was yesterday adding this IF statement to my solution and it didn

[saraswati]

Hi ! When I used the Pattern Count & Trim function and changed the Field Name to FinAidStudent by removing the period & spaces the If function worked.

Thanks a lot for all the replies.

I really appreciate it!

[Vaughan]

Yep. <sigh>

I tell people all the time not to use spaces or other weird characters in field names, but it seems they have to learn the hard way...

Even after getting FMP's warning about not being able to use the field in calculations people still use reserved characters like +_)(*& etc. Their usual reply is that they never thought they'd use the field in a calculation!