2 Replies Latest reply on May 27, 2015 8:25 AM by philmodjunk

    Account database



      Account database


      Currently I am setting up a database for my accounting colleague. I now have a table to hold the vouchers. I wish to have a method to perform calculations of a field (summing up the 'credits' field ) by voucher no. 

      to be more detail. i want to find out all the vouchers of same voucher no. and do the sum up calculation for the credit field inside  every record of same voucher no.. should i use a button with script/calculation or a portal or anything to achieve it? how to? thx


        • 1. Re: Account database

          One way to do this is to in the vouchers table make a new field (credits_summary) that has the field type summary and is set to be a total of the credits field. 

          Then make a list type layout that shows records from the vouchers table. Delete the body part. Make a sub-summary part and select when sorted by voucher_number. In this part, place the fields voucher_number and credits_summary. 

          Show all records. Sort by voucher number.

          The report should now display a list of voucher numbers and the total amount of credit for each voucher number. 



          • 2. Re: Account database

            For more on Walrus28's method see this tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

            It also possible to set up a table of vouchers, with one record per voucher number and use a relationship to link to the actual entries by voucher_number. Then, either the same summary field (in the transactions table, not the vouchers table) or aggregate functions such as Sum() can be used to get your totals by voucher.