Using the Sum Function based on year

[Karma]

I am making a database that holds information of businesses who have put advertising in out magazine (prices/ ad size etc).

The main divider of this database is year and category.

I have a drop down menu that lists all the years that exist in the advertisers table (using the 'from table' value list).

Next to that i have a field where i want to display the total revenue for the selected year.

Attatched is a picture of the tables and their relationships.

[Reed]

So from whatever layout you want to display the sum, you will need a relationship to the table that contains the revenue data. Each revenue line item should also contain a date. You can use the Year() function to extract the year from the date field if you don't have a standalone year field.

The relationship should be from a global text field to this year field. Now in the table with the global create a calculation field that uses the Sum() function like this

Sum(relatedTable::revenueField)

The value of this field will update based on the year input in the global. You can still use the value list that contains all possible years to create a popup list.