5 Replies Latest reply on Jul 10, 2012 1:05 PM by philmodjunk

    Flagging zip codes and tax rates

    MarcMcCall

      Title

      Flagging zip codes and tax rates

      Post

      Hey All,

       

      I'm trying to figure out and easy soulution to my problem.  I have created a database of zip code and tax percents.  What I need to acheive is a way to flag matching zip codes that have multiple tax rates. I need to be able to export a file reflecting the all zip codes filtering out the duplicates, and if their are similar ones using the one that has the higher percent in the exported file.  This is a procedure I need slim lined as posible as I will need to perform it monthly.

       

      Any assistance is apreaciated. 

        • 1. Re: Flagging zip codes and tax rates
          philmodjunk

          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.

          • 2. Re: Flagging zip codes and tax rates
            MarcMcCall

            Workds great. Thanx Phil.

            How can I set a flag up in the origional table that flags matching zips with diferent rates?  

            • 3. Re: Flagging zip codes and tax rates
              philmodjunk

              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.

              • 4. Re: Flagging zip codes and tax rates
                MarcMcCall

                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. 

                Flag1
                If(
                zip=Taxrates 2::zip and
                ID ≠ Taxrates 2::ID and
                rate ≠ Taxrates 2::rate  ;1)
                Flag2
                If(
                zip=Taxrates 3::zip and
                ID ≠ Taxrates 3::ID and
                rate ≠ Taxrates 3::rate  ;1)
                Flag3
                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.
                • 5. Re: Flagging zip codes and tax rates
                  philmodjunk

                  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.

                  BTW, Max ( taxrates 2::rate ) will return the highest rate from the related records and Min ( TaxRates 2::rate ) will return the smallest tax rate. Wink