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.
c0linkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lesouefConnect With a Mentor Commented:
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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lesouefCommented:
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.