Create an indexed list from dataset in Delphi

In apps development, there are many times when you have a large dataset filled with records, and you want to create a list of the items used in the records, without repeating. Like, for example, making a list of countries visited by the people in your dataset.

In this tutorial, we will show a good way to do that.

If you have a dataset like the one below :

Customer IDCity
002Rio de Janeiro
006Rio de Janeiro
009Rio de Janeiro
Yes, they are the codenames of Delphi 10.x versions.

And, you need to have a list countaining the items distributed on all records, to be used on a List or a Combobox control for filtering or statistical purposes. For our example, this list will be like this :

  • Berlin
  • Rio de Janeiro
  • Seattle
  • Sydney
  • Tokyo

To do this, you have to follow this steps in your code :

  • Load the dataset control with data.
  • Sort the dataset, using its “IndexFieldNames” property, and select only the field which the content correspond to your future list.
  • Locate the first record in the dataset, using the method “First”.
  • Now, declare three variables : a StringList, an integer and a string.
  • Initialize the string variable.
  • Set a loop, for each record, do a conditional check, if the record value <> the string variable value then :
    • Add the record value to the StringList and,
    • Store the record value in that string variable.
  • Close the condition, this will mean (else : do nothing).
  • Now, after the condition, put the last action of the loop : move the dataset to the next record.
  • After the end of the loop, your list has been created. You can export it (write it in a Memo control or save it to an external file) or use it on the fly (to populate a ListBox or a Combobox control).
  • The last things to do : release the memory of StringList with the “Free” method, and disable the dataset’s sorting (IndexFieldNames := ”;).

By following the above steps, you will get a code snippet like this, you can run it on the FormCreate or in the dataset events or at any other action.

procedure ...
i : integer;
Str : string;
SList : TStringList;

DataSet1.IndexFieldNames := 'City';

S := '';
SList := TStringList.Create;

while not DataSet1.EOF do
   if S <> DataSet1.FieldByName('City').AsWideString then
      S := DataSet1.FieldByName('City').AsWideString;

//Save the list to an external file

//Populate a Combobox with that list
for i := 0 to (SList.Count -1) do ComboBox1.Items.Add(SList.Strings[i]);

DataSet1.IndexFieldNames := '';

Leave a Reply