Development Design pattern for Best Fit Filtering

In our Org we have quite a few places were data is auto populated based on other data selected. This however comes with allot of rules. When I started, we had allot of issues where sometimes the incorrect data is selected. This required a rewrite of the filtering to find the correct data but often caused the “fix” to break another set of data combinations. One day I decided that this needs to be re-dinged in a manner that will work for all scenarios based on the general accepted rule of “what data fits best”. Well that sounds a bit AI to me… so I sat down to figure out what that means (for my Org at least) …. And below is what I came up with:

Operating a car rental company, imagine you have a table containing a list of cars and their properties/ attributes like the one below. We do not have all the information for all the cars, only some are populated. The table has “Reg” as its primary key and is sorted in alphabetical order on the Primary Key.

Applying the below customer requirements for a car he wants to rent, what record will be returned to the user?

Any filter applied to a field should considered as exact or Blank (the ones we don’t know). I.E.  If we apply a filter VW to the Make field, the system will filter on VW or Blank. The “VW” filter will return 6 records and filter out 1:

The next filter to be applied on top of the returned result is the Engine size. The system should filter on 1.8 or Blank. The “1.8” filter will return 6 records and filter out 1:

If we continue in this manner, we will end up with 3 records left that is valid after all filters are applied:

Now the question is which Car of those that is left is the best we can offer to our Client? Again, for humans this is easy, the one with the most attributes that match is the “best fit” one. I.e. LMP019GP, a BLUE VW with a Petrol engine.

Doing this in code is a bit trickier…. We would need to count the attributes and the record with the most matching attributes is the best. There are various ways of doing this with buffer tables, counters etc, but I choose to add an additional field to the tables where this pattern needs to be applied to. So, let’s have a look at the code:

I first add a field of type INT to the Cars Table and add it is a secondary key:

Then I write the Code to filter and select the best possible fit:

VARIABLES:

CODE:

LOCAL FilterCars() : Code[20]
WITH Cars DO BEGIN
  RESET;
  SETFILTER(Make,'%1|%2','VW','');
  SETFILTER(Engine,'%1|%2','1.8','');
  SETFILTER(Transmission,'%1|%2','M','');
  SETFILTER(Colour,'%1|%2','BLUE','');
  SETFILTER(Aircon,'%1|%2','Yes','');
  SETFILTER(Fuel,'%1|%2','PETROL','');
  IF FINDSET THEN
  REPEAT
    ClearCount := 0;
    IF Make = '' THEN
      ClearCount := ClearCount + 1;
    IF Engine = '' THEN
      ClearCount := ClearCount + 1;
    IF Transmission = '' THEN
      ClearCount := ClearCount + 1;
    IF Colour = '' THEN
      ClearCount := ClearCount + 1;
    IF Aircon = '' THEN
      ClearCount := ClearCount + 1;
    IF Fuel = '' THEN
      ClearCount := ClearCount + 1;

    TempCars.INIT;
    TempCars.TRANSFERFIELDS(Cars);
    TempCars.Orderkey := ClearCount;
    TempCars.INSERT;
  UNTIL NEXT = 0;
END;

TempCars.SETCURRENTKEY(Orderkey);
IF TempCars.FINDFIRST THEN
  Cars.GET(TempCars.Reg);

EXIT(Cars.Reg);

The ClearCount Variable is used to count the number of Attributes that is blank for each record. This gets added to the Orderkey field in a temp table of the same cars table. Once all the records in the filter set is injected into the temp table with a Orderkey value, this table will contain all the records in order of best to worst fit based on the count of attributes that is blank (the less blank values the better the fit). The best record is then then returned.

And there you have it. As always, your comments are more than welcome. Let me know what you think. Send me improvements or let me know if this was useful to you…

Until next time

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s