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 ID	City
001	Seattle
002	Rio de Janeiro
003	Sydney
004	Berlin
005	Tokyo
006	Rio de Janeiro
007	Tokyo
008	Sydney
009	Rio de Janeiro

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 ...
var
i : integer;
Str : string;
SList : TStringList;

begin
DataSet1.LoadFromFile('Data1.xml'));
DataSet1.IndexFieldNames := 'City';
DataSet1.First;

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

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

//Save the list to an external file
SList.SaveToFile('City_list.txt');

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

SList.Free;
DataSet1.IndexFieldNames := '';
end;