Jump to content

How to compare 2 lists from related databases


This topic is 8058 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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 ]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 crazy.gif

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This topic is 8058 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.