Thank you for your reply PhilModJunk
This seems so complicated. Is there any other simple way to do it? I was under an impression you can use something like DCAOUNTA function in Excel. Point the table, and sum based on few conditions?
The simplest solution for the new developer is to set up the needed relationships using match fields. Since a Sum function or a summary field defined in the related table both return aggregate values based on all related records, you can use the relationship to match to only those records you need for a total.
And if you only need to display such subtotals, the filtered portal option is even simpler.
I'll give it a try and will post an update.
Thank you again PhilModJunk ;)
Do you think I could send you my database, could you have a look into it?
This is doing my headache now..
When paying £££ for FM and reading all this stuff on FM website 'how simple to use it is' I was under impression I'll have this pretty much sorted out in no time, especially that I am not trying to design anything fancy here, just stuff which I was able to do in Excel..
All I want to do is this:
My table have i.e the following Fields:
Unique ID Invoice Date Total Value Supplier Name Expense Type
00001 1 Jan 2013 $100 xxx Materials
00002 15 Feb 2013 $150 yyyy Tools
00003 18 Feb 2013 $200 zzzzz Tools
00004 2 Mar 2013 $100 aaaa Tools
All I initially want to achieve is a TextBox on my Layout which will show the total of i.e all Invoices paid for Tools in February which in this case is $350.
At later stage I would like to have this TextBox to be driven by a DropList where I can choose the month availiable in the Invoice Date column.
This is killing me :(
assuming that Invoice date is properly a field of type date like it should be, add this calculation field to your table:
Invoice date - Day ( Invoice date ) + 1
Call it cMonth.
Define a date field, SelectedMonth in your layout's table with this auto-enter calculation:
self - Day ( self ) + 1
Clear the do not replace existing value check box.
Define this relationship, but using your Tutorial: What are Table Occurrences? names in place of mine:
LayoutTable::SelectedMonth = RelatedTable::cMonth
Define a calculation field in Layout table as: Sum ( RelatedTable::cMonth )
Put that field on your layout and you can get a total for any month/year date you select in the SelectedMonth table.
LayoutTable and RelatedTable can be two occurrences of the same table linked in a self join relationship.