OK...I have beyond frustrated right now. I thought I was a fairly savvy FileMaker user, but have been stuck on this issue for several hours. Please help me! Here's what I'm trying to do:
I have created a database to track customer payment trends. I have 2 tables. One for the customers' invoices, showing details such as days to pay, inv. #, date created, etc., and a 2nd table that contains customer data such as address, phone #, insurance #, etc. I want to creat a layout to show a summary of customer payment trends, summarized by year for the current year and the 3 before that. The way I approached this was to create a table occurance of the invoice table (I labeled "Quickviewer" and created same-named layout) Then I created 4 new fields in the invoice table labled year1, year2, year3, and year4. Year1 contains the calculation year (get(currentDate)), year2 is THE same -365 & so on. Then, I created another calc. field in the same table to calculate the actual year the invoice was created. I labled this cDoc_Year and it contains the calc. year (Doc_Date). Finally, I created 4 seperate relationships to 4 new TOs. From the Quickview table occ. to the new TOs (labled year1, 2, 3, & 4), I set up the relationships as follows:
(All 5 tables are occurance of the invoice table)
cDoc_Year = Year1 AND buyer# = buyer#
cDoc_Year = Year2 AND buyer# = buyer#
cDoc_Year = Year3 AND buyer# = buyer#
cDoc_Year = Year4 AND buyer# = buyer#
With these relationships, I expected to create 4 portals on the Quickview layout where the customer buyer# and name would be at the top of the screen. Below would be 4 portals showing a 1-line summary of the corresponding years' invoices. For example, the 1st portal would contain a summary of only 2009 data with average days to pay, total invoices, and total $ of invoices. Then there would be one for 2008, 2007, and 2006. Clicking on one would lead to more detail and so on. The way I've described is not working at all! I either get the total invoices for the customer, or various tweaks will show just 1 invoices per year. What am I doing wrong? I have no problem using a report with multiple sub-summaries, but I really wanted an interactive page for the AR staff to access a snapshot of each account's info. Comments and suggestions are greatly appreciated!