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

Posted on 2009-02-18
Medium Priority
Last Modified: 2012-05-06
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!
Question by:flygirl0125
  • 4
  • 3
LVL 28

Expert Comment

ID: 23673255
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.

Author Comment

ID: 23674572
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!  

Expert Comment

by:Peter Harris
ID: 23674814
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 28

Expert Comment

ID: 23675571
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?
LVL 28

Accepted Solution

lesouef earned 2000 total points
ID: 23676206

Author Comment

ID: 23683706
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.

Author Closing Comment

ID: 31548371
The example you provided was the perfect solution to my problem.  I was back on track in minutes.  Thanks...
LVL 28

Expert Comment

ID: 23719298
well thank you to you too. Actually, congratulations are quite rare here, so I do appreciate.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question