4 Replies Latest reply on Jun 26, 2017 1:48 PM by JulioSandoval

    If then Add statement

    JulioSandoval

      Hello Everyone,

       

      I have two tables:

       

      Budget Table

      Protocol Table

       

      They are linked as followed;

       

      budget.budget_id = protocol.budget_mid

       

      I am creating a calculation field in the Budget table and I need it to add some protocol charges if a protocol field = 1

       

      If (protocol.include = 1) ; sum (protocol.chargeamount)

       

      My result is that it adds ALL of the values of the charge amount and not the records where protocol.include = 1.

       

      I even tried SQL but no success;

       

      ExecuteSQL ( "SELECT sum(chargeamount)

      AS

      Budget_Sum

      FROM

      Protocol

      WHERE

      \"Include\" = 1

      GROUP BY \"Budget_MID\"

      "

      ; "" ; "" )

       

      This only gives me a ? as the result

       

      Any light on a solution would be greatly appreciated.

       

      Thank you all.

        • 1. Re: If then Add statement
          philmodjunk

          Sum is doing exactly what you set it up to do, but not what you want it to do. Sum ( RelatedTable::FIeld ) will sum the data of field from all related records, there is no feature of sum that enables it to sum selectively.

           

          In this calculaiton:

          If (protocol::include = 1 ; Sum ( Protocol::Chargeamount ) )

           

          protocol::include = 1 only checks the value of include from the first related record of Protocol. If it's 1, it sums all related record. If it's not, you get null.

           

          Sum can be used for what you need, but only by using a relationship that only matches to records where Include = 1 such as this multi-predicate relationship:

           

          budget::budget_id = protocol::budget_mid AND

          budget::constInclude = protocl::include

           

          were constInclude is a calculation field set to always evaluate as the number 1.

           

          Your ExecuteSQL function looks like it should work if you removed the group by clause.

          • 2. Re: If then Add statement
            planteg

            Hi,

             

            regarding ExecuteSQL, the query should be:

             

            ExecuteSQL ( "SELECT sum(chargeamount)

            AS

            Budget_Sum

            FROM

            Protocol

            WHERE

            \"Include\" = ?

            GROUP BY \"Budget_MID\"

            "

            ; "" ; "", 1 )

             

            The ? will be replace by the value passed as a parameter right after fields and rows character separator.

            • 3. Re: If then Add statement
              JulioSandoval

              philmodjunk the SQL worked once I removed the group but if another record has included fees, it adds it to the total. They need to be record dependent.

               

              planteg I will try your SQL command.

               

              Thank you both.

              • 4. Re: If then Add statement
                JulioSandoval

                Hi Guys,

                 

                I got it.

                 

                ExecuteSQL ( "SELECT sum(chargeamount)

                AS Protocol_Sum

                FROM

                ProtocolCharges

                WHERE

                \"Include\" = 1

                AND

                \"Budget_MID\" = ? "

                ; "" ; "" ; Budget_ID)

                 

                I totally forgot to set the second search parameter which was the budget_mid with the AND function.

                 

                Thank you both.