10 Replies Latest reply on Aug 16, 2013 4:55 PM by ch0c0halic

    2-way auto-enter calc fields?

    flybynight

      I have 3 fields: Qty, PriceEa and Total.

      Pretty simple Qty * PriceEa = Total setup.

       

      I want the users to be able to enter a number into either the PriceEa or the Total fields, and have it calculate the other.

      So, if the Qty is 10 and the user enters 2 into PriceEa, the Total field updates to 20.

      But, then if the user changes the Total to 25, I want the PriceEa field to update to 2.5.

       

      I thought I could do it with auto-enter calcs, but that doesn't appear to be working.

       

      Do I need to set it up using ScriptTriggers? Not a big deal, I was just hoping to have something that worked at the field level, rather than requiring that trigger setup on every layout.

       

      Or if you have a better way, I'm all ears!

       

      Thanks!

      -Shawn

        • 1. Re: 2-way auto-enter calc fields?
          usbc

          Shawn,

          I do a similar thing with allowing a user to enter "tons" or "pounds" into the respective field and have the other calculate or recalculate.

          I use two scripts: "Tons to Pounds" and "Pounds to Tons". Simple math (times 2000 or divide by 2000).

          Then I just use a field script trigger OnObjectExit to spark the appropriate script on the respective field.

          Chuck

          • 2. Re: 2-way auto-enter calc fields?
            DavidJondreau

            Price Ea=

            Let([

            actve.field = Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ;

            self.field = GetFieldName ( Self ) ;

            is.self.active = ( Active.Field = self.field ) ;

             

            result = If ( is.self.active  ; Self ; Total / Quantity )

            ];

            result

            )

             

             

            Total =

            Let([

            actve.field = Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ;

            self.field = GetFieldName ( Self ) ;

            is.self.active = ( Active.Field = self.field ) ;

             

            result = If ( is.self.active  ; Self ; Price Each * Quantity )

            ];

            result

            )

            • 3. Re: 2-way auto-enter calc fields?
              steve_ssh

              Hi Shawn and David,

               

              @ David:  I really like the technique/solution that you posted, David.  It is great to be able to realize the solution just within the field definitions without using script triggers.  It is really cool, and just what Shawn asked for.

               

               

              @ Shawn and David:

               

              The one suggestion that I'd like to make is for Shawn to define the desired behavior that should occur if a user modifies the value in the quantity field, and then to incorporate such business logic into David's proposed calculations.

               

               

              If I were to guess, I'd imagine that you want either one of two things to always happen when the user changes the quantity value:

               

                   1) The price each value remains fixed, and the total field adjusts to accommodate the new quantity.

               

                        - or -

               

                   2) The total field value remains fixed, and the price each value adjusts to accommodate the new quantity.

               

               

               

              As it stands now, it seems as though which of the two above behaviors you would see would be determined by the order in which you defined these AE calc fields in your schema:

               

                   - If you created the price each field before you created the total field, then the price each field will evaluate its AE calc first, and the result will be that the price each field will adjust, and the total field will remain fixed.

               

                   - If, on the other hand, you created the total field first, then total field will evaluate its AE calc first, and the result will be that the total will adjust, and the price each will remain fixed.

               

               

               

              Consequently, I'd suggest adding just a little bit more logic to the AE calculations:

               

              Rather than leaving yourself open to chance that someday one of these fields will get deleted and re-created in some other fashion, I think it would be a good idea to take David's concept and bolster it with a little bit more checking to see if the quantity field is the active field, and then tailor the calculation output accordingly.  This would avoid a sudden change of your application's behavior in the event that someone does go in and delete/recreate a field.  It would also help you out if you ever go and copy these calcs into similar schema in some other solution, where you might not be thinking about the order in which the fields were originally defined.

               

               

              Hope this helps & kind regards,

               

              -steve

              1 of 1 people found this helpful
              • 4. Re: 2-way auto-enter calc fields?
                flybynight

                Hi, David and Steve!

                 

                @ David: That is just awesome. Exactly what I was looking for.

                Just had to fix a minor type-o: actve.field vs Active.Field - missing an i in "actve" the first time.

                I also prefer camel case, so I changed it to "activeField", but that's just semantics.

                 

                @ Steve: Thanks for the input. You are right, at this point the PriceEa field was created first (because that data was imported from QuickBooks when we were gathering legacy data), so when qty is updated, the PriceEa changes, and not the Total.

                 

                At this point, our business model is that we are entering pricing at the end, after the job is produced. Someday, I hope to change to having everything actually calculating, but there are a LOT of changes that will need to happen before we get there. Goal #1 was just getting us off of hand-written job tickets and reducing the CSR's reliance on QuickBooks. Only the accounting team should need access to QB.

                 

                I'm the only one with Admin access, so I don't have to worry about anyone else changing my schema, and with our currently model, the qty shouldn't change after the job is done, so I think I like this behavior of the Total price staying put. But, I'll monitor it and get input from our CSR's once they actually have used it for a week or so.

                 

                Thanks again! I just love the TechNet community!!!

                -Shawn

                • 5. Re: 2-way auto-enter calc fields?
                  flybynight

                  David,

                   

                  Just ran into a problem with this method. When We duplicate items forward, we want to clear those fields, so I have script steps to:

                  Set Field [ Jobs::PriceEa ; "" ]

                  Set Field [ Jobs::Total ; "" ]

                   

                  But, because that field isn't active when this script fires, it doesn't actually change.

                  I got around that by adding a "Go to Field" step before each of those Set Field script steps. However (and this is a very minor issue), rather than being blank, the field that is not the last one to be set to "" will evaluate with a result of "0" rather than actually being blank. I have the fields set to "Do not display if number is zero" - but I also have conditional formatting on the field to cause it to be transparent if empty and show the field label that is directly below it. This way leaves one with the label showing and one is opaque white. Just inconsistent and it bugs me...

                   

                  I could alter my conditional formatting to also include the condition of Self = 0, but I don't want that for every field and it's easier to have the same conditional format settings for several fields.

                   

                  So, my solution was to update your formula, adding logic for when the field is not active, to detect if the other field is blank and set itself to also be blank, rather than the formlua which would result in 0.

                   

                  So here is my Total field auto-enter calc =

                  Let([

                  activeField = Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ;

                  selfField = GetFieldName ( Self ) ;

                  isSelfActive = ( activeField = selfField ) ;

                   

                  result = If ( isSelfActive  ; Self ; Case ( PriceEA = "" ; "" ; Qty * PriceEA ))

                  ];

                  result

                  )

                   

                  Long story, short (too late!)… I think I have it solved. Unless you can see any other issues, but so far it works in my testing. Sorry for "thinking out loud" in the forums, but it helps me to spell it out as I work through things.

                   

                  Thanks again!

                  -Shawn

                  • 6. Re: 2-way auto-enter calc fields?
                    DavidJondreau

                    You could also try checking the "Data" auto enter, leaving it blank. That should take care of the duplicating issue.

                    1 of 1 people found this helpful
                    • 7. Re: 2-way auto-enter calc fields?
                      flybynight

                      Nice! I'm leaving the calc the way I had it in my last post (to take care of the times when the user wants to delete the value, setting both to blank), but adding this eliminates 4 steps from my duplication script(s). When looping through a parent JobTicket to duplicate the child line items, every little bit helps.

                       

                      Gotta love the simple fixes!

                       

                      Thanks again!

                      -Shawn

                      • 8. Re: 2-way auto-enter calc fields?
                        ch0c0halic

                        Shawn,

                         

                        You say you redid a formula to get rid of the zero so it sounds like you have this set to be an Auto Enter Calculation. It looks like you've overlooked one simple operation of the Auto-Enter-Calculation. It will re-auto enter a value if you clear the field. It may look like it wasn't cleared but in reality it was and that triggered the AEC to write a new value into the field.

                         

                         

                        Let me try and clear up your misconception caused by the above behavior. A field doesn't have to be on the layout for the "Set Field[]" script step to work. If the script step is not working then there is something else going on, like the AEC triggering when the field is cleared or the wrong Table Occurrence is being used.

                         

                        Note: your comment about a Field showing "0" after Set field[]. If a field is of type number then the Set Field[""] is being Evaluated to zero because a blank value is not normally a number and the calculation requires a number result. When you want to clear a number field use the "Clear field[]" script step. If you just have to use SetField[] then use "Set Field[GetAsNumber ( "" )]" to type cast the blank value as a number.

                        • 9. Re: 2-way auto-enter calc fields?
                          flybynight

                          I'm pretty sure I understand what is going on… I probably just wasn't explaining myself correctly.

                           

                          Yes, this is an auto-enter calc.

                          It's not that it wasn't "working" - it's just that the results were not what I was expecting. I said "it doesn't actually change" but perhaps a more accurate statement would be "the end result is the same value as what we started with." Set Field changed it, but then the auto-enter rules changed it back. Of course it was doing exactly what I told it to do. Set Field was forcing it to evaluate, but since it was doing it with a script step, and not user-driven, it was not the "active" field. And David's formula stated that if it is not the active field to calculate based on 2 other fields, rather than the value (or lack thereof) set by the Set Field step.

                           

                          I am aware that a field doesn't have to be on the layout for Set Field to work. But it does have to be on the layout (even if it's off past the right divider), to use Go to Field (and hence become the "active" field). But all of that mess goes away after David's suggestion to simply check the "Data" box in the auto-enter dialog and leave that blank.

                           

                          Clear Field or Set Field to "" ends up the same. My issue wasn't the field itself, it was the other field that was set to be an auto-enter calc with this field in its definition. When that one evaluated, it calculated based on the blank field, which it read as "0" and then its own value was calculated to "0". So I could only ever "delete" one of the fields... the other one would then evaluate to "0" ... until I added that Case statement to test for a blank value in the other field.

                           

                          Thanks!

                          -Shawn

                          • 10. Re: 2-way auto-enter calc fields?
                            ch0c0halic

                            Shawn,

                             

                            Good answer. §=) This is a much better explanation, which by definition is one I am able to follow. §=)

                             

                            Congratulations, I think you have the whys and wherefores of this circular reference figured out.