How to compare 2 lists from related databases

[bobsmith]

If the related field indicates that the item exist in the other database make a calculated field in each database

case (isvalid(your related field),0,1) then you can do a find on calculated field = 1 if you want to find the records that are not present in the other database.

[tapededitions]

HELP!

I need to compare 2 different lists from 2 different databases (that are related). I need it to look at one list and see if the item exists on the other, and if it does mark a field saying so.

I'm using Windows 2000 and FileMaker 4.0 (soon updating to 5.5)

It will help me tell if a customer has already been sent that item so that we don't send it again.

Thanks,

Mindy

[ March 06, 2002, 12:18 PM: Message edited by: tapededitions ]

[Steven H. Blackwell]

What OS are you using? This can be done on the Mac OS with an AppleScript but there are no doubt other ways as well such as using APtternCount to compare the two lists.

Old Advance Man

[Keith M. Davie]

bobsmith offered what is essentially a boolean solution. More about booleans and isvalid can be found from a useful reference such as Scriptology. If you don't have Scriptology, perhaps you should get it. Every FMPro developer would benefit from having this useful resource. Just don't try running your db ScriptMaker scripts on the www without being aware of the problems there. See the Internet Forums for that aspect.

If your boss is too cheap to get Scriptology for you, get a different job.

[bobsmith]

Basically what this is saying is:

For example if the two databases are related by an invoice number, the record with invoice number 10001 would look in the other database for invoice 10001, if it is there (is valid) the calulation would return a 0, if it can not find the record would return a 1. Then you would do a search of the calculated field = 1 and this would find all records that do not exist in the other file. I agree with Keith a good manual will help. I have Scriptology and use it all the time.

[Fitch]

Maybe it would be less confusing if we turned the calculation around, so it's just:

IsValid(Item::RelatedItem)

Explanation: create a Relationship, (called "Item" in this example) from one file to the other using the Item field from each file, i.e., the field you're trying to compare. Then define a calculation field with the above formula, which just returns a "1" (or "true") if there's a match, i.e. a 1 means "customer has already been sent that item." In Bob's formula, a result of 1 has the opposite meaning.

[tapededitions]

bobsmith I'm a little confused. Could you elaborate on your directions just a little?

Thanks,

Mindy

[tapededitions]

Thanks for the suggestions! I am going to get the book.

I tried the suggestions and there is one problem. I need it to only compare the found list from one of the databases. All items ever shipped are in this database and I need it to only check the items sent to a specific customer number. There is no invoice number because this may include shipments over several years. Any other suggestions?

Mindy

[Keith M. Davie]

"I need it to only check the items sent to a specific customer number." Then let that number become the unique identifier upon which to build your relationship.

[Fitch]

Sounds like you're going to need to create a field in each database that combines the customer number and product number. If you like poetry, you can say you're "calculating a concatenated key."

So in each file, make a ConcaKey calc, a text result:

customer number & "~" & item number

Then use this field to set up the relation and calculation we talked about earler:

IsValid(ConcaKeyRelation::RelatedItem)