Skip to forum content
My Visual Database
A Simple Solution for Creating Databases
You are not logged in. Please login or register.
Active topics Unanswered topics
Search options (Page 1 of 2)
derek wrote:Hi,
If your prices or tax rates are changeable but you don't want those changes to affect old transactions, then you could try it like this (using the attachment as an example).
1. if the transaction is a 'New Record' then
1.1 retrieve the price from the 'product' table into an edit field on the form.
1.2 use a calculated field to calculate the value (price * quantity).
1.3 when you save the record, write the price to the invoice table.
2. if the transaction is a 'Show Record' then
2.1 the price is now retrieved from the 'invoice' table (not the 'product' table) and therefore not affected by any subsequent change to the price held on the 'product' table.
This way, you also have the option to over-ride the price manually (for whatever reason), so it's quite flexible.
Hope this helps,
Man, this is so simple and elegeant solution....
Tnak you very much.
Cheers,
Vladimir
blackpearl8534 wrote:You have to use prices in each transaction... for example you are selling some items then you have to add price column and taxes column in sale table.
Thanks for answer, one more question. I have problem with part of SQLExecute statment. What I don't know is how to add condition after WHERE? Condition should be: sales.date = DateTimePicker1 date. Part of code as example:
(SELECT id from orders WHERE orders.date = DateTimePickerTest)
Basically what I dont't know is syntax to compare if date from database DATE field is same as date in datetimepicker commponent.
Thanks for help.
Hi all,
I,m wondering what's best way to handle situations when db contains prices and taxes which will occasionally change. When price/taxes change, results of all calculated field will change but "old" records should somehow keep old prices.
Vladimir
Solved by myself. If anyone has same problem, right syntax for using DateTimePicker date in query is
Hi,
I'm having problems trying to make my own quarry button. What I'm trying to do is to include date from component DateTimePicker into my own query.
SELECT DISTINCT "sirovine"."naziv" as "sirovine.naziv" ,(SELECT total(kolicina) FROM sirovine_izlaz WHERE sirovine.id = sirovine_izlaz.id_sirovine AND sirovine_izlaz.id_proizvodnja_pekara IN
(SELECT id FROM proizvodnja_pekara WHERE proizvodnja_pekara.datum > Date('Form1.DateTimePicker1'))) AS 'sirovine.PROBA' FROM "sirovine"
Anyone know what's syntax should be used in my example?
Yea, this works great.
Than you for your time and help.
Hi,
What I need is to auto fill db table 'categories', field name 'cat_name' with predefined categories names only when user FIRST TIME run application. It's because I want to use that predefined categories names in script so user can't change them. For example: drinks, food, raw materials.
Thanks in advance.
Vladimir
Thanks Brian,
I would never figure this out by myself and without your help.
Greetings and thank you for your time.
brian.zaballa wrote:That's just an example to give you an idea.
Here, I made some modification on your sample. Hope it can give you another idea on how you can do it.
You can just hide the negative value on the raw mat stock list, or have a field saying that it was a mats from a recipe.
I forgot to change the Grid title (Total Received), it must be changed to Current Stock. If you want to show the total received, then you can just add additional calculated field on your raw_materials table
(SELECT total(quantity) FROM raw_mats_receive_reports WHERE raw_materials.id = raw_mats_receive_reports.id_raw_materials AND quantity > 0)
Hi Brian,
One more thing, there is problem with code when I try to enter decimal number.
('+_qty+' * quantity * -1)
Saving integer is ok. Take look at screenshot if you have time.
Thank you for your time.
Vladimir
That works, thank you again.
Yea, I see now. Any quick tip how to hide negative valuse from the raw mat stock list?
Thanks again, this means a lot to me, I'm still learning.
Vladimir
At work right now, but after frst look, post you linked is what I was looking for.
Thank you Brian, appreciate your help.
Vladimir
Edit1: your example works fine as it is, but my problem is that I have table production_reports which should be key for ingredients stock quantity calculation. There would be a problem if I want to delete wrong entry from table production_reports , in that case calculation of ingredients stock quantity doesn't work.
Thank you anyway for your time.
Hi all,
Last few days I was trying (without success) to find solution for my problem. For easier understanding of problem, I created and attached example. Example is Bakery with list of products (Meat Pie, Cherry Pie, Chesse Pie...) and list of Row Materials. Every product has his own recepie, for example, Meat Pie includes 0,3 kg of flour + 0,2 kg of meat; making Cherry Pie includes 0,2 kg of Flour + 0,4 kg of Cherrys, etc...) My main problem is how to calculate current stock quantities for raw materials (It should be something like this: (every raw material total received) - [(every product total produced * quantity of raw material from recepie for every product)]. Take look at attached example, it will be more clear what I mean (key field is in table raw_materials - cf_qty_In_Stocks).
I think I need to use SQLite JOIN function but I don't know how to do that or maybe my database is not designed as it should. It would be great if someone can solve this problem in this example for me.
Any help is appreciated, thanks in advance.
Vladimir
Hi ehwagner,
That's not exactly what I wanted but it's good enough workaround and it works well. One more question. Is there a way to auto rename file when saving file using component DBFile with property CopyTo? What I'm trying is something like this:
procedure Form2_Button_Save_OnClick (Sender: TObject; var Cancel: boolean);
begin
Form2.DBFile1.dbFileName := 'Test '+ (FormatDateTime('dd.MM.yyy.',(now)));
end;
but this doesn't work.
Thank you for help and for your time.
Vladimir
Hi Derek,
I tried few thing too, without success.
Thanks for your time anyway.
All the best,
Vladimir
Hi wenchester21,
That's not what I wanted. Take look at picture, there is left and right buttons on the DBFile1.
Hi,
Is there a way to make this work:
procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
begin
Form1.DBFile1.LeftButton.Click;
end;
I know this code doesn't work but is there a way to make on button click to make event DBFile left button click. I want to hide DBFile left and right buttons and make separate button for adding files.
Thanks in advance,
Vladimir
Problem solved.
sqlexecute('update emplyees set last_name_filename = null where id =' + Form1.tablegrid1.sqlvalue);
Derek, thank you for your time, you helped me a lot.
Hi Derek,
It works nice with TEXT field, but I tried with FILE field and it doesn't work in that case. In my FILE field is stored only link to copied file (files are stored in folder using CopyTo). I just want do delete that link after I remove file from folder. I'm doing something wrong?
Thanks Derek
Hi all,
Example: there is table emplyees with fields first_name and last_name in it. Is it possible on button click (with script) to delete only specified last_name but first_name needs to stay (i dont't want to delete whole row - record). Basically what I need is to delete only last name of specified person on button click. I tried something like this but it doesn't work:
SQLExecute('DELETE from employees WHERE last_name.id =' +Form1.tablegrid1.sqlvalue);
SQLExecute('DELETE last_name from emplyees WHERE last_name.id =' +Form1.tablegrid1.sqlvalue);
Is it possible and if it is, all I need is syntax.
Thanks in advance,
Vladimir
Thank you very much Derek, that's what I was looking for.
Hi Derek, tnx for answer,
Thing is that I dont't want to use tablegrid, what I meant is to use data (datefield) from database table writing script.
Something like this:
days_diff = SQLExecute(('SELECT MAX(dalevery_date) FROM deliveries') - ('SELECT MIN(dalevery_date) FROM deliveries')) ;
and get result - number of days as integer because i need to do some other calculations with it later.
Anyway, thanks for your time.
Vladimir
Hello,
I searched forum and all I can find is solution with two DateTimePickers, What I need is script to select max date from table1 > field date, select min date from table1 > field date and calculate days difference > max date - min date and get result as integer. I know how to do that using calculated field but I need script because I want to show that result in Form1.Edit1 on OnShow.
Thanks in advance,
Vladimir
That's exactly what I needed.
Thanks Derek.
Posts found: 1 to 25 of 36