[SOLVED] Defining Table Structures lazarus / 16-Databases ---------------------------------------------------------------------- 1HuntnMan · 2024-10-27 21:23 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i       Add('ESTPRICE', ftFloat, 7, True);  //-> Can't define the decimals/precision as 2 ????      Add('FIXEDPRICE', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????      Add('COMMISSION', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????  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 · 2024-10-27 22:01 Use one of the add overrides ? ---------------------------------------------------------------------- 1HuntnMan · 2024-10-28 20:18 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, ExtCtrls, Db, dbf,  StdCtrls, Buttons, DBGrids, ComCtrls; type   { TFrmPMSSetup }   TFrmPMSSetup = class(TForm)    DSInvoices: TDataSource;    DbfInvoices: TDbf;    DSPhotos: TDataSource;    DbfPhotos: TDbf;    DSPhotographers: TDataSource;    DbfPhotographers: TDbf;    DSSalesOrders: TDataSource;    DbfSalesOrders: TDbf;    DSProposals: TDataSource;    DbfProposals: TDbf;    DSReferences: TDataSource;    DbfReferences: TDbf;    DSContacts: TDataSource;    DbfContacts: TDbf;    DSClients: TDataSource;    DbfClients: TDbf;    DSAppointments: TDataSource;    DbfAppointments: TDbf;    LblWarning2: TLabel;    LblPrgrsBar: TLabel;    LblStartNotation: TLabel;    LblWarning: TLabel;    LblWarning1: TLabel;    LblWarning3: TLabel;  PnlPMSSetupTop: TPanel;    PrgrsBarTableRebld: TProgressBar;    SpdBtnPMSDataRebuild: TSpeedButton;    SpdBtnSetUpClose: TSpeedButton;    TimerRebld: TTimer;    procedure FormCreate(Sender: TObject);    procedure SpdBtnPMSDataRebuildClick(Sender: TObject);    procedure SpdBtnSetUpCloseClick(Sender: TObject);    procedure TimerRebldTimer(Sender: TObject);   private   public    function TFieldDefs.Add(const AName: string; ADataType: TFieldType;    ASize: Integer; APrecision: Integer; ARequired: Boolean):TFieldDef;    overload;  { Error here beginning of function: Syntax error, ":"               expected but "." found }   end; var  FrmPMSSetup: TFrmPMSSetup; implementation {$R *.lfm} { TFrmPMSSetup } procedure TFrmPMSSetup.SpdBtnPMSDataRebuildClick(Sender: TObject);{ Create the Appointments, Clients, Contacts, Invoices, Photographers,  Photos, Proposals, References and SalesOrders tables plus associated  indexes for each table. } var  Appointments: TDbf;  Clients: TDbf;  Contacts: TDbf;  Photographers: TDbf;  Photos: TDbf;  Proposals: TDbf;  References: TDbf;  SalesOrders: TDbf;  Invoices: TDbf; begin  PrgrsBarTableRebld.Step:= 10;  PrgrsBarTableRebld.Position:= 10;  ForceDirectories('PMS Data'); //-> Note: PMS Database Directory = PMS Data  //-> Build Appointments.dbf and indices  if FileExists('Appointments.dbf') then    DeleteFile('Appointments.dbf');  Appointments := TDbf.Create(nil);   // APPOINTMENTS TABLE  try    //-> Use the relative path to the "PMS Data" directory    Appointments.FilePath := 'PMS Data' + DirectorySeparator;    //-> Use Visual dBase VII compatible tables for Lazarus (Table Level 7)    Appointments.TableLevel:= 7;    Appointments.Exclusive:= True;    Appointments.TableName:= 'Appointments.dbf';    with Appointments.FieldDefs do begin //-> 17 fields      PrgrsBarTableRebld.Step:= 20;      PrgrsBarTableRebld.Position:= 20;     ... ---------------------------------------------------------------------- TRon · 2024-10-28 20:36 I am afraid I do not fully understand the question. Instead of: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Add('FIXEDPRICE', ftFloat, 7, True); you can use f.e.: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Add('FIXEDPRICE', ftFloat, 7, True, 2, true, true, fieldno, codepage );  Alternatively: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i varThisFieldDef : TFieldDef;...ThisFieldDef := Add('FIXEDPRICE', ftFloat, 7, True);ThisFieldDef.whatever_property_needs_to_be_changed := somevalue;.. ---------------------------------------------------------------------- CharlyTango · 2024-10-29 09:39 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 · 2024-10-29 10:33 Quote from: TRon on October 28, 2024, 09:36:30 pmyou can use f.e.: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Add('FIXEDPRICE', ftFloat, 7, True, 2, true, true, fieldno, codepage ); Errrr... No? Quote 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i Add('FIXEDPRICE', ftFloat, 7, 2, true, False, fieldno, codepage ); ---------------------------------------------------------------------- TRon · 2024-10-29 10:38 Craps, did I miscounted Zvoni ? Ah, well. It is the gist of the answer: use an overload (that allows to set the precision) ---------------------------------------------------------------------- Zvoni · 2024-10-29 10:47 Quote from: TRon on October 29, 2024, 11:38:56 amCraps, did I miscounted Zvoni ?Looks like it  Happens to the best of us....  QuoteAh, 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 · 2024-10-29 10:55 Quote from: Zvoni on October 29, 2024, 11:47:46 amThough 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  ). QuoteOn 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 · 2024-10-29 10:58 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 · 2024-10-29 15:18 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   DbfInvces.IndexName:= 'JOBNAME';  DbfInvces.Open;  TFloatField(DbfInvces.FieldByName('AMTDUE')).Currency:= True;  So, do ya'll see any reason for me to change the code to take care of the 2 decimals??? ---------------------------------------------------------------------- Handoko · 2024-10-29 15:51 Quote from: 1HuntnMan on October 27, 2024, 10:23:11 pmBut, 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i       Add('ESTPRICE', ftFloat, 7, True);  //-> Can't define the decimals/precision as 2 ????      Add('FIXEDPRICE', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????      Add('COMMISSION', ftFloat, 7, True);   //-> Can't define the decimals/precision as 2 ????  Don't use Add, instead setting the fields using this way: https://forum.lazarus.freepascal.org/index.php/topic,38310.msg259743.h tml#msg259743 https://forum.lazarus.freepascal.org/index.php/topic,57472.msg427404.h tml#msg427404 So you can set the field size to something like this: Quote// 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 · 2024-10-29 17:27 Thanks Handoko, I'm starting all over on creating my Database. ---------------------------------------------------------------------- 1HuntnMan · 2024-11-01 17:08 Where do you put a PUBLIC FUNCTION ... in a unit? From the examples above? ---------------------------------------------------------------------- Handoko · 2024-11-01 17:35 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.