I am trying to setup a way to view a summary (per year only) of invoice totals on a contacts layout.
In other words, a way to just see one line only for each year, 2016, 2017, 2018 etc of how a person bought. This figure will then be used to calculate a text result depending on what is is.
I figured I would setup a new table called "Yearly Spenditure". This is then connected to my invoice table by calculated year - see attached screenshot.
I have created around 20 records in Yearly Spenditure, and populated the field Year with 2016, 2017, 2018 etc.
When on the contacts layout this works so much as you only see the records when the contact has invoices attributed to those year.
I can then see exactly how much was sold per year in total (to all contacts) with this calculation field below (three summary fields for the three currencies in invoices).
However, if I am on the contacts layout how would I calculate how much a specific person has contributed to that total per year?
(Year= Year ( INVOICES::Invoice Date )
INVOICES::s_Total in Dollar +
INVOICES::s_Total in Euro +
INVOICES::s_Total in Sterling