4 Replies Latest reply on Nov 2, 2016 2:24 PM by keywords

    Help modifying calculation

    karen_joy

      Hello All,

       

      I Am using the Invoice starter solution and modifying it to suit my needs, In the Invoice Table, I want to add "Received all Items & the Current Date" and Forwarded to Accounting & Current Date" to the Info Field.

       

       

       

      1.  Payment Info Popover

       

      Case      (             

       

      IsEmpty ( Method ) ; "Set Payment Status" ;

      Method = "Forwarded to Accounting" ; Method & ¶ & Get( CurrentDate) ;

      Method = "All items received" ; Method & ¶ & Get( CurrentDate);

       

      $$PLATFORM = "Desktop" ; TextStyleAdd ( Status & "     " & Date Payment ; Bold )  & ¶ & TextSize ( Method ; 12 ) ;           

       

      Status & "            " & Date Payment & ¶ & TextSize ( "       " & Method ; 16 )

       

      )             

       

       

      2. Info Field with the following Calculation

       

       

      Status & " " &

      Case (

        not IsEmpty ( Date Payment ) and Date Payment = Get ( CurrentDate ) - 1 ; "Yesterday" ;

        not IsEmpty ( Date Payment ) and Date Payment = Get ( CurrentDate ) ; "Today" ;

        not IsEmpty ( Date Payment ) ; "on " & Date Payment ;

        Unpaid Info

        )

       

      My Problem is How can I put to my  "Forwarded to Accounting" or "All items Received" into Info field, when I choose them from my Payment info Popover.

       

      Please see attached file that I'm working.

       

      Thank you so much.

       

      KJ

        • 1. Re: Help modifying calculation
          erolst

          If I understand correctly, then the simplest way is to add an outer Case, like:

           

          Case (

            not IsEmpty ( Method ) ; Payment Info ;

            Status & " " &

            Case (

              not IsEmpty ( Method ) ; Payment Info ; 

              not IsEmpty ( Date Payment ) and Date Payment = Get ( CurrentDate ) - 1 ; "Yesterday" ;

              not IsEmpty ( Date Payment ) and Date Payment = Get ( CurrentDate ) ; "Today" ;

              not IsEmpty ( Date Payment ) ; "on " & Date Payment ;

              Unpaid Info

            )

          )

           

          You can't just add 'not IsEmpty ( Method )' as a first test to the existing case because the result calculation field (Payment Info) already has the word 'Status', so you'd get that twice.

          1 of 1 people found this helpful
          • 2. Re: Help modifying calculation
            karen_joy

            Hi erolts,

             

            Thank you for the respond, It works but how the date is not appearing, please also include the date.

             

            Thank you.

             

            KJ

            • 3. Re: Help modifying calculation
              erolst

              karen_joy wrote:

              the date is not appearing

               

              I think you just don't see the date in a one-line field because the original calc put it on the second line

              (e.g. Method & ¶ & Get ( CurrentDate ) ).

               

              Try

               

              Case (

                not IsEmpty ( Method ) ; Substitute ( Payment Info ; ¶ ; ", " ) ;

              ... rest as above

              1 of 1 people found this helpful
              • 4. Re: Help modifying calculation
                keywords

                Erolst has you on the right track for your explicit issue, but while you are at it, here is another issue you might like to address, another skill you could work on. Your Case ( ) expression is a classic example of why you should learn to use Let ( ). As it stands, you currently make up to six calls on the Date Payment field and two calls on Get ( CurrentDate ). Putting your expression into a Let ( ) function enables you to cut this to one call in each case, thus:

                 

                Let (

                [

                currentStatus = insert Status field here

                ; pmtDate = insert Date Payment here

                ; today = Get ( CurrentDate )

                ; info = insert Unpaid Info field here

                ; result =

                currentStatus & " " &

                Case (

                  not IsEmpty ( pmtDate ) and pmtDate = today - 1 ; "Yesterday" ;

                  not IsEmpty ( pmtDate ) and pmtDate = today ; "Today" ;

                  not IsEmpty ( pmtDate ) ; "on " & paymentDate ;

                  info  )

                ] ;

                result

                )

                 

                Note the pattern. You name variables in the first part to call data from the database, then use the variables further on—including in subsequent variables—thus reducing the calls on the database. You are effectively saying to FileMaker: "remember this bit of data and use it to process this field expression."

                 

                Note also that my preference is to set the result I want as a variable also, and then call that as the output part of the expression. This method enables easy checking of other variables if needed.