4 Replies Latest reply on Feb 2, 2014 12:06 AM by colinmatthews

    Two portals - one table

    colinmatthews

      Hi,

       

      In my database I have numerous sections; Customer, Supplier, Orders etc and in each of these sections I would like to be able to add tasks for work that is in progress.

       

      I have created a Task table and have a portal in the Customer, Supplier and Orders sections which pulls through data as it is entered in the Tasks table.

       

      The problem that I am having is that ALL tasks are pulled through to EACH portal.

       

      I have tried adding a Topic field on the Tasks table to specify the section it relates to and then filering on each portal using the Topic field but this isn't working.

       

      Does anyone have any suggestions?

       

      Many thanks,

       

      Colin

        • 1. Re: Two portals - one table
          erolst

          You need to use primary keys for your tables that are unique across the solution; otherwise, as you have (pardon the pun) noted, you'll generate e.g. three Notes records with an FK10, all of which are visible to Customer PK10, Supplier PK10 and Order PK10 .

           

          You could use a UUID, or calculate a table-specific prefixed text key in addition to your numerical primary key (like "CST" & customer_pk). Use that one to establish the relationship between Notes and your other tables (and don't forget that these keys are text fields, so your foreign key fields should be, too.)

           

          See here for more on UUID in FileMaker (or visit your local Wikipedia for general information ion the topic).

          • 2. Re: Two portals - one table
            colinmatthews

            Thank you erolst, helpful as always. I will look at the UUIDs.

             

            Additionally, I have realised that I had my FKs set as numbers that auto-incremented which was not helping. I have now changed these fields to text fields and taken the auto-increment off.

             

            The problem I have now though is that the Notes FK field does not have a number in it to match it to the PK field in the Customer (etc) section. Should this not automatically match the PK field from the Customer section as a result of the relationship?

             

            Thanks,

             

            Colin

            • 3. Re: Two portals - one table
              erolst

              colinmatthews wrote:

              The problem I have now though is that the Notes FK field does not have a number in it to match it to the PK field in the Customer (etc) section. Should this not automatically match the PK field from the Customer section as a result of the relationship?

              It will work when you create new related records – or more precisely: will automaticaly set the FK of the related record to the PK (so it BECOMES a related record) if the relationship of the portal has 'Allow creation …’ checked”). For the existing tasks you need the adjust the existing IDs (if this is the problem you mean …).

               

              Also make sure that the calculated PKs (or the UUID) in the main tables and the FK in Tasks are of (result) type text.

               

              See if this attachment helps you; it shows both approaches (but you should decide on just one …).

              • 4. Re: Two portals - one table
                colinmatthews

                I understand now. It is actually a million times easier than I was making it.

                 

                Thank you for your help again.

                 

                Colin