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)
brian.zaballa wrote:Hello,
Two option enter into my mind to address it.
Option 1. Do have a field (a hidden maybe) that will handle the calculated field, then put the calculation in the OnClick Event of the Save button
Option 2. If you are familiar with trigger, I am using it in some of my projects with complex saving or interaction with other table, in this sample, I just use 1 table.
Take note that you must be familiar if not expert with trigger, personally I am enjoying using triggers, procedures and functions (proc and func is only available for mySQL thou). Triggers are also difficult to maintain so for beginner, I would go with Option 1.
Hi brian,
thanks for the sample file as newbie i will go first to option 1.... do you know how to do in MVD on updating a certain field? i'm practicing on how to update the current qtty once when there is item out.
when updating a certain field in a table do i have to the sql syntax or is their another way to do this in MVD?
derek wrote:Hi,
You need to be aware of a couple of things.
1. Calculated fields are not stored in any table - they are the result of performing a calculation using 'real data' that is stored.
2. Calculated fields are recalculated whenever any underlying data has been saved.
3. You cannot create a calculated field based on another calculated field
4. The commands available when creating a calculated field are a sub-set of what is available when you write a script; also, the syntax, although similar, is not always the same which can initially be confusing.
Attached are a couple of examples;
1. calculating the value (price * qty); storing the value creates data redundancy.
2. calculating the age of an item (this changes every day - using a calculated field ensures the 'age' is always up to date).
Derek.
Hi derek,
Thanks for the sample file...
question can i create a field where i can stored the calculated field? example: balqtty(currentqtty - itemout) though storing the value creates data redundancy. just want to ask if this possible to do. or update the currentqtty once it is done in calculation.
thanks again
joshuA wrote:Hi lemodizon,
There is a basic example on this page. And if you click the button beside the Expression label, it will help you build other similar expressions using your table fields, etc. Aside from that you will have to experiment and ask more specific questions as you go along. Good luck!
-joshuA
Hi joshuA,
Thanks for the link.
Hi everyone,
Sorry i'm a newbie how do you use calculated field MVD?
Do you have a sample file on this.
Thanks in advance.
hi everyone,
how do you do in MVD update a table...
I have a stock out where i have current qtty and i have a quantity out. once i clicked the button it will deduct my current quantity depend on the number qtty out and will update product table (current qtty).
i searched in this forum regarding on this topic.... i found several topics ( i can't follow due to many codes)...
hope you can guide me...
thanks in advance.
hi Destiny,
i'm not sure if this is the output you expect to display in your program?
Destiny wrote:Here is an example of my program
Voici un exemple de mon prog
Hi Destiny,
the sample file of joshua works in my project.
you can try also this file see the attached file.... derek send it to me it is working on project file also.
joshuA wrote:Destiny,
Somehow, you will need to reference the same date field on Form1 as on Form2. Without seeing your situation, it's hard to make suggestions. I have provided another example that might be helpful.
Glad it was helpful for you lemodizon.
-joshuA
Hi joshua,
it works. Thank you
derek wrote:Hi Lemodizon,
There are various ways to do it depending on how you're getting the date in the first place.
From your sceeen shot, I'm guessing that you're calling it with a 'select' in a script so you could try it as per the attached.
The first line in the script selects just the date part and the second line reformats it to mm-dd/yyyy.
Perhaps the bigger question is why you're wanting the date as a text field in the first place?
If you attach your project, people might be able to suggest alternatives.
Derek.
hi Derek,
it works again thank you.
derek wrote:Hi Lemodizon,
There are various ways to do it depending on how you're getting the date in the first place.
From your sceeen shot, I'm guessing that you're calling it with a 'select' in a script so you could try it as per the attached.
The first line in the script selects just the date part and the second line reformats it to mm-dd/yyyy.
Perhaps the bigger question is why you're wanting the date as a text field in the first place?
If you attach your project, people might be able to suggest alternatives.
Derek.
hi derek,
thanks again.
here is my project file.
joshuA wrote:Hi Destiny,
If I understand your question, you could use the form's OnShow event with something like:
Form1.Edit1.Text := FormatDateTime('yyyy-mm-dd',Form1.DateTimePicker1.DateTime);
Form1.Edit2.Text := FormatDateTime('hh:mm:ss',Form1.DateTimePicker2.DateTime);
-joshuA
Hi joshua,
thanks i learned new syntax from you.
thanks again.
Hi derek,
here is my project file.
Thanks again.
Hi everyone,
I tried the role based access. i don't know how to use it...... is there a tutorial on this?
I tried to create a username and password but it doesn't recognize my created account.
hope you can guide me.
thanks in advance.
Hi everyone,
just want to ask how do you remove the time in the text box.
is there another way to display the date not using a textbox.
coz i'm using a textbox to display the date from my table.
hope you can guide me.
thanks in advance.
joshuA wrote:Nicely done lemodizon.
Thanks for sharing too.
-joshuA
Hi joshuA,
Thank you, I'm a newbie in MVD need more practice. while reading the book "hands-on exercises for my visual database training" when i read the database normalization i checked my tables still need to revised.
hoping i can finish my simple inventory and make another to be familiarize MVD, SQL and etc.
Hi Everyone,
here is my updated design in my simple inventory.
Thank you in advance.
derek wrote:Hi,
If you use a 'status' indicator, then there is no need for a temporary table. Not saying one is better than the other, it's all about giving you choices.
If you use a status indicator, there are a couple of options to consider (see the attachment).
Option 1 in the attachment - simply click on the 'status' cell in frmstockout to change the cell value from 'to be confirmed' to 'confirmed' (but if you decide to have other statuses as well, clicking on the 'status' cell will toggle through each of the various statuses in turn).
Option 2 in the attachment - you can use the 'status' filter (optionally) to filter records by status and then use the 'change to status' combobox (combobox2) to select the status you want to change the records to (giving you the option to change things back if you get something wrong).
Again, no right or wrong way, it's all about what suits your application best.
Derek.
Hi All,derek
I got your point.
Thank you very much..
I updated my file since i'm still studying SQL I didn't putted any script code in my tablegrid. I saved all the data in my stockout table so i can use the edit button.
I chose option1 but i didn't put any script code in the table. just set the setting what records it will display.
Questions:
1. How to check in the tablegrid if the product is in the list already
So that my record will not duplicate and the user will go edit button. ( I tried this but using sql checking in the database if it has duplicate).
2. in my tablegrid "Expdate" how can i set into date only format? ( i already set in the property noting happens)
3.How do you calcute in the tablegrid every item will will deduct based on the OUT item and display the balance and will removed in my table if the status is "Confirmed" once I clicked the processd button.
Once it is done it will update my table products(master list) the quantity and balance field. ( i tried this but in the same form)
Thank you everyone for guiding me and helping me out .
derek wrote:Hi Lemondizon,
Can't you just add a 'status' flag to your 'stockout' table ('confirmed', 'to be confirmed' - and anything else you might want)?
Any entries you make on the 'frmstockout_add' form can be created with a default status of 'to be confirmed'
Then it's simply a case of a batch update of the status when required.
It would also give you the option to filter by 'status' which would probably be useful.
Derek.
Hi derek,
thanks for your suggestion. I will revised my form.
here is the updated file. i don't know if I got your suggestion derek right... i added combobox i'm looking for optional button the only available is checkbox.
Do I still need the temp table or just stockOut table will do?
joshuA wrote:Hi lemodizon,
This is not a complete solution, but hopefully it provides some ideas to work with.
Most tablegrids are attached to a table in the database, so in my example, I added a TMP table to hold the changes "to be committed". This gives you a table full of items that need to be updated, so you'll have to fill-in the SQL to make that happen.
While it is possible to add items manually to a tablegrid, I believe that defeats their purpose. Someone correct me if I am wrong about that.
-joshuA
Hi joshua,
Thanks for the response.
TMP table is a temporary table once i clicked the button "confirmed" will it transfers or create a records going to my "StockOUT" table is this possible in MVD? so that StouckOUT table will stored all the item that is been pulled out.
Edit button
i tried to create a script code it doesn't work.
// i created a var that will store all the text fields
var
Hold_flds_QttyOut: string;
Hold_flds_Remarks: string;
Hold_flds_Area: string;
Hold_flds_NewQtty: string;
///////////////////////////////////////////////////////////////////////////////////////////////////
procedure frmStockOUT_Add_OnShow (Sender: TObject; Action: string);
begin
if action = 'NewRecord' then
if frmStockOUT_Add.txtProdID.Text <> '' then
begin
frmStockOUT_Add.cboProdList.dbItemID := strToInt(frmStockOUT_Add.txtProdID.Text);
frmStockOUT_Add_cboProdList_OnCloseUp(Sender);
end;
begin
If Action = 'ShowRecord' then
Begin
frmStockOUT_Add.txtQttyOut.Text:=('SELECT QttyOut FROM Temp_StockOUT WHERE id_products= '+frmStockOUT_Add.txtProdID.Text.sqlValue+ ' '); /// i'm not sure here i don't know if this is right.
End;
end;
end;
thanks for helping me out.
Hi Everyone,
i created a stock OUT form.
I have a two forms (form1) will ADD Only a product one at a time (No saving record yet) once the user knows how many quantity will be out.
it will display only in my tablegrid.(form2).
the CONFIRMED button (this button will save the records from my tablegrid). is this possible in MDV? i just want to save all the records added one time.
while learning MVD i can display the data but i have to use save record then display into my tablegrid.
hope you can guide me.
sparrow wrote:Hi all,
Start with the simplest, which is what MVD is for.
Creation of the simplest application without a script, SQL, pascal, etc.
The simplest forms, tables, buttons, etc.
Derek tells you this way all the time.
Believe me, a lot can be done without scripts and SQL.
Find examples on the forum, here is an example of the simplest:
http://myvisualdatabase.com/database_ex … ntory.html
This will give you an understanding of how and what works. Other programs can be found on the forum.
Try to add your fields and forms.
From the simplest you will reach more complex programs.
Step by step...
Then a script with the simplest constructions and more... )))
Hi sparrow,
Thanks for this. step by step.
derek wrote:Hi,
Rather than use the record-id, it might be better to set up a counter on the relevant table in your database schema and use that (with your 'P' prefix) as the basis for your product code.
You might also want to think about using 'wildcards' in your search and also the incremental search function (see attached).
They're just options to consider but it's as well to be aware of them.
Derek.
Hi Derek,
Thanks for the file.
honestly it's kinda hard on my part coz i don't know where to start to learn. it's kinda mixed learning MVD interface,sql, database and scripting.
i'm looking for tutorial for script that use MDV platform.
it's hard but i enjoyed to learned in this forum. hope i can create my first inventory application in MDV.
Thank you again.
sparrow wrote:frmProd_Add.txtProdCode.Text:= sqlexecute('select "P" || (IFNULL(max(id),0) + 1) from Products');
I think it's time for you to get familiar with SQL.
Hi sparrow,
thanks again. your right..... i'm familiarizing the MVD and little by little i will insert what i learned in sql.
thanks again.
Posts found: 1 to 25 of 48