Question about alpha-numeric fields

[M_Campbell]

I need to average the numbers in a form but I also need to be able to enter a not applicable (N/A) value if appropriate without the N/A being counted.

I tried keeping the fields all numeric and set up an If/Then statement which didn't work [If (DecimalField = 0) then DecimalField = null)]. I don't want to place a zero in a field representing N/A because it will be counted in the average and thus lower the average and, therefore, be inaccurate.

I tried to use Text Fields but my Avg formula didn't like it.

Any suggestions?

Thanks.

[ocen12]

if (this.rawValue == 0) {this.rawValue = null;}

Worked fine with javascript on validate for me.

Can I have a look at your "average formula"? Probably it's easier to change this one slightly so it would disregard zeros. (The zeros/null fields shouldn't be that big of a problem when it comes to decimal values... though I hate handling with variables . Don't think it will make so much of a difference when it comes to alphanumeric.)

In which event is it put into?

You don't need to upload your form... just post the formula.

And I dunnot get into the alpha numeric thing quite good...

I know a bit about higher systems than decimal, which one are you trying to take? Hex?

Decimalfields won't work with alphanumeric numbers since they just accept numbers from 0-9. I'm not 100% sure but I think same goes for numeric ones.

Though... writing a script for a Textfield that will just allow special characters should be possible...

As for the calculation... it should also be possible but most likly a lot of work...

In other words... a lot of fun for me ^^

[M_Campbell]

Ah, okay.  I am using the formula in FormCalc not Java. To be honest, I'm not a programmer. I've created limited formulas in Excel and that's about it.

The formula is: Avg (Table1.Row[*].DecimalField [*])

I'm not certain what you mean when you ask for which event.  If I have to guess, the calculations happen as I fill in the field (event?).

I tried to write a FormCalc script of: If (DecimalField = 0) then DecimalField = null) so that I could then use the AVG formula based on Adobe's Help definition:

Avg(Quantity[*])

The average value of all non-null occurrences of Quantity

I appreciate any help you are able to offer.

Thanks.

[Former Community Member]

Here's what I came up with. I have three text fields: forwards, middies, and defenders. I used hidden fields to capture the numeric values: forwards_, middies_, and defenders_. Similarly I have a hidden field for the average called avg_.

On the forwards exit event, for example, I force "N/A" for spaces and nulls. If you enter a string containing anything but an integer, I force "N/A". My code, my rules.

// form1.page1.subform1.forwards::exit - (JavaScript, client)

if (this.formattedValue.length == 0 || this.isNull) {
    this.rawValue = "N/A";
}
else {
    var regExp = /^\d+$/;
    if (!regExp.test(this.rawValue)) {
        this.rawValue = "N/A";       
    }
}

On the hidden field forwards_ calculate event I check that forwards is a number (isNaN). If it is not a number (it is "N/A"), I set forwards_ to 0 (zero). If it is a number I convert the string to an interger (parseInt).

// form1.page1.subform1.forwards_::calculate - (JavaScript, client)

if (isNaN(form1.page1.subform1.forwards.rawValue)) {
    this.rawValue = 0;
}
else {
    this.rawValue = parseInt(form1.page1.subform1.forwards.rawValue);
}

On the hidden avg_ field I walk the decision table to detrermine which fields have 0 (zeroes) and call Avg() accordingly.

// form1.page1.subform1.avg_::calculate - (FormCalc, client)

if (forwards_ eq 0 & middies_ eq 0 & defenders_ eq 0) then
    $ = ""
elseif (forwards_ ne 0 & middies_ ne 0 & defenders_ ne 0) then
    $ = Avg(forwards_,middies_,defenders_)
elseif (forwards_ eq 0 & middies_ ne 0 & defenders_ ne 0) then
    $ = Avg(middies_,defenders_)
elseif (forwards_ eq 0 & middies_ ne 0 & defenders_ eq 0) then
    $ = Avg(middies_)
elseif (forwards_ eq 0 & middies_ eq 0 & defenders_ ne 0) then
    $ = Avg(defenders_)
elseif (forwards_ ne 0 & middies_ eq 0 & defenders_ ne 0) then
    $ = Avg(forwards_,defenders_)           
elseif (forwards_ ne 0 & middies_ ne 0 & defenders_ eq 0) then
    $ = Avg(forwards_,middies_)   
elseif (forwards_ ne 0 & middies_ eq 0 & defenders_ eq 0) then
    $ = Avg(forwards_)   
endif

Finally, I set the visible field avg to avg_.

// form1.page1.subform1.avg::calculate - (JavaScript, client)

this.rawValue = form1.page1.subform1.avg_.rawValue;

Steve

[M_Campbell]

Dear Steve:

Thank you so much for your response.  I'm just not sure I can do it justice.  It makes me feel like I am a Piper Cub student pilot who just got launched into an F-16--very fast and very classy but the pilot (me) is way too inexperienced and easily confused.

Looking at the attachment using the hierarch display, I'm noticing references to both JavaScript and FormCalc (at least I think so). I would try to impliment a monkey-see-monkey-do technique but I unfortunately am not able to follow the map you've generously provided. Is there a less elegant way that will allow me to accomplish the same thing?

Thank you again.

M

[Former Community Member]

I understand. I am a 61' Mooney pretending to fly an F-22.

This is a hard problem, mixing up text with numeric fields. You could get away with using JavaScript, only, but that would make the solution more complicated. I tend to stick with JavaScript but FormCalc functions like Avg() alleviate a lot of pain.

I don't see it getting any easier, sorry.

Steve

[M_Campbell]

Okay.  I'll stop crying in my grog and start experimenting with your formulae and see if the light of dawn manages to seep through to my brain.

The FormCalc AVG works just fine if I don't have to include those dratted NAs.

M