6 Replies Latest reply on Apr 27, 2012 4:44 PM by philmodjunk

    Pulling a portal record on a report

    WarnerBros

      Title

      Pulling a portal record on a report

      Post

      Hello,

       

      I have a report using fields from a portal on a different layout. There are multiple lines with records in the portal, and on my report it only shows the first portal record. I would like the report to show the portal record that is at the top of the portal (because I can sort the portal to the most relevant record). Please let me know how this is possible since the report is showing a portal entry that is not relevant to the report.

       Let me know if more information is needed.

       

      Thanks,

       

      Sam

        • 1. Re: Pulling a portal record on a report
          philmodjunk

          You'll need to describe your tables, layouts and relationships in more detail before anyone will have much success suggesting a solution.

          • 2. Re: Pulling a portal record on a report
            WarnerBros

            OK,

             

            So I have table X, which is my main table. Table X is connected to table Y in a one-to-one relationship. On a layout in table X I have a portal with several fields from table Y. I made a new layout on table X that is a report of lots of fields from table X. I would like to add fields from table Y to this layout, but when I do that the records that show up on the report are the 1st records that were entered in the portal. I would like the this report to show the last entered record in the portal. Please advise or let me know if this needs further clarfication.

             

            Thanks,

             

            Sam

            • 3. Re: Pulling a portal record on a report

              Create your report using table Y. You can add fields to the report form Y and X as needed.

              Create a new layout and select the report option and use table Y...

              • 4. Re: Pulling a portal record on a report
                philmodjunk

                Ditto Jack's advice.

                Table X is connected to table Y in a one-to-one relationship.

                Well, actually, you have a one to many relationship or you'd have only one record in that portal and would have no trouble with this report. Wink

                • 5. Re: Pulling a portal record on a report

                  Filemaker relationships are many to many and it is up to the developer to define a one to many to limit the creation and showing of the related records by the handling of the layouts and records. If only one record on one side, or both, exists to establish the relationship then one might say it is a one to many or one to one. Duplicating a record with a linking field, for instance, now makes that relationship a many.

                  The relationship can be limited by use of the checkbox to not allow duplicates in an attempt to create a one to many but even this can be over ridden during an import or otherwise.

                  So reports and invoices, for instance, are easily made from the side of the relationship that has many records.

                  Aha, but what happens when a phone number link connects to many people and each person can have many phone numbers? I give up...  :)

                  To foul the waters a bit a text field can be used as a linking field and containing multiple linking ids. For instance if the files are linked by some id field and it is text on one side and a number on the other, the text field can have multiple number entries separated by a Pilcrow and the portal will show all of those records.

                  Filemaker is so flexible that it can drive you nuts...  :)

                  And a really cool thing I just learned after all these many years is that dated record can appear in many places in a report if that date field is a calculated date field. For instance, one that will produce the date of every Monday. Sort the report list by the date field and that record will show up every monday in the report. If you do it right, of course...  :)

                  • 6. Re: Pulling a portal record on a report
                    philmodjunk

                    Filemaker relationships are many to many and it is up to the developer to define a one to many to limit the creation and showing of the related records by the handling of the layouts and records.

                    @Jack, I'll have to disagree with you a wee bit on that statement though i'm just picking nits here. When you define a field as an auto-entered serial number or with a unique values validation rule, the relationships graph will correctly show that you have a one to many relationship and you do not need to implement any addtional layout or script based features to control the function of that relationship. With the "validate always" option specified, not even imports can produce duplicate primary keys in your table.

                    Aha, but what happens when a phone number link connects to many people and each person can have many phone numbers? I give up...  :)

                    You give up too easily! Use a join table for the linking and you can then base your report on the join table to get what you need, but you are making a key point on where FileMaker reporting capabilities hit a nasty limitation. Certain relational structures make for absolutely ugly report options where you simply cannot base your report on the "child" side of a many relationship and refer back through many to one relationshps to access needed data from any and all parent, grandparent, greatgrandparent tables.

                    Case in point:

                    GreatGrandparent----<GrandParent----<Parent---<Child

                    works because we can use a layout based on Child and get everything we need via the relationships.

                    GreatGrandparent----<GrandParent----<Parent---<Child
                                                                                    |
                                                                                    ^
                                                                                  Uncle

                    Gets really ugly when we need to list info from "uncle" and "child" in the same report. Either we have to use a portal for one of the two tables and accept the limitations of printing a layout with a portal or we have to create some kind of script generated "report" table that imports data from two or more dissimilar tables into the same table--with potentially very complex field labeling and layout design issues.

                    A similar challenge occurs with the first structure when you need to see all records in Parent even though some Parents are "childless".

                    Other SQL based DB's do not have these limitations.