Finance App Tables

[pat03uk]

I am new to sqlite3 and have a problem with a small finance app which I'm hoping I can get some help with.

I have three tables (simplified):
   payee (id, name).      eg amazon, bmw etc
   category(id, name)    eg clothing, medicine etc
   transaction(id, payee_id, category_id, name, amount)

If a new transaction is entered (by entering text values for payee and category) I need to:
   a) check if payee (name) is present, if not enter it into the payee table,  then get back the payee id and enter that into the transaction record.
   b) do the same same for the category

Is this a sensible approach?. It seems i would need to do an insert or ignore query to enter name into the payee table, followed by a select query to get back the id to enter into the transaction record - or is there a one step way or simpler way.

[Sieben]

The usual way is rather to have the user enter a new payee or category first before it can be used with the main table (preferably with a lookup combo). That way you can also provide better checks for duplicates, otherwise you might end up with eg a bunch of different Amazon 'brands' like Amazon Co, Amazon Limited or even simple typos entering the payee table. BTW this has nothing to do with the brand of database you're using.

[pat03uk]

The problem is that input can come from a gif file (a text file of accounts transactions etc) exported from another finance app as well as user input.

[pat03uk]

I have tried (where p is the payee name)

Insert or Ignore into payee(name) values('+quotedStr(p)+')
which won't return a value, followed by
Select id From payee where name = ' + quotedStr(p)
which will return a value for id

this seems long winded

I have also tried:
Insert or Ignore into payee(name) values('+quotedStr(p)+') RETURNING id as payeeID
and even:
UPDATE payee set name = '+quotedStr(p)+' WHERE name = '+quotedStr(p)+' RETURNING id as payeeID
both of which always return the value 1.

[silvercoder70]

I would suggest the following ...

Code: Text  [Select][+][-]

1. for each transaction do
2.   get payee id given some criteria
3.   if not found then insert payee
4.  
5.   do the above for the category
6.  
7.   insert row into the transaction table
8.  

[cdbc]

Hi
How about this:
1) when the app starts up, you 'select id, name from payee;' into a TPayeeList.
2) after that, you 'select id, name from category;' into a TCategoryList.
3) with a transaction you check the 'Lookup'-lists to find the names and id's
   you need, if they're not found, you offer the user the ability to add them to
   the sqlite3 database and when saved - add them to your lookup-lists...

just my 2 cents worth
Regards Benny

[five]

Simply...

Assuming that we have a unique index on the [Name] field

Code: SQL  [Select][+][-]

1.  
2. INSERT INTO PAYEE(Name)
3. SELECT  'Payee name'
4. WHERE NOT EXISTS(SELECT 1 FROM PAYEE WHERE Name = 'Payee name');
5.  
6. INSERT INTO CATEGORY(Name)
7. SELECT  'Category name'
8. WHERE NOT EXISTS(SELECT 1 FROM CATEGORYWHERE Name = 'Category name');
9.  
10. -- Assuming that the Transaction [ID] Field is autoincrement
11. INSERT INTO TRANSACTION ( Payee_id, category_id, Name, Amount )
12. SELECT
13.      (SELECT ID FROM PAYEE WHERE Name = 'Payee name'),
14.      (SELECT ID FROM CATEGORY WHERE Name =  'Category name'),
15.      'The transaction description',
16.      1234.67
17. ;
18.  
19.  

[pat03uk]

Thanks everyone for the help - I appreciate it.

silvercoder70

I think this is more or less what i've tried, a problem is that insert payee doesn't return a value(id), so i then have to follow with a select payee which does return a value(id)

cdbc

When I started I thought that payees and category could be simple lists. But then transactions could also be a list, which begs the question why bother with a database? needs a bit of thought!

five
I tried this but have yet to get it working, for simplicity I tried:
Insert into transaction(payeeID, date) Select ((Select id From payee Where name = '+quotedStr(payeename)+'),'+quotedStr(date)+')'

