Alpha Video Training
Results 1 to 6 of 6

Thread: Calculated field defaults to zero

  1. #1
    Member
    Real Name
    Richard
    Join Date
    Mar 2012
    Posts
    361

    Default Calculated field defaults to zero

    I have a calculated field that does a simple field1 - field2 numerical calc. I've noticed that the calculated field defaults to zero when adding a new record. This is despite the initial value being set to Null in A5 and to nothing in my Access Db. I've also tried setting the default value in Access to Null but to no avail.

    If I remove the calculation from the field the default value is null, so it certainly appears that by having a calculation defined I get a zero.

    Does anyone know how to avoid showing zero in a calculated field.

    Regards, Richard.

  2. #2
    Member
    Real Name
    Jim Coltz
    Join Date
    Oct 2005
    Location
    Western Pennsylvania
    Posts
    600

    Default Re: Calculated field defaults to zero

    Change your calculated field to use a custom function like result_of(field1,field2) then in the javascript functions define a function
    like
    Code:
    function result_of(field1,field2){
    	if(field1-field2 != 0 && field1-field2 != null){
    		var c = field1-field2;
    		return c;
    	}else{
    		return '';
    	}
    }
    Or, if you choose, you can do it all in the calculated field:
    Code:
    if(field1-field2 = 0,'',field1-field2)
    Jim Coltz
    Alpha Custom Database Solutions, LLC
    A5CustomSolutions.com
    jimcoltz@a5customsolutions.com

  3. #3
    Member
    Real Name
    Richard
    Join Date
    Mar 2012
    Posts
    361

    Default Re: Calculated field defaults to zero

    Thanks Jim, that's a good idea... although in my case 0 may be a valid result from the calculation so I would want to show the result as 0 if field 1 and field 2 aren't null and the result is 0. It's a simple mod of your example to do this so thanks.

    Another idea I came up with is to use a show/hide expression - to hide the calculated field and then display it when field1 and field2 aren't empty.

    Regards, Richard.

  4. #4
    Member
    Real Name
    Fodor Mihai
    Join Date
    Nov 2011
    Posts
    30

    Default Re: Calculated field defaults to zero

    Another idea is to put this :

    alltrim(str( convert_type(<value>,"N") ,250,2,"B")) - 2 decimals

    alltrim(str( convert_type(<value>,"N") ,250,0,"B")) - NO decimals

    in DISPLAY FORMAT in the DISPLAY SETTINGS on the CALCULATED FIELD.

    Basically, it means displaying any number with 2 decimals and NOTHING if the number is 0.

    Mike.Blank if zero.jpg

  5. #5
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,399

    Default Re: Calculated field defaults to zero

    This is ugly, but I think it does what you want.
    I assume a dialog with 3 numeric fields, and a button to initiate the check.

    Code:
    if({dialog.Object}.getValue('FIELD1')=='' ||{dialog.Object}.getValue('FIELD2')==''){
    
    {dialog.Object}.setValue('FLDRESULT','' );
    alert('you have a null value, no math will be performed');
    }
    else{{dialog.Object}.setValue('FLDRESULT',{dialog.Object}.getValue('FIELD1')-{dialog.Object}.getValue('FIELD2'))}

  6. #6
    "Certified" Alphaholic
    Real Name
    Gregg Schmidt
    Join Date
    Mar 2001
    Location
    Milwaukee
    Posts
    1,399

    Default Re: Calculated field defaults to zero

    If you want to make sure only numbers are processed, this code is better:

    Code:
    if(isNaN({dialog.Object}.getValue('FIELD1'))||{dialog.Object}.getValue('FIELD1')==''||isNaN({dialog.Object}.getValue('FIELD2'))||{dialog.Object}.getValue('FIELD2')=='')
    {{dialog.Object}.setValue('FLDRESULT','' );
    alert('something is NaN');}
    else{{dialog.Object}.setValue('FLDRESULT',{dialog.Object}.getValue('FIELD1')-{dialog.Object}.getValue('FIELD2'))}

Similar Threads

  1. Conditional Defaults - Field Rule
    By TerriH in forum Alpha Five Version 5
    Replies: 3
    Last Post: 09-28-2006, 01:58 PM
  2. Use of calculated field in calculated fiel
    By pinzgauer in forum Alpha Five Version 5
    Replies: 6
    Last Post: 07-08-2004, 12:06 AM
  3. Field Rule defaults
    By Richard Froncek in forum Alpha Five Version 5
    Replies: 2
    Last Post: 02-19-2003, 12:41 PM
  4. Calculated Fields vs. Calculated Field Rule
    By Rhett Scott in forum Alpha Five Version 4
    Replies: 12
    Last Post: 07-07-2002, 05:58 AM
  5. How do I force a calculated field to be calculated
    By Susan Avery in forum Alpha Five Version 4
    Replies: 5
    Last Post: 11-18-2001, 03:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •