Making the County List Dynamic

As a follow-up to my "New Application Journey" article, I wanted to talk about an interesting requirement that I encountered while gathering information for my mother-in-law's application.

Recap

As a TL;DR ("too long; didn't read") to the original article, I wasn't happy with the application my mother-in-law was using for her very small business in the southeast section of the United States. So, I used her business needs to create a new application from scratch using Angular, mySQL, and the AWS environment. As I started building out the main form for data entry, I noticed an interesting requirement around the county tax rates.

The County Table

As one might imagine, the homes being sold and tracked by the application reside in a particular county within the state of Florida. As a result, county taxes are applied based upon a percentage calculation from the total sales price. Since each county is their own municipality, they have different tax rates.

This was a pretty simple design. I would build a County table that listed each county and included the tax rate for that county.

When I asked my mother-in-law for a listing of the current taxes, she pointed me to a form that provided information I was not expecting. There were effective dates for the tax rates. As an example, Dade County might have a tax rate of 1% from 2015 through 2017, but bump up to 1.5% starting on January 1 of 2018.

My county table started to get a little more complicated.

Instead of simply maintaining a table with a county name and county tax rate, I created the table with the following attributes:

private Integer id;
private String name;
private BigDecimal rate;
private Timestamp effectiveDate;

Using the example above, I would now have two records for Dade County, as shown below:

{
  "id" : 1,
  "name" : "Dade",
  "rate" : 0.01,
  "effectiveDate" : "2015-01-01"
},
{
  "id" : 2,
  "name" : "Dade",
  "rate" : 0.015,
  "effectiveDate" : "2018-01-01"
}

On the Property record, I needed to build the pick-list for the county using a query similar to what is displayed below:

SELECT c.* FROM county c 
INNER JOIN (SELECT name, MAX(effective_date) maxDate FROM county 
WHERE effective_date <= ?1 GROUP BY name) b ON c.name = b.name 
  AND c.effective_date = b.maxDate  
ORDER BY c.name";

Within the Angular client, the Closing Date field calls the  refreshCountyList() method when the ngbDatePicker value changes:

<input id="closingDate" class="form-control text-right" placeholder="mm/dd/yyyy" 
      formControlName="closingDate" #closingDate
       name="closingDate" ngbDatepicker #d1="ngbDatepicker" readonly
       (dateSelect)="refreshCountyList()">

Within the Angular component for the property-edit functionality, this calls the following function:

refreshCountyList() {
  let thisDate = new Date();
  if (this.propertyForm.value.closingDate) {
    thisDate = new Date(this.propertyForm.value.closingDate.year,
      this.propertyForm.value.closingDate.month - 1, this.propertyForm.value.closingDate.day);
  }

  const currentCounty = this.property.county;

  this.countyService.getActiveCounties(thisDate.getTime())
    .subscribe(data => {
      this.counties = data;

      const newCounty: County = this.getNewCounty(currentCounty);

      if (newCounty) {
        this.property.county = newCounty;
      } else {
        this.property.county = null;
        this._error.next('Please select a County from the drop-down list.');
      }

      this.computeFinancialData();
      this.propertyForm.patchValue({property: this.property});
    });
}

The process uses the date value to get a list of counties that fall into the appropriate range. Then determines if the currently selected county still exists in the list of counties. From there, the form is updated and the computeFinancialData() method is called to recompute the tax amounts and other financial information stored on the form.

As a result, the correct tax rate is used for the given county based upon the closing date of the property sale.

Looking Ahead

This article is a continuation of a multi-part series that I am putting together regarding my new application journey to providing a better application experience for my mother-in-law. Below is a list of the current and planned articles, if you are interested in reading more:

• New Application Journey

• Okta, a Nice Solution — Even for Small Apps

• FormBuilder in Angular 6

• The Challenge of the Commission Report

• Making the County List Dynamic (this article)

• New Version of the Commission Report

• What I Learned After Initial Deployment

Have a really great day!