dbase and indices [SOLVED] lazarus / 16-Databases ---------------------------------------------------------------------- timcs · 2012-09-10 10:07 I am trying to add an index with three fields in a dbase database. I have tried the following but this errors with an operator/function missing : Quote dbf1.Active:=false;    dbf1.Exclusive:=true;    dbf1.Open;    dbf1.AddIndex('custdate','field1.field2.field3' [ixCaseInsensitive]);    dbf1.Close;    dbf1.Exclusive:=false;    dbf1.Active:=true;      I have also tried other separators just as spaces, semi colons, colons and the plus sign with no luck. How do I add more that one field to an index in a dbase database? ---------------------------------------------------------------------- howardpc · 2012-09-10 12:56 The second parameter to AddIndex() must be a string expression. So you have to consider the field type of each field you want to use in the index. For string fields you can just concatenate their values using '+', but for numeric fields you have to use the STR(numField) conversion function in the field expression before concatenation. Date fields require the DTOS(dateField) conversion function. There is a 100 character limit on the expression that combines the fields. ---------------------------------------------------------------------- timcs · 2012-09-10 13:06 Quote from: howardpc on September 10, 2012, 02:56:02 pmThe second parameter to AddIndex() must be a string expression. So you have to consider the field type of each field you want to use in the index. For string fields you can just concatenate their values using '+', but for numeric fields you have to use the STR(numField) conversion function in the field expression before concatenation. Date fields require the DTOS(dateField) conversion function. There is a 100 character limit on the expression that combines the fields. Thanks for this howardpc I will give this a try, looking at the code I supplied it was actually this: Code: [Select]dbf1.Active:=false;    dbf1.Exclusive:=true;    dbf1.Open; [b]   dbf1.AddIndex('custdate','field1,field2,field3', [ixCaseInsensitive]);[/b]    dbf1.Close;    dbf1.Exclusive:=false;    dbf1.Active:=true;      I didn't put the correct separators in the first post and missed the , after listing fields ---------------------------------------------------------------------- timcs · 2012-09-10 13:12 Code: [Select]   dbf1.Active:=false;    dbf1.Exclusive:=true;    dbf1.Open;    dbf1.AddIndex('custdate','field1, '+' field2,'+' field3',[ixCaseInsensitive]);    dbf1.Close;    dbf1.Exclusive:=false;    dbf1.Active:=true;    Tried it as above and also tried the delimiter with spaces, semi colons, is what I have done what you are referring to? I still get the error operator/function is missing with the comma, if I tried spaces it repeats the first field twice in the error and if I try any of the other delimiters it just asks to terminate the program. I now know what the problem is after reading you reply again however how to I specify the date fields when all I am doing to is entering the reference as the field name dateon ? ---------------------------------------------------------------------- Knipfty · 2012-09-10 13:47 timcs, I believe that the syntax you need is Code: [Select]dbf1.AddIndex('custdate',field1+field2+field3,[ixCaseIns ensitive]); That is assuming that all 3 fields are of String type.  IF not, then you need to convert each one to a string.  For example: Code: [Select]dbf1.AddIndex('custdate',field1+inttostr(field2)+field3, [ixCaseInsensitive]); Knipfty ---------------------------------------------------------------------- timcs · 2012-09-10 14:02 Quote from: Knipfty on September 10, 2012, 03:47:19 pmtimcs, I believe that the syntax you need is Code: [Select]dbf1.AddIndex('custdate',field1+field2+field3,[ixCaseIns ensitive]); That is assuming that all 3 fields are of String type.  IF not, then you need to convert each one to a string.  For example: Code: [Select]dbf1.AddIndex('custdate',field1+inttostr(field2)+field3, [ixCaseInsensitive]); Knipfty Hi Knipfty   Do I specify the fields as per their names or am I having to do dbf1.field....... ? ---------------------------------------------------------------------- Knipfty · 2012-09-10 14:31 I don't a a dbf environment to play with here at work (they actually expct that I work while I'm at work ) so I'm not sure. DId you look at this page? http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial ---------------------------------------------------------------------- timcs · 2012-09-10 14:57 Quote from: Knipfty on September 10, 2012, 04:31:59 pmI don't a a dbf environment to play with here at work (they actually expct that I work while I'm at work ) so I'm not sure. DId you look at this page? http://wiki.lazarus.freepascal.org/Lazarus_Tdbf_Tutorial First place I looked at, the field names mentioned here for creating indices are no good for an example that I need as they are just for single fields not multiple. In the examples it states to put the fields surrounded by single quotes this is where I started using them when I have been doing it. What I cannot seem to get to work is that if I state a date field e.g. named as dateon then if I do datetostr(dateon) it errors with dateon is not a valid identifier . I then have tried to include dbf1.fieldbyname('dateon') method but I am getting no where on this either. ---------------------------------------------------------------------- Knipfty · 2012-09-10 15:44 Hi timcs, I found this on Koders Code:  http://www.koders.com/delphi/fid6041B675 F67CF37C3E155BD0F0D2A8B51406CD18.aspx?s=dbf+AddIndex#L8 Code: [Select]TimeLogDbf.AddIndex('PCNO.NDX', 'PCNO+TAG', [ixExpression]); Hopefully this gives you a good example Knipfty ---------------------------------------------------------------------- timcs · 2012-09-10 16:34 Quote from: Knipfty on September 10, 2012, 05:44:45 pmHi timcs, I found this on Koders Code:  http://www.koders.com/delphi/fid6041B675 F67CF37C3E155BD0F0D2A8B51406CD18.aspx?s=dbf+AddIndex#L8 Code: [Select]TimeLogDbf.AddIndex('PCNO.NDX', 'PCNO+TAG', [ixExpression]); Hopefully this gives you a good example Knipfty Knipfty , I did try this at the start using the + sign but how do I cope with date fields ? or is that what the [ixExpression] part is for? Just tried it and it errors on the + symbols ---------------------------------------------------------------------- Knipfty · 2012-09-10 16:50 At this point I do not know.  I played with this years ago in Delphi.  Please try the [ixExpression]. Can you get multiple fields to work that isn't a date field?  IN the example I provided, it is interesting that all of the indexes seem to only use a TStringField.  And the only use of  [ixExpression] is when there are two fields being indexed. ---------------------------------------------------------------------- timcs · 2012-09-10 19:47 Quote from: Knipfty on September 10, 2012, 06:50:10 pmAt this point I do not know.  I played with this years ago in Delphi.  Please try the [ixExpression]. Can you get multiple fields to work that isn't a date field?  IN the example I provided, it is interesting that all of the indexes seem to only use a TStringField.  And the only use of  [ixExpression] is when there are two fields being indexed. Sorry Knipfty but I did use the [ixExpression]  at the same time but still got the error on the + symbol stating that this is not a valid field ---------------------------------------------------------------------- Knipfty · 2012-09-10 19:50 What about only using fields of TStringField type? ---------------------------------------------------------------------- timcs · 2012-09-10 20:22 Quote from: Knipfty on September 10, 2012, 09:50:06 pmWhat about only using fields of TStringField type? I did try dbf1.fieldbyname('dateon').asString if this is what mean you mean. ---------------------------------------------------------------------- Knipfty · 2012-09-10 20:26 no.  I want you to index on two fields that are string types without any type casting using the plus sign.  It looks like you have two issues right now. 1. building an index with two or more fields 2. building an index using a date field Yes, you want them all together, but let's at least isolate the two issues.  If you can build the index with two string fields, then we are hald way home. ---------------------------------------------------------------------- timcs · 2012-09-10 20:31 Quote from: Knipfty on September 10, 2012, 10:26:01 pmno.  I want you to index on two fields that are string types without any type casting using the plus sign.  It looks like you have two issues right now. 1. building an index with two or more fields 2. building an index using a date field Yes, you want them all together, but let's at least isolate the two issues.  If you can build the index with two string fields, then we are hald way home. In that case I can test two fields in the database that are string and set these with the plus sign and then I could try and create a further index just with the date field and see what happens ---------------------------------------------------------------------- Knipfty · 2012-09-10 20:45 Yes!  Exactly! ---------------------------------------------------------------------- timcs · 2012-09-10 20:58 Quote from: Knipfty on September 10, 2012, 10:45:06 pmYes!  Exactly! Okay, I have been able to create an index using two string fields and the plus symbol, I have then tried to create a separate index using the date field and as expected it errors stating the field must be string or float. So the question is, how to create an index with a date field ! ---------------------------------------------------------------------- TurboRascal · 2012-09-11 08:37 Tdbf is actually well documented: http://sourceforge.net/projects/tdbf /files/TDbf%20documentation/TDbf%20documentation%201.1/ There is also a nice demo: http://sourceforge.net/projects/tdbf/files/TDbf%20Demo%20App/ So far I've been able to find everything I needed there. If something happens to be unclear, there is always the source at hand. ---------------------------------------------------------------------- timcs · 2012-09-11 09:29 Quote from: TurboRascal on September 11, 2012, 10:37:55 amTdbf is actually well documented: http://sourceforge.net/projects/tdbf/files/T Dbf%20documentation/TDbf%20documentation%201.1/ There is also a nice demo: http://sourceforge.net/projects/tdbf/files/TDbf%20Demo%20App/ So far I've been able to find everything I needed there. If something happens to be unclear, there is always the source at hand. Thanks for this TurboPascal - I will read these and hopefully get this  working! ---------------------------------------------------------------------- timcs · 2012-09-11 11:46 I think one of the first answers here was the answer but I didn't use the DTOS function correctly. so I can do this: Code: [Select]   dbf1.Active:=false;    dbf1.Exclusive:=true;    dbf1.Open;    dbf1.AddIndex('custdate','field1+'+'DTOS(datefield)',[ixCaseInsensi tive]);    dbf1.Close;    dbf1.Exclusive:=false;    dbf1.Active:=true;    This appears to work now, thanks for everyone's help here