5 Replies Latest reply on Feb 3, 2017 3:10 PM by philmodjunk

    Relationship help

    ecohomes

      Hi guys,

      Sorry I do not know the terminology of what I need.

      I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type.

      Eg:

      Supplier    - Lead Type - Postcode - Count

      Supplier A - Back Data - AB            - 20

                                           - BB            - 155

                                           - CB            - 30

       

                           Profile     - AB             - 55

                                          - BB             - 27

      My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type.

      Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships.

      Thanks

        • 1. Re: Relationship help
          philmodjunk

          What you post does not fully match the screen shot of your relationships and some of the terms differ. I'm sure that it's clear to you but not necessarily to others. Not sure why you have TWO join tables here.

           

          Working from a theoretical context if you have these relationships: (----< means "one to many")

           

          Suppliers----<Join>---PostCodes

           

          A field in Join would identify the type of lead.

           

          You can get your counts by using methods that do not rely just on the relationship. ExecuteSQL( ) could be used to get the totals for each type, a summary "count of" field in PostCodes and a set of one row filtered portals to PostCodes that each filter for a different type could give you the counts.

           

          Another approach would be to add a table of Lead Types with only one record for each type of lead (4 records in all) and use these relationships:

           

          Suppliers----X----LeadTypes>-----Join

           

          Suppliers::anyField X LeadTypes::Anyfield (literally any non empty field can be used in both tables)

          LeadTypes::gSupplierID = Join::_fkSupplierID AND

          LeadTypes::LeadType = Join::LeadType

           

          In this case, gSupplierID is a global field in Lead types that you set via script (from the OnRecordLoad script trigger) to that of the current supplier's record (Can't use this method in a list view). A calculation field in Lead Types can then be used to count the join table records for the same supplier for a given lead type and a portal to LeadTypes can then show the counts for each type.

          • 2. Re: Relationship help
            ecohomes

            Thanks for the response. I forgot to say that I cannot use ExecuteSQL as I'm stuck on FM11 and that was introduced in 12.

             

            I understand what I said doesn't match my relationship otherwise it would work and I wouldn't need to ask for help lol.

             

            Another approach would be to add a table of Lead Types with only one record for each type of lead (4 records in all) and use these relationships:

             

             

            This is what I have done (or tried to do) the "Lead_Type" table has a field called "lead_type" and in this table I have created the 4 records for the 4 different types, this has a join table linking to the suppliers table. The second join table links the postcodes table (150 records each line is first half of UK postcode - 2 letters) to each lead type. That is why I have 2 join tables.

            • 3. Re: Relationship help
              philmodjunk

              Then use the relationships and scripting that I have suggested for the second option or use the filtered portal approach that I also described in the first option.

               

              And when asking a question about an version older than the most recent release, PLEASE tell us what version you are using in your very first post. This can save us a lot of trouble as we might otherwise suggest an approach that does not work in your version. Sometimes helpful people even volunteer example files they've created and might share one that is of the wrong version to be helpful to you.

               

              This is what I have done (or tried to do)

               

              But it's not a join table and the match fields are different.

              • 4. Re: Relationship help
                ecohomes

                I have given that a go but I must be doing something wrong. Is this how you meant to setup the relationship? I set the gSupplierID to be a global field and set the onRecordLoad script step to assign the current supplier. I cannot work out what you mean with the calculation field:

                A calculation field in Lead Types can then be used to count the join table records for the same supplier for a given lead type and a portal to LeadTypes can then show the counts for each type.

                 

                I set a field called supplier_count in the Lead_Types table which is a calculation but I left the calculation empty as I'm not sure what to put there.

                 

                 

                .relationship.jpg

                • 5. Re: Relationship help
                  philmodjunk

                  The relationships look correct and I can see that the global field appears to have global storage specified.

                   

                  But is that global field being assigned the correct value?

                   

                  And do you have calculation fields in the Lead Types table set up correctly to calculate the needed counts?