Calc field with If statement

[kcep]

I'm stuck.

My calculation field has the following type of calculation (I'm looking for the calculation result to be text)

If ( AddressBook::NameField = "John" ; "Send Birthday Card" ; " " )

This calculation works as long as John is the first record in the related table. However, if John is a subsequent record to the first - it doesn't work.

result one should = insert the text "Send Birthday Card"; result two should = an empty field.

This is just an example. My real field result is much more complicated, and has nothing to do with trying to figure out when to send a birthday card to John.

Edited November 12, 2005 by Guest

[Lee Smith]

I prefer using the Case Statement, for me it is easier to structure.

I'm not sure of what you are trying to accomplish by what you posted, why not just post a copy of your files involved. I'm afraid that there are things that you are not telling us that is effecting your If Statement.

Lee

[kcep]

Thanks for the feedback. Actually there is a "Case Statement" inside of the "If Statement". It's somewhat complicated. However, even if I remove the "Case Statement" from within the "If Statement" and just use a simple "If Statement" - it doesn't find related fields subsequent to the first related field. The file has 18 tables and is somewhat involved. I'm afraid to try and post it. The calculation works great in finding the first record in the related table - it's just not searching through the rest of the related records.

If John is showing up in portal row #2 or more of an unsorted portal - the calculation will not work. However, if John is in row #1 of an unsorted portal it works.

Edited November 12, 2005 by Guest

[Lee Smith]

As I stated before, I'm not sure of what you are trying to accomplish. Is this calculation part of the relationship, or are you trying to Go To Related Record, Portal Row, etc.

I guessing that you are trying to make the first (and only) record to show in the portal to be John's record.

Lee

[LaRetta]

Hi kcep,

When you are in a main table looking at related records and MANY records relate, FM will always display the first record by its natural sort state (creation order). You can change WHICH record relates first by sorting your relationship by various related fields. Your example implies the perspective is in the main table. That may well be necessary but not always! If you want to isolate a specific related record (and relationship sort won't cut it), sometimes you can simply place the calculation in your related file; or further isolate your relationship on-the-fly. In addition, your example is hardcoding a name within the calculation which is bad and probably not your REAL calc - which is precisely why you must provide the real purpose of the calc.

So 1) Relationship sort, 2) Consider perspective shift, 3) Filter relationship dynamically and 4) Give us more information about what you're trying to do (and show us your REAL calc).

You are providing an example like "my throat hurts" to a doctor and expecting him to know whether you have a sword stuck down your throat or simply talked too darned much yesterday. Oh ... that 'talked too much' part made me giggle - probably because it fits me so well!

So cough up some REAL information and we'll walk you through it, okay?

LaRetta :wink2:

[kcep]

Ok - I've attached a zip file of an example.

Hope the attach thing works

Notice the warning on record six of the "Songs" table.

This warning should appear when there is the following

associated with a song record:

Type of use in any of the related files =

"Artist Cut"

"Sheet Music"

"Sound Track"

or date of license is after 1-1-1900

The warning should disappear when a number equal to or greater than 1 is entered in the "PRO_Number" field.

Thanks so much for your interest!

Tom

FindJohn.fp7.zip

[comment]

Try it this way?

FindJohn.fp7.zip

[Lee Smith]

holy cow, comment. I didn't even get a chance to download it.

:giggle:

[kcep]

WOW, COMMENT !

I'm still testing every possible combination - SO FAR, SO GOOD!

You're amazing and FAST !

Thanks, Tom

[comment]

I didn't even get a chance to download it

LOL - there wasn't much else to do. LaRetta has just now told me I have beaten her response with an identical file by a mere 15 seconds...