Jump to content

Checking the second or third record within portal


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

Recommended Posts

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! smile.gif

Link to comment
Share on other sites

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")

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Hi Vaughan,

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

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

Case(not PatternCount("

Link to comment
Share on other sites

This topic is 7500 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.