Calculation using dates and sales data
I have a database with inventory items in one table, and sales data in another table. In the inventory table, which I have linked to sales, and can see the sales data in one of my layouts, I want to create a calculation field that will give me sales totals for each month individually. So, it will look at sales data, look for the given month, and extract the total sales for that month. Then I also need to create a field that will calculate year-to-date for the item.
I have tried various if statements (based on examples found searching the internet), but have had no luck getting anything to work.
I am new to FileMaker (although a very experienced user), but have pretty much mastered Bento. My new databases were imported from Bento.
I am using FileMaker Pro 10, latest version, on a Mac running Snow Leopard.
Here is a screen shot showing the related sales data, and the month fields on the right that I want to automatically fill with the correct info.