3 Replies Latest reply on Dec 12, 2014 5:10 AM by philmodjunk

    Return multiple rows from Table B in a Table A layout

    Stu412

      Title

      Return multiple rows from Table B in a Table A layout

      Post

      Hi there

      Still a FM noob!  I have two tables, we'll call them Names and Financial.  I have 30 records in Names and around 2000 records in Financial which are sales made to the Names.  As a side, I've created a list layout based purely on Financial which provides summary data using GetSummary and now I need to take things a step further.

      I've related the two tables by NameID (PK Names, FK Financial) and I want to create a layout based on Name showing the summary data I already have.  The relationship type is '=' with crows feet either end, suggesting many to many.  (Not sure if I need to make this an 'x').

      The aim is to have 30 records I can flip through, each with a summary of the customer data as it's required by year and type.  My current layout is obviously based on 2000 records - it looks good but isn't ideal for end users.

      I've tried adding a portal to a form by name layout and using the GetSummary fields from the Financial table, but these appear blank, despite the existing relationship.  

      In my previous existence working with Crystal Reports, I'd have added a sub report based on the list layout I have and linked it by NameID, but things are different here:)  

      Hope that all makes sense, thanks in advance.

        • 1. Re: Return multiple rows from Table B in a Table A layout
          philmodjunk

          The method you have used to summarize your data has to work from a layout based on Financial, not Names. You can set up a list view layout with fields from Financial in the body and fields from Names in the header. You can then put buttons in the header that move you from name to name record followed by pulling up the matching set of records on your Financials layout.

          Portals in FileMaker do not support sub summary layout parts nor do GetSummary Function calls (which rely on found set status/sort order to work) work in them.

          There are, however, other ways to summarize data that could work from the context of a Names layout.

          The relationship type is '=' with crows feet either end, suggesting many to many.  (Not sure if I need to make this an 'x').

          I don't see a reason to make this an "x" relationship (Cartesian Join) as that matches any record in one table to all records in the other. You have "crows feet" on both ends because you haven't selected any field options on the match fields that limit the value of that field to unique values within the table. So a given record in Names might match to multiple records in Financials and a given record in Financials might match to multiple records in Names. This isn't really a many to many relationship, just one that incompletely defined at the data level. I recommend that you define an auto-entered serial number field in Names and use it to match to a number field in Financials to set this up as a one Names record to potentially many Financials record. (I'd guess that you may have set up the relationship to match records by name and that is NOT a good idea here as names are not unique and people (and companies) change their names for a number of reasons.)

           

          • 2. Re: Return multiple rows from Table B in a Table A layout
            Stu412

            Thanks for the info.

            I've created button navigation from a customer 'home page' to the list report which searches only on related records, therefore returning just those for that particular customer.  I can then navigate back by using a further button/script combination which seems to work. I've changed the NameID to auto number as well.

            This is good to test the concept for what I'm working on, but as you mentioned, ideally I'd like the summary data for say, 4 years, on a customer home page.  The list view I get shows all customers and their associated annual data, one beneath the next.  What I need is to show one customer (say ID 1010) and their annual data only on the screen, with a nav button to move to the next customer (ID 1011).

            What is it that I'm missing on the list layout which would show only one customer record (in a leading sub summary group) at one time?

            Thanks

            • 3. Re: Return multiple rows from Table B in a Table A layout
              philmodjunk

              You would simply need to use a relationship that matches only to records for a given customer along with Go to Related Records to pull up just the records related to that customer. (You would start from a record for that customer on the "names" table.)

              or you can perform a find on the summary report layout for only those records related to a given customer, followed by sorting the found set just produced to get your summary sub totals.

              Here's a thread of scripted find examples: Scripted Find Examples