Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Grouping and doing aggregate functions on records

I am developing a portfolio database that has transaction records, including fund (Fund_ID), whether bought or sold, units,  amount, purchase date, etc. I want to be able to group these records on Fund_ID and sum various of the fields such as Units, Book Cost, etc. I assume this would be in a portal but am open to suggestions. I cannot find a way in which I can consolidate this information into a holdings portal, Book Cost, etc. that I can further use.

In MS Access this was a simple task using a query, grouping on Fund_ID and doing Total, Count and other aggregate functions on the requisite fields. Seems inordinately difficult with FMP for some reason.
0
c0link
Asked:
c0link
  • 8
  • 5
  • 2
1 Solution
 
lesouefCommented:
You can either create a sub-summary layout based on Fund_ID, sort records, and it will display records summarized by Fund_ID.
To get the sums, create a statistic field (ex: sum of amount) and place in the sub-summary part of the layout, it will calculate the sum for each Fund_ID, once records are sorted; use preview mode to see the result. In this case, the value is not sorted
Or
create a selfrelationship based on Fund_ID, and use this relationship for your portal. the sum displayed will reflect what you need.
I have mixed both methods in the following example.
Let me know if not clear enough
http://www.caraibscann/fichiers/summary.fp7.zip.
Also note that 2 separate tables, one for Fund_ID and another one for items would make it easier.
0
 
lesouefCommented:
0
 
lesouefCommented:
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lesouefCommented:
0
 
c0linkAuthor Commented:
The end result I want is to see one row displaying fund_ID, total units, total bookcost, to which I can add a Unit Price field and a calculation field Value based on the Total Unit and Unit Price field. The problem at the moment with this method is that for each fund record I have to show all Transactions in the sub summary rather than just the net total for these transactions.

Hope I am making myself clear, if not let me know.
0
 
lesouefCommented:
You can remove details and only keep the sub-summary by removing the body part of the layout. It will behave the same wihtout the details lines.
You can add all calculated values you like the same way I did for the sum field.
If you want to get the sum(amount) without the sub-summary and having to sort records, you can create a a calculated field = Sum ( summary::amount ).
This will make the sum of the self relationship for each ID, even if records are unsorted, but the way to get only 1 line per fund_ID is the sub-summary in any case as long as you only have a single table
0
 
c0linkAuthor Commented:
Ok, this is partly my lack of familiarity with FMP reporting. Let me work through this. Thanks.
0
 
lesouefCommented:
basically yes, the "grouped by" is done by the layout here, may look quite quite strange, but once you're used to it, rather easy, and provides clean reports in seconds.
0
 
JoJohn2004Commented:
There is another way to do this. It involves splitting your data in two tables. One table will hold the fund info and the other will hold the transaction info. Create relationships between them that filter on either bought or sold. You can make a field in Funds that is a calculation of ID and the word "bought" or "Sold" depending on the filter and a field in Transactions that is ID and TransactionType.  Then create sum() fields in the fund table that adds up the data of the desired relationship (ie, either bought or sold). Be careful of the context when you create this. You can then place the Sum() fields on a list in the fund layout that will show you what you want. I have a file I can send you if you want to post your email address.
0
 
c0linkAuthor Commented:
I would be interested in seeing that. My email is colin.king@gsworldwide.com
0
 
JoJohn2004Commented:
I have been notified that I have to post this sample in a public way so Here is my attempt at that:

http://www.nantucketisle.com/ftp/pub/Samples/funds.zip

Please let me know if you have trouble with the download.
0
 
c0linkAuthor Commented:
This should be simple but I still have problems with this. I have a set of commission records that I want to restrict on a report to only those records where there is a value in the field for Date Written. I am just confused at the moment as to what I do to achieve this. I know it has to be dead simple!
0
 
lesouefCommented:
Use the same report but reduce the found set of records by find records complying the Date Written condition
0
 
c0linkAuthor Commented:
Thanks, I sent this and then worked out what to do. I am used to using queries for this type of thing. Noticed that I can script these steps to save having manally sort record set each time I open the report.
0
 
lesouefCommented:
This can be scripted too...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now