1 Reply Latest reply on Jan 28, 2009 2:47 PM by ninja

    Sum of selected records of a child table



      Sum of selected records of a child table




      I have a money control database, with a movement table (with 1 record per transaction) and another tables that groups each transaction.


      I have a table called "items", with a calculated field called "Quantity in stock". This database is small, and I wish to sum all the "quantity" field in movement table to get the total in stock.


      In other words, I need to "translate" the SQL statement bellow to File Maker understands it:


      select sum(a.Quantity)

      from movement a, items b

      where b.id_items = a.id_items             -- primary key and foreign key

      and a.transaction_type in ("sell","buy","borrow")




        • 1. Re: Sum of selected records of a child table

          Howdy alaghi,

          I'm not sure what a money control Dbase is, and the only money movement I typically see is headed the wrong way, but I think perhaps you're looking for a Sum() function.


          For my inventory, I keep a "Quantity in stock" for each lot of material.  THere is a 'Qty in stock' for per record in Table 1 (Individual Lots)


          Table 2 (Products) has a portal bringing in the related records from table 1.  Table 2 also has a field called "Total Qty" which is a calculated field in the form of "Sum(Table1::Qty in stock)" which shows me how much of that product I have...across ALL the lots of that product.


          Table 3 (Goods Available for Sale) has a portal bringing in records from Table 2 (Products) and Table 3 has a field called TtlGoodsAFS which is a calculated field in the form of "Sum(Table2::TotalQty)"


          In my case, it makes sense to use portals between tables to sort groups of data out in one-to-many relationships.  This may not be the case in your position...I don't know.  I think that the "Sum() function is still what you're looking for... 


          If the portals don't fit your need, try using the Sum() function within the Table you're working in...it should still work even without portaling the records to somewhere else.


          Is this what you're after?