3 Replies Latest reply on Mar 14, 2014 2:50 PM by raphthepenguin

    Change one field based on another field's content

    bobrace

      I have a DB with a number of fields, 2 of which are as follows:

       

      in_out (text - dropdown lis - In or Out)

      amount (numeric)

       

      I would like to change the amount field to display a number surrounded by parentheses (negative) based on the content of the in_out field. 

       

      Example: The transaction is for $2000.00 and it is an outgoing wire transfer. The user chooses "out" from the in_out dropdown list, the amount displayed in the amount field would display as ($2000.00).  If however, the users chooses "In", the amount field would simply display $2000.00.

       

      Can anyone tell me what calcualtions I need to do on what fields to get this to work?

       

      Thank you for your help

       

      Bob Racinowski

        • 1. Re: Change one field based on another field's content
          siplus

          DisplayAmount =

           

          Let (

           

                 da = "(" & amount & ")";

           

                 case ( in_out = "in";

           

                           Filter(da; "$'0123456789.") ;

           

                           da)

           

          )

          1 of 1 people found this helpful
          • 2. Re: Change one field based on another field's content
            bobrace

            Thank you so much for your reponse.

             

            I added this code to the calculation of the "amount" field and now when I type an amount in the amount field, the amount only displays 1.00 if the in_out field is set to "In" and it only displays 0.00 if the in_out field is set to "Out".  No matter what I type in the amount field, it only displays 1.00 or 0.00 based on the setting in in_out.  Incidently, I had to change the first part of the calc to amount as it told me it could not find the field DisplayAmount before it would even let em save the calc.  Any ideas why it is not working?

             

            Thanks.

            Bob Racinowski

            • 3. Re: Change one field based on another field's content
              raphthepenguin

              I think Siplus means you have to create a new cal field (DisplayAmount). Have a look in the file.

               

              The downside with that is you need one field to enter the Amount and another to show it...

               

              Another way would be a script that does the formating for you.

               

              Or the easiest way is to just format the field as currency where negative numbers are in brackets, then you dont need the whole in or out thing, at data entry you just write it positive or negative...

               

              Many ways to rome!