Need a summery field for some records

[ACT]

greetings,

FM12, Mac 10.8. Customers make payments using various methods. There is a field that categorizes the method, credit card payment category begins with "5-".. So there are two fields "payment_amount" and "pay_type" which exist in the daughter table for payments and charges. I would like to have a summary field for credit card payments only. How to do that? Thank you.

[eos]

Do you want a summary field for/from a report within the child table, or a sum calculation in the parent table?

[ACT]

Do you want a summary field for/from a report within the child table, or a sum calculation in the parent table?

 

 

Do you want a summary field for/from a report within the child table, or a sum calculation in the parent table?

 

Hmm... sum calculation in the parent table. 

[eos]

There are several time-honored ways to do this in FileMaker, but since you have FM12, you can use ExecuteSQL. That does away with the schema clutter necessary for those ancient methods.

 

In your Customers table, define a new calculation field and try this expression:

 

ExecuteSQL
  ("
  SELECT SUM ( yourAmountField )
  FROM aTransactionTO
  WHERE
     yourCustomerForeignKey = ? AND
     paymentCategory LIKE '5-%'
  " ; "" ; "" ; aCustomerTO::yourCustomerPrimaryKey // for some reason worked for me only when using a fully-qualified field name

)

 

If this is all Greek to you (sure looks like sort of Latin to me), take a peek into the sample file to see it in action. It has some added flexibility in that the payment type has been parametrized, too, so you can choose which payment type to sum.

SUMbySQL_eos.fmp12.zip

[ACT]

WOW! Thank you very, very much. I believe I can adopt this into my FM solution. Frankly, I think I can adopt this general idea to a couple of other things, come to think of it. Thanks for the code, file, and the education.