8 Replies Latest reply on Oct 29, 2013 10:49 PM by MohammedAhmed

    how can I use (if) with (sum) and (and)

    MohammedAhmed

      Title

      how can I use (if) with (sum) and (and)

      Post

           Hi guys,
            
           Im new in Filemaker and I facing a difficulty to write a function.
            
           I have two tables
            
           the first table has 4 fields:
            
           ID, price, statue, value
            
           the second table has 2 fields:
            
           ID, Result
            
           there is a relationship between table1 and table2 in the ID fields (many to many)
            
           I am trying to write a function in table2 in the (Result) field but I couldn't get the right result that I'm looking for ... it seems to me that the function (and) desn't work.... I don't know honestly.
            
           This is my function, please correct me if I'm wrong:
            
           If ( table1::statue = "b"  and  table1::price = 26 ;Sum(table1::value); 0)
            
           Thank you for your help
            
           Mohammed.

        • 1. Re: how can I use (if) with (sum) and (and)
          philmodjunk

               Here is a link to a thread that describes two methods that work for this: Sum_Calculation based on condition

               If you are using FileMaker 12, there is another option: ExecuteSQL, that can selectively sum your related records.

          • 2. Re: how can I use (if) with (sum) and (and)
            MohammedAhmed

                 Hi PhilModJunk

                 Thank you for your replay ...

                 as I read about ExecuteSQL the result must be text not numbers ...and I am trying to get a number result ...

                 The first method in the link works for me ...

            Thank you for your help ...

                  

                  

            • 3. Re: how can I use (if) with (sum) and (and)
              philmodjunk

                   ExecuteSQL can be used for this purpose even though the result is of type text.

              • 4. Re: how can I use (if) with (sum) and (and)
                MohammedAhmed

                     Thank you for your replay ...

                     I read a lot ExecuteSQL and I couldn't get the function works.

                Could you please help me to convert the function below to ExecuteSQL?

                     If ( table1::statue = "b"  and  table1::price = 26 ;Sum(table1::value); 0)

                      

                     Thank you very much

                     Mohammed

                • 5. Re: how can I use (if) with (sum) and (and)
                  philmodjunk

                       ExecuteSQL ( "SELECT SUM ( \"value\" ) FROM \"Table1\" WHERE \"statue\" = 'b' AND \"price\" = 26" ; "" ; "" )

                       or you might use:

                       ExecuteSQL ("SELECT SUM ( \"value\" ) FROM \"Table1\" WHERE \"statue\" = ? AND \"price\" = ?" ; "" ; "" ; "b" ; 26 )

                  • 6. Re: how can I use (if) with (sum) and (and)
                    MohammedAhmed

                         Hi  PhilModJunk

                         Thank you very much for the functions ... I tried them but unfortunately they gave me a wrong result ...

                         it seems that ExecuteSQL does not recognise the relationships between the two tables ... I have a relationship between the two tables in the field ID ID=ID.

                         in table1 I have multiple records for one Id but in table2 I have a unique Id ex:

                         Table1::ID           Table2:ID

                         33                        11

                         33                        22

                         44                        33

                         77                        44

                         33                        55

                         77                        66

                         66                        77

                         since ExecuteSQL does not recognise the relationships, is it possible (in ExecuteSQL) to do three conditions like this :

                    SUM (value) in (Table1) 

                    IF (statue) = (b)

                    and (price) = (26)

                    and (Table1::id) = (Table2::id)

                         Thank you very much for your help ...

                         Mohammed

                    • 7. Re: how can I use (if) with (sum) and (and)
                      philmodjunk
                           

                                it seems that ExecuteSQL does not recognise the relationships between the two tables

                           It does, but you have to specify the relationship as part of the SQL. Since your example did not specify a relationship, my SQL could not specify that either. You'd eitehr use a join clause to define the link between tables or specify one more comparison in the WHERE clause to specify the foreign key value of the records to be summed--which is exactly what you show in your preceding post.

                           ExecuteSQL ("SELECT SUM ( \"value\" ) FROM \"Table1\" WHERE \"statue\" = ? AND \"price\" = ? AND \"ID\" = ?" ; "" ; "" ; "b" ; 26 ; Table2::ID )

                           This assumes that Table1 is the "child" table--the table on the "many" side of a "one to many" relationship.

                      • 8. Re: how can I use (if) with (sum) and (and)
                        MohammedAhmed

                             it works like a charm! 

                        Thank you very much for your help and sorry for the misunderstanding of the relationship.

                             Mohammed