4 Replies Latest reply on May 20, 2017 9:26 PM by mz5005

    Many to one relationship issue


      I need to compare a table with about 15K (not unique) Companies to a table of (a hundred) unique LargeCompanies. (not unique = one company can be listed several times). The purpose is to flag what Companies are a Large Company. The 2 tables are linked together on the CompanyName/LargeCompanyName fields. This is of course a many to one relationship (if any relationship at all).


      My script works well in the sense that it does what it should do, but ONLY for the first occurrence of a company in the Companies table. The other times the same company appears in the Company table, the flag is not set.


      In the script I have set the Sort order to another field than CompanyName to make sure the records-order is not by CompanyName. That doesn't help.


      Q: do I misunderstand something about (1 to Many) relationships here, or is it really a problem in my script?


      Script attached - flag is "Active=No" - field names vary slightly from above (changed for sake of clear explanation)


      Staring on this for hours already - any advice greatly appreciated!

        • 1. Re: Many to one relationship issue

          I believe your problem might be here.  I could tell more if I could see your relationship graph.



          #If the company is a large company then set Active=No

          If [ Get(FoundCount)>0 ]
          Set Field [ EAInfoAll::Active; "No" ]

          End If



          When your found count is > 1, the set field will only flag the first record to "No"


          You would need a replace field contents here instead.

          • 2. Re: Many to one relationship issue

            Hi MZ,


            I understand that in this case you will mark that a company is "a large company" by relating them to them self in the "large company" database. Correctly understood?


            Or will the record in the "Large company" table have records dividing in branches, size larger/smaller than X or?


            If the first assumption is correct, that you only use this relationship to mark the company up against it selv to mark that it is a large company ... then a simple attribute field could be enough ... something like Large_small_ln where 1 is large and 0 is small. Or any other attribute that is helpfull to you.


            But I might have got you wrong.


            If it is because a large company might "own" many other companies you could consider just having one company table and a joint table to connect daughters to their mother company. Thereby only maintaining the same attributes (fields) in one table and only having one layout to care about?

            • 3. Re: Many to one relationship issue

              thanks philip and carsten.


              @philip: never knew that - one learns every day :-) thanks will try that out tomorrow (night here now)


              @carsten - funny you mention this - I was just going to post a new question about exactly that.

              see new question in board in 10 min. woud be great to hear your view there too.

              • 4. Re: Many to one relationship issue

                Problem solved Philip, thanks again!