8 Replies Latest reply on Oct 11, 2014 10:40 PM by philmodjunk

    Portal necessary to create summary from another table?

    WillEvans

      Title

      Portal necessary to create summary from another table?

      Post

      I'm trying to create a mini summary of the most recent transactions on what I call our homepage. The 'homepage' is an independent/unrelated table that all users land on when they log on, and has a bunch of buttons to useful layouts. 

      To create this summary, I was thinking I would just create a portal to the table where the transactions are recorded, and stick it on the homepage (then only display 5 records sorted by date). However, I discovered the tables need to be related in order to create a portal.

      Do I need to find a way to relate my homepage appropriately to this table? Or is there a simpler way to create this summary report? 

        • 1. Re: Portal necessary to create summary from another table?
          philmodjunk

          Your are correct that a portal requires a relationship. Why would that be a problem? Many "dashboard" type layouts use portals and relationships to display data and to display different data for different users. In FileMaker 12 or later there are also ways to do much the same thing with Calculation fields that use ExecuteSQL.

          You'll need to describe the data and tables involved in much more detail.

          • 2. Re: Portal necessary to create summary from another table?
            WillEvans

            Thanks for your reply PMJ

            So I created a relationship between the dashboard and the relevant table. I created a new primary key (with serial number) on the dashboard table, and then a separate foreign key in the layout where I want the portal to be from. I established a relationship between these key fields. I then created the portal on the dashboard, along with some fields from that portal table, but it's just appearing as blank at the moment. No records are appearing when I enter browse mode. 

            What could I be doing wrong? Let me what info I can provide you with to better answer my query. 

            • 3. Re: Portal necessary to create summary from another table?
              philmodjunk

              If no records are appearing, then there are no records in the portal's table where their foreign key field's value matches the value of the current record's primary key. Just defining the relationship does nothing to assign values to your foreign key fields and existing records will not auto-enter a primary key just because you added a new field that auto-enters a serial number.

              You may need to use Replace Field Contents to assign serial numbers to the existing records of your Primary Key field.

              • 4. Re: Portal necessary to create summary from another table?
                WillEvans

                Awesome, thanks PMJ, I got it to work! I had to go in and replace field contents in the foreign key field too so that it matched the parent key field (of which there is only one record). So I was just replacing the content of all the foreign key fields with the number '1'. That's ok right? 

                • 5. Re: Portal necessary to create summary from another table?
                  philmodjunk

                  The gets you started, but you'll need to figure out a better way to do this going forward so that you don't have to keep doing this for new records that you add.

                  If you will only have one record in your Dashboard table and it always needs to match to all records in the related table, you can use the Cartesian Join operator (X) in the relationship and then any record in your Dashboard table will match to all records in the related table.

                  • 6. Re: Portal necessary to create summary from another table?
                    WillEvans

                    Awesome PMJ, I tried the Cartesian relationship and it worked! 

                    Just so I can get my head around what you're saying, could you give an example of what 'other methods' rather than manually entering stuff into the foreign key field so that it matches the value in the primary key field that I could use? 

                    • 7. Re: Portal necessary to create summary from another table?
                      WillEvans

                      Just run into another problem, how do I get the portal to show only the have most recent records? I have sorted by date Descending, but I can't find how to restrict it to 5 records. Is there a way I can do this? 

                      • 8. Re: Portal necessary to create summary from another table?
                        philmodjunk

                        A portal with exactly 5 rows and no scroll bar would be restricted to 5 records. Sort the portal (or the relationship) and you get only the most recent 5.

                        One standard way to automatically link new records to the current parent is by selecting "allow creation of records via this relationship" for the child table in the parent to child relationship. (Double click the relationship line to open a dialog on where you can select this option.) With that option selected, you get a blank "add row" in the portal below the last record in the portal. If you then enter data into a field in that "add row", FileMaker copies over the primary key value of the current layout record into the new portal record--which automatically links the new portal record to the current layout record.

                        Another method is to use a script to update a global variable with the current parent record's primary key. Then you go into field options on the child table's foreign key field and set it to auto-enter the value of that global variable.

                        And sometimes we just format the foreign key with a value list of primary key values and an associated name or description.

                        You might check out the Adventures in FileMaking series. Adventures 1 and 2. They are free to download.

                        Adventures in FileMaking #1 - Conditional Value Lists (includes details on how to set up a basic field based value list)
                        Adventures in FileMaking #2 - Enhanced Value Selection (what to do when a simple value list won't cut it.)

                        Caulkins Consulting, Home of Adventures In FileMaking