I have two databases. One database with the customer information and another database with zipcodes, city and state.

I would like to compare the zipcode information (city and state) that the customer gave me with the zipcode information in the zipcode database.

I'm currently able to compare the city and state that the customer gave me with the first record in the portal of the zipcode database.

How do you do a compare with all the records within the portal?

Here's the relationship:

(Customer Database).........(Zipcode Database)

Zipcode field ----------------> Zipcode field

Here's the database

(Customer Database)<----------(Zipcode Database Portal)

Customer Database:

Arvada, CO 80002

Zipcode Portal:

Denver, CO 80002

Arvada, CO 80002

Wheat Ridge, CO 80002

My current calculation (which only checks the first (Zipcode Portal) record:

If( Customer City = zipcode::city, "" , "City Might Not Match" )

I would like the calculation to check all the records within the portal instead of only the first record.

Thank you!

Hello kllrwlf,

There are any number of ways to approach this, but one that you might like to consider would be to create a value list called 'ZipCities' in your Customer Database, and select the 'Use values from a field' option, then choose the 'Only related values' setting and select the relationship to the Zipcode Database, then choose the City field from the zip code database as the source for the value list.

Once that is in place, you will be able to compare the customer data with all the related records with a formula along the lines of:

Case(not PatternCount(ValueListItems(Status(CurrentFileName), "ZipCities"), City), "No match")

Not often I can imporve on Ray's suggestions, but... as it is, the PatternCount calculation will return true for city "York" if the value list has "New York" in it, which probably isn't what you want.

Case(not PatternCount("P" & ValueListItems(Status(CurrentFileName) & "P", "ZipCities"), "P" & City & "P"), "No match")

Replace the P character with the paragraph symbol.

That being

Vaughan must have been excited...

Isn't there something wrong with where this "P" or "

That seems to have worked!

Thank you for your help!

Hi Vaughan,

Thanks for picking up on that. Yes, of course! Funny how things 'slip through the cracks' from time to time...

Ugo's right, however, that you didn't quite manage to 'P' in all the right places. It should be:

Case(not PatternCount("

Ugo and Ray -- quite right, thanks for the correction!