Source language: Translate to:

Performing Math & Adding results to a variable

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Performing Math & Adding results to a variable

Postby Simanest » Mon Sep 17, 2007 12:45 pm

Does anyone know if its possible to perform a calculation of 2 particular fields in a record in a table and output the answer to a variable? I cant find any tools that will allow me to do math operations on individual fields only on entire columns, i.e. SUM

Is it possible to locate a certain field anywhere in the grid table and use its content for other purposes.
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Gaev » Mon Sep 17, 2007 1:42 pm

Simanest:
... perform a calculation of 2 particular fields in a record in a table and output the answer to a variable

Say you open a database table ...
Code: Select all
dbpOpenTable "MyDB" "MyTable" ""
... and say the table has fields Name, Address, Asset1, Asset2 ...NeoBook will automatically place the contents of each field in the CURRENT record in variables ...

[MyDB.MyTable.Name]
[MyDB.MyTable.Address]
[MyDB.MyTable.Asset1]
[MyDB.MyTable.Asset2]

... so if fields Asset1 and Asset2 are numeric ...
Code: Select all
SetVar "[TotalAssets]" "[MyDB.MyTable.Asset1]+[MyDB.MyTable.Asset2]"
Math "[MyDB.MyTable.Asset1]/[MyDB.MyTable.Asset2]" "0" "[AssetRatio]"


Note that as you navigate from record to record, values of [MyDB.MyTable.Asset1] and [MyDB.MyTable.Asset2] will be automatically updated ... but you will have to re-invoke the commands to recalculate values of [TotalAssets] and [AssetRatio] (in the above example).
User avatar
Gaev
 
Posts: 3737
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Simanest » Mon Sep 17, 2007 1:47 pm

Gaev, your a star! I didnt realise DBpro automatically knew this information already. You have just saved me a major headache. This stuff gets better all the time.

Thanks again!!
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Simanest » Mon Sep 17, 2007 2:23 pm

Ok, i have done that and it works perfectly. My next question is can i then put that result into a blank field i have created in that record.

Then move onto the next etc, populating the new field with the math result i have now got.

I know i can do Field to VAR but can i do VAR to Field?
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Gaev » Mon Sep 17, 2007 2:26 pm

Simanest:
Gaev, your a star!

I didn't develop the plugin ... Dave is the real star ... I am just the messenger ... who quotes from the Help file :)
User avatar
Gaev
 
Posts: 3737
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Simanest » Mon Sep 17, 2007 2:34 pm

Ok, i have done that and it works perfectly. My next question is can i then put that result into a blank field i have created in that record.

Then move onto the next etc, populating the new field with the math result i have now got.

I know i can do Field to VAR but can i do VAR to Field?
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm

Postby Gaev » Mon Sep 17, 2007 3:56 pm

Simanest:
Ok, i have done that and it works perfectly. My next question is can i then put that result into a blank field i have created in that record.

Using the previous example fields with fields Asset1 & Asset2 ... and assuming the blank fields are TotalAssets and AssetRatio ...
Code: Select all
SetVar "[MyDB.MyTable.TotalAssets]" "[MyDB.MyTable.Asset1]+[MyDB.MyTable.Asset2]"
Math "[MyDB.MyTable.Asset1]/[MyDB.MyTable.Asset2]" "0" "[MyDB.MyTable.AssetRatio]"


Then move onto the next etc, populating the new field with the math result i have now got.

Take a look at the dbpNext command ... and read about the Special Variables ...

[ID.Table.$RecNum]
This is the current active record's position from the beginning of the file based on the current sort/query.

[ID.Table.$RecCount]
If a query is active, the result reflects the number of found records. When no query is active, the result reflects the total number of records in the database.

I know i can do Field to VAR but can i do VAR to Field?
dbpFieldToVar will ... according to the Help file ... Copy the contents of a single field from each record to a variable. Items will be separated by the specified delimiter. ... which isn't what you want.

dbpVarToRecord may be deployed in some cases.


All of this is documented in the Help File.
User avatar
Gaev
 
Posts: 3737
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Simanest » Mon Sep 17, 2007 4:10 pm

Thanks again Gaev, just after i posted the article i sussed it, its now adding field data all the way down through my records, adjusting to the calculated formulae automatically, fantastic stuff.
I need to look deeper in the help files, there is more there than meets the eye!

very Happy Cookie!! :P

P.S. NeoBook/DBpro RULES!!

cant beleive i just wrote that, never mind.....tis good though
Simanest
 
Posts: 10
Joined: Thu Sep 13, 2007 3:28 pm


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest