4 Replies Latest reply on Feb 10, 2014 12:49 PM by hanstrager

    How to cary over numbers from a table to a cross tab report.

    hanstrager

      Title

      How to cary over numbers from a table to a cross tab report.

      Post

           I’m trying to learn a bit of filemaker.

           I’ve created a database to keep track of the ins and outs of a small sports club (I’ve got a bit of help here http://forums.filemaker.com/posts/ee16127691?page=1). I’ve done it so all receipts are being locked by date, season (spring 13, winter 13, spring 14, winter 14…) category (travel, equipment etc…) a radio button set to say whether the receipt was paid by the club or by another source (donations).

           So far I’ve made a cross tab report where all expenses are divided by season and then by category.

            

           My main table is called Transaction, which I’ve made a duplicate of called Transaction| sameseason

           Then I’ve made a relation between Transaction::season and Transaction| sameseason::season

           Then I’ve made a summary field called summaryTotalAmount which gives me the summary of the field called ‘net amount’

           Then I’ve made a new layout based on Transaction.

           Here I’ve changed the body part to a sub summary set to be sorted by ‘season’

           Then I’ve placed a portal onto the layout where I’ve put transaction|sameseason:: summaryTotalAmount.

           Then I’ve Specified the filter portal records fx. For travel expenses as: Transactions|SameSeason::CATEGORY ="Travel"

           Have then done this for all categories.

           This now gives me the break down of all expenses in the differnet categories pr. Season - which is great.

            

           The only thing now is I want to use this info to make an average of how much is spend on each member versus an average of how much each member is paying back to the club.

            

           I’ve made another table called members with 3 fields called ‘members’ (amount of members and ‘income’ for total income, and the 3rd for season.

           My question is how do I carry the amount of members and Income over into my cross tab report, so they’re pu into the right season and so I can use the amount of members with the generated numbers in my cross tab to get an average of how much has been spend on average pr. Member and how much has come in on average pr. Member..?

        • 1. Re: How to cary over numbers from a table to a cross tab report.
          philmodjunk

               The only thing now is I want to use this info to make an average of how much is spend on each member versus an average of how much each member is paying back to the club.

               Too bad you didn't mention that first in the other thread. The values being computed in filtered portals are "display only". They are not accessible for export nor for use in other calculations such as your averages.

               We should have used an alternate approach that did not rely on filtered portals. (those options require either adding more fields, more table occurrences or both, but produce totals that could then be accessible for such purposes.

               What are you trying to produce here?

               The average (Mean) cost in each category per season? (total cost / number of members enrolled during that season) and then the average donations computed the same way?

          • 2. Re: How to cary over numbers from a table to a cross tab report.
            hanstrager

                 Sorry, I just had the project kind of broken down into obstacles in my head, and take them as they come...sad

                 What i want is a table where every record is a season, where I can put the amount of members and and income pr. sesaon. Then carry over the information from those records into my cross tab and make and average of how much was used (using the total from my crosstab) and gained pr. member (using the income and number of members in new table).

                 I've made a quick mock up for how I kind of want my final cross tab to look like.

            • 3. Re: How to cary over numbers from a table to a cross tab report.
              philmodjunk

                   But it's how you design the tables from which you pull this data that's critical. I really don't think that you need to denormalize your data by copying it into a table where you have one record for each row shown in order to produce the report that you show. (it could be done that way, I just don't see the need and not doing that avoids issues where correcting an error fails to update the data affected by the error in such a table.)

                   What do you have in your database for tracking membership and income?

              • 4. Re: How to cary over numbers from a table to a cross tab report.
                hanstrager

                     Now I only have a table with the season, number of members that season, and income. Number of members is just a number and income is a number since it comes from an external source. Just want to use it as a quick way of seeing how every season is progressing, but completely broken down, if I fx. to show the numbers quickly to someone else. Expenses and income is kind of irrelevant if you don't see it in relation to how many members there is in the club.