matching zip codes that have multiple tax rates
means that you have more than one record with the same zipcode, but potentially a different tax rate?
And you need to export the data to a file with one record for each zipcode and whichever tax rate is largest?
Try this two step process:
Define a new table with just a zipcode field. Set a "unique values", "validate always" validation rule on the zipcode field.
Import all your records from the original table into this new table. Records with duplicate zipcodes will be filtered out during the import.
Define a relationship from this table to the original, matching on the zipcode fields. Open the relaitonship details for this relationship and specify a sort order for the records in your original table to sort them by Tax Rate in descending order.
From a layout based on your new table, export the data, specifying the zipcode field in your new table and the tax rate field from your original, now related table. The sort order will ensure that the largest tax rate is the rate value exported.
Workds great. Thanx Phil.
How can I set a flag up in the origional table that flags matching zips with diferent rates?
For starters, you could specify the unique values validation for the zipcode in that field.
For existing data, you can enter find mode, put a ! in the zicoded field, perform the find, sort by zipcode and get groups of records where there is more than one with the same zipcode.
You can also define a self join based on the zipcode field to use the relationship to check for other records with the same zipcode. With that method, you can even add a portal that displays them.
Figured the flag out.
I made 2 more occurences of the origional table and made the relationshipe zip = zip in both of them. I sorted the relationship one decending, and the other acending. Then I created three flags.
zip=Taxrates 2::zip and
ID ≠ Taxrates 2::ID and
rate ≠ Taxrates 2::rate ;1)
zip=Taxrates 3::zip and
ID ≠ Taxrates 3::ID and
rate ≠ Taxrates 3::rate ;1)
If(Flag=1 or Flag2=1;1)
I can perform a find for the Flag3 to find all zips that match but rates are different.
Why not use this single relationship?
TaxRates::zip = Taxrates 2::zip AND
TaxRates::ID ≠TaxRates 2::ID
IF there is any related record in TaxRates 2, you have a record with a duplicate zip. IsEmpty ( TaxRates 2::zip ) will be false if there is any other record in the table with the same zipcode.