Comparing lists for missing names

[bob sherman]

I need to compare a master list with a secondary list to find which names are missing from the secondary list. I have created two files that sit side by side. The master list is imported into one file and the secondary list is imported into the second file. The files are alphabetized, and then I scroll down each file looking for a missing name. Can anyone suggest an automated process to do this? Thanks.

[CobaltSky]

Hello Bob,

I suggest that you create a relationship from the file that has your master list to the file that has the secondary list, which matches the name field in both files.

Then create an unstored calculation field in the master file which has a formula along the lines of:

Case(Count(NameRelationship::Name), "", "X")

The calc field will then automatically show an 'X' against each name in the master list which is missing from the secondary list.

[Pupiweb]

Try this:

in the file in which you import data create a global gText field and relate it to field names of the other file

Import data

run this script

Go to layout (a layout with only field name in it)

Copy all records

Go to layout with field gText in it

Paste in gText

Go to related record (Show, Relationship gText->Name)

Perform script in the other file (see below)

Find Omitted

The procedure has a limit given by the 64 k characters a field can hold, so if you've thousands of long names it could fail

[danjacoby]

Cobalt has a simpler solution, but I think he's missing part of the calc:

Case(Count(NameRelationship::Name), "X")

should be:

Case(Count(NameRelationship::Name) > 0, "X")

[CobaltSky]

Nope, not missing an operator, actually.

The Case( ) function requires a boolean test, where zero results are false and non-zero results are true. Since Count( ) will return a boolean, there is no need to compare it to zero. Ie. if it is zero then the Case will fail, if it is greater than zero the Case will succeed.

Therefore the '> 0' is redundant.

[bob sherman]

Thanks for your help. The master file now puts an x next to the last name that is the same as the last name on the secondary list. I think the answer is no, but can the comparison include two fields, first and last name? Thanks

Bob

[CobaltSky]

Hi Bob,

The answer is yes.

To make it work that way, you'd first need to create a calculating field 'cFullName' in each file, which concatenates the first name and last name fields - eg:

FirstName & " " & LastName

...and then alter the relationship so that it matches the calculating fields to each other.

To complete the adjustment, it would also be best to alter the flag calc to:

Case(Count(NameRelationship::cFullName), "", "X")

Then the 'X' will only appear against those records where the combination of both names does not appear in the second file.

[bob sherman]

Thanks Ray,

I've made the changes and it looks right, but now it's not working at all. I must have missed a connection. I have uploaded the file to view.

Thanks

Bob