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:
LOCAL FilterCars() : Code 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