Help with Calculated Field Expression

[HRSue]

Hi! I'm very new to alpha five and I'm not a programmer at all. The most I've done with formulas is creating basic formulas in excel so please be patient with me :o)

We are creating a web page that will house executive level performance and merit information. We are being asked to have a tab where we have current salary, merit percentage increase, merit dollar increase and New Salary. So for example:

Current Salary = 100,000
Merit % Increase = 10%
Merit $ Increase = 10,000
New Salary = 110,000

Here is the catch. I need to be able to let someone enter a $ amount OR a % and the other needs to automatically calculate. For example, if someone enters the 10,000 in the Merit $ increase field then the Merit % Increase should auto populate. If someone enters the 10% in the Merit % increase then the Merit $ Increase should auto populate. The New Salary can add the Current Salary and the Merit $ Increase amount.

What we have tried:
We enter a calculated field expression in the Merit % Increase to say something like - if(Merit$Increase > 0, Merit$Increase + CurrentSalary, 0) and in the Merit $ Increase field we have if(Merit%Increase > 0, ((Merit%Increase - CurrentSalary)/(CurrentSalary)), 0) except it creates a circular reference obviously and doesn't work.

We also tried using the onclick, onchange, onselect, etc. but we don't know java or xbasic to figure out what kind of wording to use so we keep getting errors. We checked the forums and the videos and still cannot figure this out.

Oh, we are connecting to a sql table that houses all our data.

If anyone can help us newbies we'd really appreciate it!!

Thank you so much!!!!

[Keith Hubert]

Re: Help with Calculated Field Expression

Hi Sue,

While this is not a definitive answer but just a pointer, have a look at the e.variables in grid events.

[Pat Bremkamp]

Re: Help with Calculated Field Expression

Sue,

Here's a thought. Create an additional field of type hidden and make it a calculated field with a formula like

if( Merit $ Increase > 0, Current Salary + Merit $ Increase, if( Merit % Increase > 0, CurrentSalary * Merit % Increase, 0))

Then make New Salary = Current Salary + this hidden value.

Also, if these are going to be used on the web, you should not have spaces in the field names, and avoid special characters that have special meaning like $ and %. Most of us use something like Merit_Dollar_Increase and Merit_Percent_Increase

[HRSue]

Re: Help with Calculated Field Expression

thank you both! I will look into the e.variable and see if I can manage that.

Pat - that is a good idea. The only thing is that the person entering the information will want to see what the % is if they add the $ amount and they will want to see the $ amount if they enter the percent. Any thoughts on how to do that without getting a circular reference? I am stuck on the last part of the formula. I want it to allow me to just enter the amount if the other part is false. We did add a flag and that is the InputFlag with an option of Percent or Amount. So basically I need something like:

if(InputFlag = "Amount" AND TargetAmtNew > 0, TargetAmtNew / USSalaryCurrent, ......) that last part is the part that gives me a problem. I have to put a similar formula in the Percent field.

Anyone???

Thank you!!

[Peter.Greulich]

Re: Help with Calculated Field Expression

I know you're not a programmer, but you could do that w. some simple JavaScript that reads the 10% or the $10,000 from the input field, calcs, and dumps the results into the output field.

Now do I get that $10,000 merit bonus?

[HRSue]

Re: Help with Calculated Field Expression

I knew you would know Peter :o) Now can you give me the wording to use because that is where I'm having the problem lol. I can't figure it out without creating a circular reference. I wish I was receiving that bonus after all this Alpha Five work ;o)

[Peter.Greulich]

Re: Help with Calculated Field Expression

If I have time later, I'll write a sample expression for you.

[gaby_h]

Re: Help with Calculated Field Expression

This is just one approach, not fully tested; seems to do what you are after; probably needs some tweaking:
It's all happening on the client side

CurrSal - no expression
MeritPct - (1)
MeritAmt - (2)
TotSal - (3)

(1)In its onBlur
var z1={grid.Object}.getValue('G','CURRSAL',{Grid.RowNumber});
var z2={grid.Object}.getValue('G','MERITPCT',{Grid.RowNumber});
{grid.Object}.setValue('G','MERITAMT',{Grid.RowNumber},(z1*z2)/100);

(2) in its onBlur
var z1={grid.Object}.getValue('G','CURRSAL',{Grid.RowNumber});
var z2={grid.Object}.getValue('G','MERITAMT',{Grid.RowNumber});
{grid.Object}.setValue('G','MERITPCT',{Grid.RowNumber},(z2/z1)*100);

(3) Client Side Express
CurrSal + (CurrSal*MeritPct)/100

When entering MeritPct enter it as 2.6, 10.8 (not 0.026, 0.108)

[HRSue]

Re: Help with Calculated Field Expression

This information helped tremendously Gaby! The only issue is that once I enter the display format I get a NaN error in the cells with the onblur command. Short of removing the formatting (which I would love to have) I don't know what to do now? Any suggestions?

[gaby_h]

Re: Help with Calculated Field Expression

That's what the tweaking was about. Try the following:

In MeritPct add the following condition:
if (z1>0 && z2>0)
{{grid.Object}.setValue('G','AMOUNTBONUS',{Grid.RowNumber},(z1*z2)/100);}

In MeritAmt add the following condition:
if (z1>0 && z2>0)
{ {grid.Object}.setValue('G','PCTBONUS',{Grid.RowNumber},(z2/z1)*100); }

In TotSal refine it as follows:
if( PctBonus>0 or AmountBonus>0, BaseSal+(BaseSal*PctBonus)/100, 0)

to get rid of NaN
and see how it goes.

[nlights]

Re: Help with Calculated Field Expression

Sue --

If you've done any other kind of programming (C, Foxpro, Basic, etc.) picking up xBasic is not that hard once you get over the original mind twist required for any new language. I'm afraid that you have to learn xBasic if you want to create even semi-sophisticated applications with A5. Although Alpha Software doesn't provide us with any kind of reasonable xBasic help beyond the wiki, or even a few tutorials, here's a good starting point: http://www.proctorandpeake.com/train...008_Xbasic.pdf. It's a bit old, but still quite valid as a starter.

[Jim Coltz]

Re: Help with Calculated Field Expression

The Wiki does have some useful info on learning Xbasic. http://wiki.alphasoftware.com/Conten...earning+Xbasic

[nlights]

Re: Help with Calculated Field Expression

Some, yes. But have you tried to use this Wiki entry as a newbie without any clue as to the xBasic syntax and capabilities? Maybe it's just me, but I gave up after 15 minutes and started Googling, Binging and Yahooing for better sources. However, now that I have wrapped my brain around it a bit, I find myself constantly translating what I already know from other languages to what I think is in xBasic, and that helps a lot -- I'm not completely in the thick woods anymore. Would've been nice to have some hand-holding documentation to get me started in the right direction, though.

[Jim Coltz]

Re: Help with Calculated Field Expression

I found Dr. Peter Wayne's book very useful when I was starting to learn Xbasic. https://www.alphasoftware.com/shop/i...asp?PRODID=597 Although it is written for version 8 it still has lots of great information.

[HRSue]

Re: Help with Calculated Field Expression

I have never done any kind of programming at all. The closest I've come to programming is formulas in excel :o)

Thank you Gaby!! I will try that in the morning!

Thank you everyone else for providing useful resources that I will definitely look into! I was hoping there would be an Alpha Five for dummies!!

I will keep you all posted on how this works out.