kllrwlf Posted January 7, 2004 Share Posted January 7, 2004 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! Link to comment Share on other sites More sharing options...
CobaltSky Posted January 7, 2004 Share Posted January 7, 2004 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 More sharing options...
Vaughan Posted January 7, 2004 Share Posted January 7, 2004 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 More sharing options...
-Queue- Posted January 7, 2004 Share Posted January 7, 2004 That being Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted January 7, 2004 Share Posted January 7, 2004 Vaughan must have been excited... Isn't there something wrong with where this "P" or " Link to comment Share on other sites More sharing options...
kllrwlf Posted January 8, 2004 Author Share Posted January 8, 2004 That seems to have worked! Thank you for your help! Link to comment Share on other sites More sharing options...
CobaltSky Posted January 8, 2004 Share Posted January 8, 2004 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(" Link to comment Share on other sites More sharing options...
Vaughan Posted January 8, 2004 Share Posted January 8, 2004 Ugo and Ray -- quite right, thanks for the correction! Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7406 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 accountSign in
Already have an account? Sign in here.
Sign In Now