Found set where field A ≠ field B

[ken_s2007]

In a FM database with the following fields:

"insured_value" (number)

"current_value" (number)

… I want to create a script that will create a found set as follows:

"insured_value" is not null.

… and:

"insured_value" ≠ "current_value"

--

Using OMIT with:

Enter find mode

insured_value =

… finds all records with a non-NULL value for field "insured_value", which is what I want.

Next, Find requests do not appear to allow the value of a field as part of the criteria, i.e., "insured_value" "≠ main::main_current_value" does not work.

Do I need to create a global calculation field that calculates if "insured_value" = "current_value", and use that as part of my find process?_

Should I create a loop: go to first records, if if "insured_value" = "current_value", omit record, go next (exit at last)? thanks!

[bruceR]

There are several ways to do this but a looping script will probably be best. How many records are you talking about?

[ken_s2007]

Less than 500 records. I had set up a looping script, first doing a find on all records that met the first criteria, then go to first record, if "insured_value" = "current_value", omit record -- the 'if' part worked, but not the omit (I'm obviously not doing the find stuff correctly), continue through the records until the last. I'm at a guest computer and don't have my exact script handy. Thank you.

[bruceR]

How can the omit not work? I don't think that's possible. What is more likely is that you need to understand what happens after an omit: which is that you are now on the next record.

[ken_s2007]

I'll have to check the script tomorrow when I have access to it, but my guess is I was using omit improperly (the found set was not shrinking). But you've inspired me to (re) work it from scratch, and I've found success. Thanks!

# First, find all records whose insured_value is not nil.

Perform Find [ Specified Find Requests: Omit Records; Criteria: main::insured_value: “=” ] [ Restore ]

# Loop through the records, omitting those whose insured_value = current_value.

Go to Record/Request/Page [ First ]

Loop

[tab]If [ main::insured_value = main::current_value ]

[tab][tab]Omit Record

[tab]End If

Go to Record/Request/Page [ Next; Exit after last ]

End Loop

[bruceR]

Go to Record/Request/Page [ First ]

Loop

If [ main::insured_value = main::current_value ]

Omit Record

Go to record [Previous]

End If

Go to Record/Request/Page [ Next; Exit after last ]

End Loop

[bruceR]

Explanation:

If you were on record 3; and it qualified for omit; you omitted it, and now you are on what was record 4 of the set.

Your script then says go next record; so now you're on what was record 5 of the found set; and you never processed record 4.

[ken_s2007]

Interesting. Your script and mine produced the same found set (given a sample data set below), however when I disabled the first Perform Find (that omitted records with an insured_value of null), your script came up with the same found set as mine, plus records 5 (values of "0", and "0"), and 8 (both null values).

insured_value current_value

All records in order of creation (prior to the first Find that eliminates all records with where insured_value is null); comparison of values:

1; same (10, 10)

2; different (8, 4)

3; different (15, 12)

4; same, but (null, null)

5; same (0, 0)

6; different (2, 0)

7; same (100, 100)

8; same, but (null, null)

9: different, but (null, 1)

10; different (2, null)

After the first Find that eliminates all records with where insured_value is null; comparison of values, and subsequent scripting. (Same results in my script and BruceR's)

2; different (8, 4)

3; different (15, 12)

6; different (2, 0)

10; different (2, null)

Disabling the first Find that eliminates all records with where insured_value is null; comparison of values, my script:

2; different (8, 4)

3; different (15, 12)

5; same (0, 0)

6; different (2, 0)

8; same, but (null, null)

9: different, but (null, 1)

10; different (2, null)

Disabling the first Find that eliminates all records with where insured_value is null; comparison of values, BruceR's script:

2; different (8, 4)

3; different (15, 12)

6; different (2, 0)

9: different, but (null, 1)

10; different (2, null)

BruceR's excludes these two that mine kept:

5; same (0, 0)

8; same, but (null, null)

So if I'm understanding this correctly, my script would work for its intended purpose (I think, unless my test data set is weak), but only due to data being initially excluded (the first find), so pure, and dangerous luck; but Bruce's is the correct way to do it, since it works properly even standalone.

You've probably saved me some headaches by pointing this out!

(Attached .fp7 file: Admin with a null password.)

show_records_comparing_value_of_two_fields.fp7.zip

Edited November 13, 2010 by Guest
Re-sent attachment (zipped this time, sorry)

[bruceR]

No, yours would never work. It is dependent on the order of records where the record you skip is not a record that should be omitted.

[ken_s2007]

Seems to work for me.

Edited November 14, 2010 by Guest

[bruceR]

There is no attached file.