Solved

Grouping and doing aggregate functions on records

Posted on 2006-10-29
15
270 Views
Last Modified: 2010-04-27
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
Comment
Question by:c0link
  • 8
  • 5
  • 2
15 Comments
 
LVL 28

Expert Comment

by:lesouef
ID: 17829136
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
 
LVL 28

Expert Comment

by:lesouef
ID: 17829140
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17829143
0
 
LVL 28

Expert Comment

by:lesouef
ID: 17829145
0
 

Author Comment

by:c0link
ID: 17832360
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
 
LVL 28

Accepted Solution

by:
lesouef earned 500 total points
ID: 17838798
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
 

Author Comment

by:c0link
ID: 17839069
Ok, this is partly my lack of familiarity with FMP reporting. Let me work through this. Thanks.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 28

Expert Comment

by:lesouef
ID: 17839107
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
 
LVL 6

Expert Comment

by:JoJohn2004
ID: 17876580
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
 

Author Comment

by:c0link
ID: 17878430
I would be interested in seeing that. My email is colin.king@gsworldwide.com
0
 
LVL 6

Expert Comment

by:JoJohn2004
ID: 17886448
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
 

Author Comment

by:c0link
ID: 17915010
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
 
LVL 28

Expert Comment

by:lesouef
ID: 17915310
Use the same report but reduce the found set of records by find records complying the Date Written condition
0
 

Author Comment

by:c0link
ID: 17919542
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
 
LVL 28

Expert Comment

by:lesouef
ID: 17925063
This can be scripted too...
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now