If member of table Calculation

[rayh34]

Need to know the most efficent way of doing something close to this:

If table::field is in the records of 2nd table::field then etc.

would work just as well if not member of...

We are inputing websites into a database and doing a whois data capture on the base domain.

So URL table one has all the websites and

Whois Table has all the domain info.

The URL table can have multibles of the same domain, but with different websites. So, was looking for way to flag the user that that URL while may be new has a domain that has been catured already so the user does not have to spend time doing a search on the second table.

[T-Square]

Create a relationship between the two fields, and a simple calculation:

If(RelatedTable::Field; "I'm here"; "I'm not")

This uses a relationship to test whether there's anything on the other end of the relationship.

David

[rayh34]

Wow, that sounds almost too easy.. testing it out now.

Thank you, that was very simple, and works very quickly. ???

Edited January 28, 2007 by Guest

[LaRetta]

If(RelatedTable::Field; "I'm here"; "I'm not")

This is boolean test which will only produce 'true' if the field contains a number. Try:

If ( not IsEmpty ( RelatedTable::Field ) ; "I'm here"; "I'm not")

Also, uncheck 'Do not evaluate if all referenced fields are empty' if you need a default result to display.

LaRetta

[rayh34]

LaRetta, I tested it against both ip and text domain, seems to work fine both ways. Here is how I set up my test:

[T-Square]

LaRetta--

AFAIK, a bare If test like the one I presented works with a text field--so long as there's *something* found in the remote field, the test will evaluate to True. A failure to match to the other side would yield a Null set, which should evaluate to False.

Good point about the evaluate when checkbox.

David

[comment]

LaRetta is correct. Well, almost correct.

This is boolean test which will only produce 'true' if the field contains a number.

Actually, it will only produce 'true' if the field contains a digit that is NOT zero.

TestRelated.fp7.zip

[rayh34]

Am I missing something, because in my tests, it's doing exacts matches.. if I change one letter or number in the comparison, it sees the difference and reports correctly.

[LaRetta]

No, rayh34, Comment wasn't disproving my suggestion but rather clarifying it.

This is boolean test which will only produce 'true' if the field contains a number.

...if the field contains a digit that is NOT zero.

Sloppy wording of mine. Thanks for the catch. For boolean to produce true, the field must contain an INTEGER. Zeros are numbers but not integers; a fine (but critical distinction). And integers are not just positive numbers; negative numbers (except -0) also fit. It is moot in a text field but it can throw you when working with numbers. As Michael points out, an integer is a DIGIT other than zero and not the value in the field.

BTW David, if I ever correct someone's suggestion, I always create a test file first even if I 'know beyond doubt' that I'm right; just as, if someone corrects me, I'll test their response. This was no exception. As it stands, testing first is mandatory in my book because I've known FM behaviors to change just as I've also been just flat wrong.

[color:green]CLARIFICATION: rayh34, your post was in Calculation Engine and you are referring to an If[] script-step. I had tested the calculation. But upon testing it in script still makes no difference. I wonder if this is a version behavior change! Script fails in 8.0v3 when no integer is in the field. So has it changed in 8.5? Did you try Comment's file? I did and the results seem to hold and he is using vs. 7 (I believe). And I tested using vs. 7.0v3 here and it also fails just as it did in 8.0v3.

LaRetta

Edited January 28, 2007 by Guest
Added clarification

[Oldfogey]

Thanks for the heart attack!! I've been using If(relship::childkey) to check for the existence of child records for ages. It works fine with Text childkeys.

Just did a heap of testing and my heart attack is back, and how!

I hope whoever first published that simple test, is reading this thread.

My tests have been working fine because I've used text keys which contain numbers only and I only check for TRUE in calculations. Scripts work fine with IF...ELSE.

With non-numeric text, the test always returns FALSE.

Thanks, Laretta. You wouldn't like to find out where I've put in all those tests would you?

Edited January 28, 2007 by Guest

[LaRetta]

Nope, identical fails for me, Paul. Please try this test file with 8.5.

text field is joined to text field. Shouldn't we be getting YES on Bravo since it is the same in both field/tables?

according to the FMP description of CASE; the "I'm not here" result should be the default result.)

You must uncheck 'Do not evaluate.'

Is this prime example of why an IsEmpty() test should always be used to see if there are related records? Or do we have version differences here? Or am I whacky? Okay, yes I am. But am I whacky in my understanding of it?

test2.zip

test2.zip

Edited January 28, 2007 by Guest
Added sentence

[Oldfogey]

Laretta,I think you might be replying to an earlier, unedited, version of my post.

I'm with you 99% now. Your file fails on both equal and non-equal.

Just to clarify,

TEXTtext::TEXTtext is just not on. Shambles. This is your example.

TEXTnumbers::TEXTnumbers returns TRUE if TRUE but not FALSE if FALSE. This is what I've been using for years - serial numbers in text fields. I suspect the FALSE may have worked in earlier versions.

The whole problem here (and I hope T-square reads this) is the wrong assumption that NULL evaluates to FALSE. If you read enough of the Help, this becomes obvious but why FMI don't just spell it out is beyond me. (Access is even worse in its handling of null-type stuff.) There are some very good grounds for having 3-way logic as in True-False-Unknown but there are far better grounds for just having 2 values - TRUE and FALSE.

Edited January 28, 2007 by Guest

[rayh34]

LaRetta,

I downloaded your test2, and it worked just as you said, only weird part was if I used text would get "No" in calc, if I used number that was in the second table i would get "Yes". If I used a number that was not in the second table, I would get "" nothing. The script side reported all.

Ok, Now for my script... After seeing the results of your tests, I when back and examined it closer and found that the couple of domain tests I used happen to have at least 1 number somewhere in the domain name. when I tried straight text, always false... great.. back to the drawing board!!

LaRetta, I tried the [not IsEmpty ( Sharked Domains::Domain Name )] and seems to work correctly under numerical/text/mixed...lol

Thank you for saving me a huge headache!! And sorry about the one I gave everyone else!

Edited January 28, 2007 by Guest

[comment]

The whole problem here (and I hope T-square reads this) is the wrong assumption that NULL evaluates to FALSE.

NULL (i.e. 'empty' in Filemaker speak) does evaluate to FALSE. The wrong assumption here is that the opposite is also true, i.e. that ANY string that is not empty evaluates to TRUE. But only a string that returns a non-zero when converted to number returns TRUE.

the field must contain an INTEGER

No, a fraction returns TRUE just as well.

[Oldfogey]

I stand corrected (and relieved.)

( Not so relieved about messing up the quick tests I did.)

I've not seen that "..when converted to number..." qualification before. It has interesting ramifications if one were silly enough to check text fields for True and False. 'Fred' is FALSE but 'Fred2' is TRUE.

[T-Square]

Well, it's clear that I know enough to set sail, get out on the open seas, and sink unceremoniously without a trace.

David

[LaRetta]

:giggle: Hey David! Wanna compare errors! In fact, you have less than most people! Silly man. Besides, you got that information from somewhere probably on one of those OTHER forums. ROF! Why do you think I test constantly - it's because I've been caught like this and I trust NOBODY - only tests. Don't you DARE even KID about leaving us!

Edited January 30, 2007 by Guest

[Lee Smith]

Your statement is false, but it has me rolling on the floor laughing my ass off.

Lee

[Oldfogey]

Wanna compare errors!

Let's not go there!!! Please. :shower: