Source language: Translate to:

Trying to Calculate a Percentage

Questions about using NeoBook's scripting language

Moderator: Neosoft Support

Trying to Calculate a Percentage

Postby billc » Thu Oct 07, 2010 4:03 pm

Hi, all.

Okay, so this is a problem revisited.

And I realize that this is specifically about DBPro but I suspect that this might be an issue across the board with NeoBook so I'm placing this in the General Discussion area.

I've tried literally everything that was suggested and I've made a little headway but getting a percentage to show up as a variable is still elluding me.

Each record can have a Budget and a Cost to Date (CTD). I want to show the Percent Expended in a separate field.

At first, I couldn't get anything to show up in the PercentExp field using SetVar, Math, and even DefineVar.

Then, by placing the following code in the FirstRecord, PreviousRecord, NextRecord, and LastRecord button actions:

SetVar "[oitFundsTracker.WorkOrders.PercentExp]" "[oitFundsTracker.WorkOrders.CTD]/[oitFundsTracker.WorkOrders.Budget]"

Finally got something to show in the field but it's the calculation that's being displayed, as in...
$84,982/$624.39

There is no exclamation mark in front of the calculation so the right-slash shouldn't be displayed but it's there every time. In other words, the formula is displayed with the actual variable values but the calculation does not happen.

BTW, putting the SetVar in the Book actions, the Page actions, and even in the Field actions had no effect at all. It only works when the script is in the control buttons.

And there's no way to get even the $84,982/$624.39 to show up in the table view in DBPro.

Anyone know how to get the calculation to work so I get just the result in the field display rather than the calculation itself with the variable values?

This has been holding me up on delivering a product for over a week now and I'm about ready to implode. Help, please.
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Postby Gaev » Thu Oct 07, 2010 4:37 pm

billc:

a) check the details about the fields in question ... just before your SetVar command ...
Code: Select all
dbpGetFieldDefs "database id" "table" "delimiter" "variable"
... display the results in an AlertBox.

b) Assuming, the fields are all numeric try ...
Code: Select all
SetVar "[tempCTD]" "[oitFundsTracker.WorkOrders.CTD]"
Setvar "[tempBudget]" "[oitFundsTracker.WorkOrders.Budget]"
SetVar "[tempPercentExp]" "[CTD]/[Budget]"
... then do an AlertBox showing all three [tempXXX] variables.

c) then, immediately after the ...
Code: Select all
SetVar "[oitFundsTracker.WorkOrders.PercentExp]" "[oitFundsTracker.WorkOrders.CTD]/[oitFundsTracker.WorkOrders.Budget]"
... do an AlertBox showing each of the three database fields.


Then tell us what was displayed in each case.

Debugging is a methodical process of examining each of the "possible culprits" immediately before/after the "command exhibiting unexpected behaviour".
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

I'll report back...

Postby billc » Fri Oct 08, 2010 7:54 am

I'll do that. Thanks, Gaev.


Gaev wrote:billc:

a) check the details about the fields in question ... just before your SetVar command ...
Code: Select all
dbpGetFieldDefs "database id" "table" "delimiter" "variable"
... display the results in an AlertBox.

b) Assuming, the fields are all numeric try ...
Code: Select all
SetVar "[tempCTD]" "[oitFundsTracker.WorkOrders.CTD]"
Setvar "[tempBudget]" "[oitFundsTracker.WorkOrders.Budget]"
SetVar "[tempPercentExp]" "[CTD]/[Budget]"
... then do an AlertBox showing all three [tempXXX] variables.

c) then, immediately after the ...
Code: Select all
SetVar "[oitFundsTracker.WorkOrders.PercentExp]" "[oitFundsTracker.WorkOrders.CTD]/[oitFundsTracker.WorkOrders.Budget]"
... do an AlertBox showing each of the three database fields.


Then tell us what was displayed in each case.

Debugging is a methodical process of examining each of the "possible culprits" immediately before/after the "command exhibiting unexpected behaviour".
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Postby Neosoft Support » Fri Oct 08, 2010 10:35 am

You probably need to remove the "$" characters from the data before performing the calculation. The easiest way to do this is to use the StrReplace action.

* Also please don't post the same question in multiple places - it's not necessary.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Multiple Posts

Postby billc » Fri Oct 08, 2010 10:41 am

* Also please don't post the same question in multiple places - it's not necessary.[/quote]

Well, that assumes that everyone who might have a suggestion or solution checks every single forum each time they visit.

Since my particular concern could have been with either the action commands or DBPro, I felt that sounding out possible responses in both areas would be valuable.

However, I see that my other entry was deleted anyway so, to be accurate, multiple posts might be necessary but they are not allowed.
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Trying to Calculate a Percentage - Reporting Back to Gaev

Postby billc » Fri Oct 08, 2010 10:55 am

Gaev,

Thank you again for your suggestion on debugging.

Here is the actual code I just used following your instructions...

dbpGetFieldDefs "oitFundsTracker" "WorkOrders" "," "[FieldDefsResult]"
AlertBox "INFORMATION" "Field definitions are: [FieldDefsResult]"

SetVar "[tempCTD]" "[oitFundsTracker.WorkOrders.CTD]"
SetVar "[tempBudget]" "[oitFundsTracker.WorkOrders.Budget]"
SetVar "[tempPercentExp]" "[CTD]/[Budget]"
SetVar "[tempPercentExpDatabaseNames]" "[oitFundsTracker.WorkOrders.CTD]/[oitFundsTracker.WorkOrders.Budget]"
AlertBox "INFORMATION" "tempCTD is [tempCTD]|tempBudget is [tempBudget]|tempPercentExp w/o database field names: [tempPercentExp]|tempPercentExp with database field names: [tempPercentExpDatabaseNames]"

SetVar "[oitFundsTracker.WorkOrders.PercentExp]" "[oitFundsTracker.WorkOrders.CTD]/[oitFundsTracker.WorkOrders.Budget]"
AlertBox "INFORMATION" "tempCTD is [tempCTD]|tempBudget is [tempBudget]|tempPercentExp w/o database field names: [tempPercentExp]|tempPercentExp with database field names: [tempPercentExpDatabaseNames]"


Here are the alertbox results:

1. Pertinent field definitions:
Budget Currency
CTD Currency
PercentExp Integer

Please note that the PercentExp field is now an Integer but I also tried making it a Float and a BigInt without success.

2. Temporary fields:
tempCTD is $.00
tempBudet is $10,000.00
tempPercentExp is $.00/$10,000.00

3. The second variable listing was exactly the same.

As you can see, the result is "$.00/$10,000.00" - the formula rather than the result.

Some NeoBook staffer suggested that I strip the dollar signs before the SetVar command, which I'll try now, but at least now you have what you asked for. If the removal of dollar signs is the key, I'll report back right away.

Thanks for your willingness to help with this, Gaev.[/img]
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Trying to Calculate a Percentage - Reporting Back

Postby billc » Fri Oct 08, 2010 11:09 am

Removing the dollar signs was the solution, all. The PercentExp variable is now showing the correct percentage value. Thank you for that clue.

Now I'll work on having the variable displayed properly.

I really do appreciate the help. I'd never have thought of the dollar signs.
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Postby Gaev » Fri Oct 08, 2010 1:37 pm

billc:
I'd never have thought of the dollar signs
As per your earlier post, the field is defined as currency (not some kind of pure numeric field) ... so the database you use returns values with the currency symbol.

Note that according to the same post, PercentExp is defined as an Integer ... since the result of your division is most likely to be a fractional amount, you will most likely lose the fractional amount ... so 0.00001 to 0.499999 would all be stored as 0 !!! ... if you want the result to be a percent with (say) two decimal places ...

a) your Setvar command would be better replaced with the Math command ... where you can have NeoBook return a value with the desired number of decimal points.

b) your PercentExp should defined as a Decimal field with the desired number of decimal places.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Sat Oct 09, 2010 8:52 am

Billc:

Some NeoBook staffer suggested


:shock: That "Neobook staffer" is Dave Riley, developer of Neobook. Needless to say, he's forgotten more about Neobook than all of our knowledge combined. He takes up his valuable time to cruise through here once a day and helps EVERYBODY.

Most of the folks whom you will receive help from here DO check every single forum when they visit. If you have a question that could be covered in more than one forum, pick one. It will get answered.

And yes, duplicate posts are not allowed. I don't know of ANY forum worth it's salt that allows duplicate posts. It wastes people's time. And these people will be the ones that help you.

If it were me, I would have responded with a simple "Sorry. Won't happen again". But then, I'm from the old school. I was taught not to throw things at the teacher.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1505
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Gee, I'm sorry.

Postby billc » Sun Oct 10, 2010 3:04 pm

Thanks for the admonition, Wrangler.

Of course, I had no idea who was responding since it offered no name.

I had no idea that it was someone who was helping everyone everyday in every forum.

I had no idea that it was the unquestionably brilliant creator of NeoBook and the many programs that have been derived from it.

I came onto the forum to find out how to accomplish something and, several times now, I've come away with the impression that I shouldn't have bothered anyone. Since this is the ONLY resource that I have other than the manual and the online help (which is essentially the manual), I didn't even show up here until I was desperate.

And I probably won't waste anyone's time again.

BTW, there are some outstanding forums that DO allow simultaneous posts for the very reason that I stated. In fact, there are many.

But I do apologize. It was not my intention to offend anyone. If someone took offense, then I'm sorry that person chose to do so.

I'm working in a vacuum here trying to demonstrate NeoBook's potential for a group of IT professionals and coming up short in some critical tasks so I came to the only resource that I knew of hoping to get friendly and helpful assistance without any baggage.

Email (and therefore forums) are a terrible way to communicate, especially when people tend to think the worst of anything that is stated and tend to judge one another typically unfairly. But that's human nature, I suppose.

So thank you for pointing out my blunder, Wrangler. Please don't think too ill of me. I committed it in ignorance.
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

One more thing, Wrangler...

Postby billc » Sun Oct 10, 2010 3:05 pm

Your signature...

Before you criticize someone, walk a mile in their shoes. Then when you criticize them you're a mile away and you have their shoes. - Wrangler
billc
 
Posts: 28
Joined: Fri Jul 30, 2010 1:46 pm

Postby Neosoft Support » Mon Oct 11, 2010 11:06 am

I don't think anyone was offended by the duplicate post - certainly not me. I didn't mean to make it sound like anything other than the very minor issue that it was. Many other sites do allow duplicate posts, but we're small enough that it's not necessary here. Not a big deal.

I came onto the forum to find out how to accomplish something and, several times now, I've come away with the impression that I shouldn't have bothered anyone...


I hope you really don't feel that way. We try to make everyone feel welcome here - beginners and experts alike. I apologize if you felt unwelcome for any reason.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBook Action Commands

Who is online

Users browsing this forum: No registered users and 1 guest

cron