[SOLVED] Defining Table Structures

[1HuntnMan]

I've been working on an application and now getting into the defining currency type fields. I've discovered something using My Dbf Studio and Dbf Table Manager.  I'm using Dbase 7 and defining these currency type fields as type Float with a length of 7. Using Dbf Table Manager you can define a currency field as Float, length 7 with 2 decimals.  If I open My Dbf Studio, it shows the Float field with a length of 2, no way to define the length 7 with decimals 2.

But, programming how would I do this correctly?  This is the code I'm using to build the database tables for those tables containing currency fields.

Code: Pascal  [Select][+][-]

1.       Add('ESTPRICE', ftFloat, 7, True);  //-> Can't define the decimals/precision as 2 ????
2.       Add('FIXEDPRICE', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????
3.       Add('COMMISSION', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????
4.  

After I run withing the application to build the database.  Every field that I define as type Float, 7 characters displays in My Dbf Studio as Integer with no length.  In Dbf Table Manager the currency fields as above are displayed as Large Integer with a length of 7 ?????

Do I even need to be concerned about this.  In the Database Setup form that I have all the TDbf components and coded for the user to create the complete database with all tables I've noticed that for example SalesOrders.dbf, I can click the SalesOrders COMMISSION field for example and the precision for the field is 2 with length of blank but can change it to 7.

[TRon]

Use one of the add overrides ?

[1HuntnMan]

TRon, I'm not understanding where to add this example you gave me in the right place in my UNIT,  see below:

Code: Pascal  [Select][+][-]

1. uses
2.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ExtCtrls, Db, dbf,
3.   StdCtrls, Buttons, DBGrids, ComCtrls;
4.  
5. type
6.  
7.   { TFrmPMSSetup }
8.  
9.   TFrmPMSSetup = class(TForm)
10.     DSInvoices: TDataSource;
11.     DbfInvoices: TDbf;
12.     DSPhotos: TDataSource;
13.     DbfPhotos: TDbf;
14.     DSPhotographers: TDataSource;
15.     DbfPhotographers: TDbf;
16.     DSSalesOrders: TDataSource;
17.     DbfSalesOrders: TDbf;
18.     DSProposals: TDataSource;
19.     DbfProposals: TDbf;
20.     DSReferences: TDataSource;
21.     DbfReferences: TDbf;
22.     DSContacts: TDataSource;
23.     DbfContacts: TDbf;
24.     DSClients: TDataSource;
25.     DbfClients: TDbf;
26.     DSAppointments: TDataSource;
27.     DbfAppointments: TDbf;
28.     LblWarning2: TLabel;
29.     LblPrgrsBar: TLabel;
30.     LblStartNotation: TLabel;
31.     LblWarning: TLabel;
32.     LblWarning1: TLabel;
33.     LblWarning3: TLabel;
34.     PnlPMSSetupTop: TPanel;
35.     PrgrsBarTableRebld: TProgressBar;
36.     SpdBtnPMSDataRebuild: TSpeedButton;
37.     SpdBtnSetUpClose: TSpeedButton;
38.     TimerRebld: TTimer;
39.     procedure FormCreate(Sender: TObject);
40.     procedure SpdBtnPMSDataRebuildClick(Sender: TObject);
41.     procedure SpdBtnSetUpCloseClick(Sender: TObject);
42.     procedure TimerRebldTimer(Sender: TObject);
43.  
44.   private
45.  
46.   public
47.     function TFieldDefs.Add(const AName: string; ADataType: TFieldType;
48.     ASize: Integer; APrecision: Integer; ARequired: Boolean):TFieldDef;
49.     overload;  { Error here beginning of function: Syntax error, ":"
50.                  expected but "." found }
51.  
52.   end;
53.  
54. var
55.   FrmPMSSetup: TFrmPMSSetup;
56.  
57. implementation
58.  
59. {$R *.lfm}
60.  
61. { TFrmPMSSetup }
62.  
63. procedure TFrmPMSSetup.SpdBtnPMSDataRebuildClick(Sender: TObject);
64. { Create the Appointments, Clients, Contacts, Invoices, Photographers,
65.   Photos, Proposals, References and SalesOrders tables plus associated
66.   indexes for each table. }
67.  
68. var
69.   Appointments: TDbf;
70.   Clients: TDbf;
71.   Contacts: TDbf;
72.   Photographers: TDbf;
73.   Photos: TDbf;
74.   Proposals: TDbf;
75.   References: TDbf;
76.   SalesOrders: TDbf;
77.   Invoices: TDbf;
78.  
79. begin
80.   PrgrsBarTableRebld.Step:= 10;
81.   PrgrsBarTableRebld.Position:= 10;
82.   ForceDirectories('PMS Data'); //-> Note: PMS Database Directory = PMS Data
83.   //-> Build Appointments.dbf and indices
84.   if FileExists('Appointments.dbf') then
85.     DeleteFile('Appointments.dbf');
86.   Appointments := TDbf.Create(nil);     // APPOINTMENTS TABLE
87.   try
88.     //-> Use the relative path to the "PMS Data" directory
89.     Appointments.FilePath := 'PMS Data' + DirectorySeparator;
90.     //-> Use Visual dBase VII compatible tables for Lazarus (Table Level 7)
91.     Appointments.TableLevel:= 7;
92.     Appointments.Exclusive:= True;
93.     Appointments.TableName:= 'Appointments.dbf';
94.     with Appointments.FieldDefs do begin //-> 17 fields
95.       PrgrsBarTableRebld.Step:= 20;
96.       PrgrsBarTableRebld.Position:= 20;
97.      ...
98.  

[TRon]

I am afraid I do not fully understand the question.

Instead of:

Code: Pascal  [Select][+][-]

1. Add('FIXEDPRICE', ftFloat, 7, True);
2.  

you can use f.e.:

Code: Pascal  [Select][+][-]

1. Add('FIXEDPRICE', ftFloat, 7, True, 2, true, true, fieldno, codepage );
2.  

Alternatively:

Code: Pascal  [Select][+][-]

1. var
2. ThisFieldDef : TFieldDef;
3. ...
4. ThisFieldDef := Add('FIXEDPRICE', ftFloat, 7, True);
5. ThisFieldDef.whatever_property_needs_to_be_changed := somevalue;
6. ..
7.  

[CharlyTango]

dBase is a format I am very familiar with. From dBase II to dBase 3+, Foxpro to Clipper with different format libraries and component indices, I have worked with it for a long time.
However, it is an obsolete format for me and I recommend using SQLite instead, for example.
The advantages are obvious. You have a modern data format (available free of charge on all operating systems) with good possibilities for small to large applications and, by using SQL, you also have the option of using other databases later.

It could be worth a try

[Zvoni]

you can use f.e.:

Code: Pascal  [Select][+][-]

1. Add('FIXEDPRICE', ftFloat, 7, True, 2, true, true, fieldno, codepage );
2.  

Errrr... No?

public function TFieldDefs.Add(
  const AName: string;
  ADataType: TFieldType;
  ASize: Integer; 
  APrecision: Integer;
  ARequired: Boolean;
  AReadOnly: Boolean;
  AFieldNo: Integer;
  ACodePage: TSystemCodePage
):TFieldDef; overload;

-->

Code: Pascal  [Select][+][-]

1. Add('FIXEDPRICE', ftFloat, 7, 2, true, False, fieldno, codepage );
2.  

[TRon]

Craps, did I miscounted Zvoni ?

Ah, well. It is the gist of the answer: use an overload (that allows to set the precision)

[Zvoni]

Craps, did I miscounted Zvoni ?

Looks like it 
Happens to the best of us.... 

Ah, well. It is the gist of the answer: use an overload (that allows to set the precision)

Yepp.

Though i agree with CharlyTango: Move to SQLite and be done with it

On a sidenote: Why ever needing a precision for a Float in the DB itself?
Wouldn't that be a task for DISPLAYING?

[TRon]

Though i agree with CharlyTango: Move to SQLite and be done with it

Yes, +1 form me as well. Much more flexible, less restrictions and more importantly no known bugs (knocks wood  ).

On a sidenote: Why ever needing a precision for a Float in the DB itself?
Wouldn't that be a task for DISPLAYING?

Would have been my thought as well. Dunno tbh, I haven't been using dbf for over 20 years.

[Zvoni]

As to the initial question:
If there is a need to have a precision of 2 (in this case) in the DB itself, i wouldn't go with Float but with (Big) Integer.
Just multiply the Value with 100 BEFORE sending it to the Database.
Divide the Value by 100 AFTER pulling it from the DB, but BEFORE displaying it

[1HuntnMan]

Here's an example of the way I'm defining the tables in the Database Schema for this application, attached.  I agree, need to rewrite the whole app using SQLite but started this project last year as a fun thing to do for a fellow photographer.  I started programming back in the 1980's-early 90's with Turbo Pascal. Then a company I worked for wanted us to use dBase, can't remember whether it was dBase III+ and then converted everything to Clipper.  Fast forward to 2023-24 and relearning how to program.  Too many years for a 72 year old dude.

Look at the attached, esp. the Invoices, Proposals and SaleOrders tables creation.  I think I'm making a mountain out of a mole-hill.  I don't think I need to worry about defining the 4-6 fields in 3 tables as float 7 with precision/decimals of 2.  In the procedure Invoices for example in the FormShow Procedure I have one field, AMMTDUE that is a currency field defined as so: 

Code: Pascal  [Select][+][-]

1.   DbfInvces.IndexName:= 'JOBNAME';
2.   DbfInvces.Open;
3.   TFloatField(DbfInvces.FieldByName('AMTDUE')).Currency:= True;
4.  

So, do ya'll see any reason for me to change the code to take care of the 2 decimals???

[Handoko]

But, programming how would I do this correctly?  This is the code I'm using to build the database tables for those tables containing currency fields.

Code: Pascal  [Select][+][-]

1.       Add('ESTPRICE', ftFloat, 7, True);  //-> Can't define the decimals/precision as 2 ????
2.       Add('FIXEDPRICE', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????
3.       Add('COMMISSION', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????
4.  

Don't use Add, instead setting the fields using this way:
https://forum.lazarus.freepascal.org/index.php/topic,38310.msg259743.html#msg259743
https://forum.lazarus.freepascal.org/index.php/topic,57472.msg427404.html#msg427404

So you can set the field size to something like this:

// add a float field
NewFieldDef := NewFieldDefs. AddFieldDef;
NewFieldDef. FieldName := ’ NEW_FLOAT ’;
NewFieldDef. FieldType := ftFloat;
NewFieldDef. Size := 10;
NewFieldDef. Precision := 3;

Source: tdbf.pdf

You can find the documentation tdbf.pdf from the Internet. Anyone who wants to use TDbf should have a copy of it.

[1HuntnMan]

Thanks Handoko, I'm starting all over on creating my Database.

[1HuntnMan]

Where do you put a PUBLIC FUNCTION ... in a unit? From the examples above?

[Handoko]

If I write database programs, I usually will put all the database related things in a single unit file, I name it data.pas. All the forms do not access the database directly, but they have to call the functions/procedure in data.pas.

The data.pas may have public procedures like these:
- Procedure PrepareDatabase;
- Procedure ValidateProduct(const ID, Name: string; Price: Real; out ErrMessage: String);
- Procedure SaveProduct(const ID, Name: string; Price: Real; out Succeed: Boolean);
- Procedure DeleteProduct(const ID: string; out Succeed: Boolean);
- Procedure ValidateUser(const ID, Name: string; Price: Real; out ErrMessage: String);
- Procedure SaveUser(const ID, Name: string; Price: Real; out Succeed: Boolean);
- Procedure DeleteUser(const ID: string; out Succeed: Boolean);

The data.pas is responsible for creating, saving, loading, deleting and verifying new data. So the forms are only for collecting user's inputs/actions and displaying error messages.