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

Posted on 2009-02-18
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
    LVL 28

    Expert Comment

    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

    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!  
    LVL 5

    Expert Comment

    by:Peter Harris
    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.
    LVL 28

    Expert Comment

    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


    Author Comment

    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

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

    Expert Comment

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    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…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now