Do you want to see just the total for the current month or do you wan to see a list of totals, one for each month over the past X months?
Both are possible.
A useful calculation to use for sorting to group by month and to use for matching records by month in a relationship:
DateField - Day ( DateField ) + 1
This computes the date for the first day of the month of the date entered into DateField.
An fixed table showing me all the sum sorted by month would be great like:
Month Total received
I didn't figure out how the formula you gave works and where I am supose to write it.
Sorry for my very basic level.
1 of 1 people found this helpful
I am assuming that you want that in a portal on your dashboard layout. To do that without SQL you will need a table of months.
To start, you will only need one field in months, though you can add a primary key field as well if you want to. Make sure that you have a field named Month defined as type date. You may have intended AUX_Month for this purpose, but note that I am describing a different design for that table.
You'll need one record per month entered into this table. Enter the date for the first of each month. This can be done by hand, or you can create a script to create these records.
Define this calculation field in Payment, cMonth and select "Date" as its result type:
Date - Day ( Date ) + 1
Now use the button with two plus signs to make a new occurrence of Payments, Payments|byMonth. link it to your new Months table like this:
Payments|ByMonth::cMonth = Months::Month
Now add a calculation field, cMonthTotal, to Months defined as:
Sum ( Payments|ByMonth::Amount Paid )
with a number result type.
Link Months to company Dashboard in a Cartesian Join just as you linked Invoices to it in a Cartesian join.
You can now put a portal to Months on your dashboard with the Month and cMonthlyTotal fields in the portal row to show the total payments received for each month. You can use date formatting to format Month to display only the Month and year as the day will not be significant. (By using a field of type date, you have a value that will sort correctly any time you need to sort this data by month.)
No words to say thank to you for soooo much deep details you gave.
Even with definitly no FM skills I could follow you instructions and finally works!
Thanks a thousand times!
God bless you.