• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 845
  • Last Modified:

FileMaker 9 - Multiple portals to self-joined tables showing invoices per year for matching customer

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!
  • 4
  • 3
1 Solution
it won't work, summaries are only broken down by sub-summaries in layouts when properly sorted.
you can either create a dummy table for the 4 years global fields and your quickview layout,
or use calc fields for sums, averages, instead of summary fields.
for instance instead of the summary field "total of invoices" use sum(invoices_year1::amount)
and don't show sums in portals, but outside.
let me know if unclear, I am sure you can do it since I did it (only 1 portal of which you can alter the year), but is easier to do it than explain it, at least for me.
flygirl0125Author Commented:
I tried to do use calculation fields as you mentioned, with no success.  Would you mind elaborating on the dummy table solution you mentioned?  It must be easier for you to do than explain, because I am still totally lost.  Would the dummy table have 1 record and 4 fields, or 1 field with 4 records?  I think I am beginning to sound like an idiot, but I am no expert and this is giving me all kinds of trouble.  I really appreciate your help and sharing your knowledge!  
Peter HarrisFileMaker Developer at CognitiveCommented:
I think you are on the right track and should be able to get your 4 portals list invoices from different years.

You may need some adjustment on the relationships though.

By the way I would just put numbers in them eg. the field YearsAgo in INVOICES could just be a number (but it doesn't have to be to work).

I would have 4 'connection' fields in the CUSTOMERS table as follows: YearsAgo1, YearsAgo2, YearsAgo3, YearsAgo4

Also set up auto-entry in the field definition to add the data automatically for each field. Use the replace command to populate these with "Year1" etc. for each respective year for the customer records that already exist.

I would just have one YearsAgo field in the INVOICES. This field would hold 1 or 2 or 3 etc. You can do the calculation for this, just make sure it returns precisely an integer of 1 or 2 ... etc

So the relationships with be:
CUSTOMERS:tYearsAgo1 = INVOICES:YearsAgo AND buyer# = buyer#
CUSTOMERS:tYearsAgo2 = INVOICES:YearsAgo AND buyer# = buyer#
CUSTOMERS:tYearsAgo3 = INVOICES:YearsAgo AND buyer# = buyer#
CUSTOMERS:tYearsAgo4 = INVOICES:YearsAgo AND buyer# = buyer#

Relationships must involve a relationship of field to field not field to number or something else.

Your 4 portals should work, once reflecting these new relationships.
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

no pb, you're not an idiot, you just don't know how to do, period.
cogitive's way is a similar to my way roughly, you could even skip year calculation and use dates ranges for the 4 relationships.
I think I'll keep that for too-morrow (getting late here) and make a little example? how about that?
flygirl0125Author Commented:
Wow...I wish I asked this question before I wasted so much time.  I actually started out with something like the example above...using the customer table.  Eventually, I managed to get further away from the best solution.  I really appreciate both of your responses and the simple examples.  It appears that this will work perfectly, as I have a rough version already thrown together with a functioning portal.  Thanks!  I'll post back shortly with my final outcome.
flygirl0125Author Commented:
The example you provided was the perfect solution to my problem.  I was back on track in minutes.  Thanks...
well thank you to you too. Actually, congratulations are quite rare here, so I do appreciate.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now