3 Replies Latest reply on Jun 18, 2016 11:00 AM by richardsrussell

    Portal With Related Fields

    eschillay

      All,

       

      I'm having trouble getting a portal to work correctly. If I'm in a contact record, I want to have a portal that shows everyone else at the same company, sorted by last name.

       

      Here is the file: https://dl.dropboxusercontent.com/u/15162917/TRIALCOM/ntlf.fmp12

       

      I've looks at other portals that work and I think my settings are the same, but probably not. Any suggestions on where I'm going wrong, please let me know.

        • 1. Re: Portal With Related Fields
          erolst

          So what's your issue? Obviously, the portal is showing everyone from the same company.

           

          If you want to get rid of the current person – i.e. show everyone else at the company – add a ≠ predicate to the relationship definition to exclude by primary key …

           

          Oops! Your tables don't have primary keys. That is the first thing you should add to all tables; also, add foreign key fields where necessary (e.g. a company fk to Contacts); then change your relationships to use these fields.

           

          If you want to have the portal sorted, sort ether the relationship or the portal.

          • 2. Re: Portal With Related Fields
            bigtom

            You need key fields As advised. Life will be much better this way.

            • 3. Re: Portal With Related Fields
              richardsrussell

              Absolutely you want to have primary and foreign keys in your database. Absolutely! Best database advice you'll ever receive. A primary key is the identifying code that uniquely identifies a particular record (say, 123) in its own table (say, ABC). A foreign key is that same number, except existing in a different table (say, XYZ) and used to refer back to Record 123 in Table ABC.

               

              So now, since you're already up and running with a database, how do you retrofit keys into it? First thing you have to do is create them in each table in each file in your system. FWIW, I've adopted a naming convention for key fields that uses a 2- or 3-character abbreviation of the table name (such as "Per" for the "People" table or "In" for the "Invoices" table) followed by the letters "Seq" (for "sequence number"). In addition, in the table where that abbreviation is used as the primary key, I append a "1" to it. So my main table, for "People", uses a primary key named "PerSeq1". Other tables that need to refer to it (for example, the "Invoices" table) just call it "PerSeq". (These are my own conventions, not any kind of industry standard. Use something that's meaningful to you, but have some kind of system to it.)

               

              So let's say you've created the "PerSeq1" field in "People" and specified under "Validation" that it must be numeric. Now it's time to fill it up with values. On one of your layouts associated with the "People" table, make sure that the "PerSeq1" field is visible. In Browse mode, show all records in that table. In any of those records, click in the "PerSeq1" field, go to the "Records" menu, and choose "Replace Field Contents". You'll see a dialog that gives you 3 different ways of doing this (value of current record, sequence numbers, or calculated result, this last one immensely powerful ... and potentially dangerous). You'll want the one that inserts sequence numbers, and I generally start with a 4-digit one, but that's just me:

               

              Replace Field Contents.jpeg

               

              Once you've inserted these values in all your current records, you'll want to set up "PerSeq1" to enter future values automatically. Before you do that, go to the last record in your table and jot down the value of "PerSeq1". (Let's say it's 2345.) Go into File > Manage > Database > Fields again and double-click on "PerSeq1". You'll want to set up the Auto-Enter options like this:

               

              Auto-Enter.jpeg

               

              and the Validation options like this:

               

              Validation.jpeg

               

              Do the same for all your other tables. Whenever you need to refer to a record from a different table, make sure it's the sequence-number keys that do the relating.

               

              You may next be puzzling over how to get those foreign-key numbers loaded into your various tables. If so, you can come back here and ask. Good fortune. Happy databasing.