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

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

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)

Mohammed.

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

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)

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)

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)

Thank you for your replay ...

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

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)

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)

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)

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)

it works like a charm!

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

Mohammed