not sure if this can or should work, its complicated sqlite! (also tried replacing the first select with values.

[cdbc]

Hi
Don't forget this:

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

1. TSQLite3Connection = class(TSQLConnection)
2.   ...
3.   public
4.     constructor Create(AOwner : TComponent); override;
5.     procedure GetFieldNames(const TableName : string; List :  TStrings); override;
6.     function GetConnectionInfo(InfoType:TConnInfoType): string; override;
7.     procedure CreateDB; override;
8.     procedure DropDB; override;
9.     function GetInsertID: int64; // <-- THIS ONE !!! \o/
10.     // See http://www.sqlite.org/c3ref/create_collation.html for detailed information
11.     // If eTextRep=0 a default UTF-8 compare function is used (UTF8CompareCallback)
12.     // Warning: UTF8CompareCallback needs a wide string manager on Linux such as cwstring
13.     // Warning: CollationName has to be a UTF-8 string
14.     procedure CreateCollation(const CollationName: string; eTextRep: integer; Arg: Pointer=nil; Compare: xCompare=nil);
15.     procedure LoadExtension(const LibraryFile: string);
16.   Published

Easy access to the *Latest inserted ID*
Regards Benny

[five]

five
I tried this but have yet to get it working, for simplicity I tried:
Insert into transaction(payeeID, date) Select ((Select id From payee Where name = '+quotedStr(payeename)+'),'+quotedStr(date)+')'

not sure if this can or should work, its complicated sqlite! (also tried replacing the first select with values.

That should work for you...

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

1. program Project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   {$IFDEF UNIX}
7.   cthreads,
8.   {$ENDIF}
9.   Classes ,sysutils , strings, Dialogs
10.   { you can add units after this };
11.  
12. var sSQLCommand : AnsiString;
13.     P1 : string;
14.     P2 : String;
15.  
16. begin
17.  
18.   // Set Parameter  and raw sql command
19.   P1 := quotedstr('MyPayee');
20.   P2 := quotedstr('2024-11-09');
21.   sSQLCommand := 'INSERT INTO Transaction( payeeID, date) SELECT (SELECT id FROM payee WHERE name = @payeename ), @date ; ' ;
22.   // Raw SQL
23.   writeln(sSQLCommand);
24.   writeln(P1);
25.   writeln(P2);
26.   // Parse parameter
27.   sSQLCommand := StringReplace( sSQLCommand , '@payeename', P1,[rfReplaceAll]);
28.   sSQLCommand := StringReplace( sSQLCommand , '@date', P2,[rfReplaceAll]);
29.   // Final SQL
30.   writeln(sSQLCommand);
31.  
32.   // Use the final SQL Command in your query object ....
33.  
34.   readln;
35. end.
36.  

[pat03uk]

five

Thanks for that. I am having success with this. The first problem was using transaction which must be a reserved word. Now i have it working fine along the lines you suggest with payeeID and accountID but not categoryID. This must be a simple problem for me to solve.

Insert into transactions(payeeID, accountID) Select(Select id From payees Where name = "patrick"),(Select id From accounts Where name="Ford Money") works fine

Pat

[CharlyTango]

Before we dig deeper in table design or application design, here's a tip.

Do not call the table "transaction" because this will lead to misunderstandings in the future. A "Transaction" in the context of SQL databases means a set of SQL Commands which are to be done or not.

Do not call the Primary keys "ID" only, use more distinctive expressions instead. This way some can understand the table design much more easier and some design software is able to recognize the connections.

example:

   payee (payee_id, payeename).      eg amazon, bmw etc
   category(category_id, categoryname)    eg clothing, medicine etc
   booking(booking_id, payee_id, category_id, bookingtext, amount)

Whenever possible, do not use self combined Statements, use parameters instead:

SQLQuery1.SQL.Text:= 'SELECT booking_id, payee_id from booking WHERE booking_id = :booking_id';
SQLQuery1.ParamByName('booking_id').AsInteger := StrToInt(Edit1.Text);

or

SQLQuery1.SQL.Text:= 'INSERT INTO booking( payee_id, category_id, bookingtext, amount)
VALUES ( :payee_id, :category_id, :bookingtext, :amount)';
SQLQuery1.ParamByName('booking_id').AsInteger := StrToInt(Edit1.Text);
SQLQuery1.ParamByName('category_id').AsInteger := StrToInt(Edit2.Text);

SQLQuery1.ParamByName('bookingtext').AsString := Editbookingtext.Text);

SQLQuery1.ParamByName('category_id').AsFloat:= etc etc;
SQLQuery1.ExecSQL;

 

[five]

five

Thanks for that. I am having success with this. The first problem was using transaction which must be a reserved word. Now i have it working fine along the lines you suggest with payeeID and accountID but not categoryID. This must be a simple problem for me to solve.

Insert into transactions(payeeID, accountID) Select(Select id From payees Where name = "patrick"),(Select id From accounts Where name="Ford Money") works fine

Pat

I don't know with SQLITE, but the standard in SQLSERVER use of backet can overcome your problem.

Code: SQL  [Select][+][-]

1.  
2. INSERT INTO [TRANSACTION] ([payeeID], [accountID])  SELECT (SELECT id FROM [payees] WHERE [name] = "patrick") , (SELECT id FROM [accounts] WHERE [name]="Ford Money")
3.  
4.  

[Zvoni]

You are aware that above approaches only work, if the column "name" in payee as well as in category is UNIQUE?? (HINT HINT HINT!!!)
and i'm going to leave out case-sensivity here....

[five]

You are aware that above approaches only work, if the column "name" in payee as well as in category is UNIQUE?? (HINT HINT HINT!!!)
and i'm going to leave out case-sensivity here....

Yes for sure, it was mantionned earlier in that thread. Good to recall it.