1 Topic by lemodizon (edited by lemodizon 2022-06-02 03:42:23)

  • Registered: 2022-05-15
  • Posts: 48

Topic: How to use the calculated field

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.

Post's attachments

Attachment icon calculated field.JPG 20.98 kb, 69 downloads since 2022-06-02 

-lemodizon
  • Registered: 2019-12-24
  • Posts: 169

Re: How to use the calculated field

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

"Energy and persistence conquer all things."
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,670

Re: How to use the calculated field

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.

Post's attachments

Attachment icon cf.zip 336.26 kb, 159 downloads since 2022-06-02 

  • Registered: 2022-05-15
  • Posts: 48

Re: How to use the calculated field

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.

-lemodizon

5 Reply by lemodizon (edited by lemodizon 2022-06-03 03:58:13)

  • Registered: 2022-05-15
  • Posts: 48

Re: How to use the calculated field

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

-lemodizon
  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: How to use the calculated field

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.

Post's attachments

Attachment icon lemodizon_calc.zip 549.08 kb, 135 downloads since 2022-06-03 

brian
  • Registered: 2022-05-15
  • Posts: 48

Re: How to use the calculated field

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?

Post's attachments

Attachment icon test.rar 294.91 kb, 122 downloads since 2022-06-03 

-lemodizon
  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 417

Re: How to use the calculated field

Maybe this post will give you some ideas

http://myvisualdatabase.com/forum/viewtopic.php?id=6995

brian