7 Replies Latest reply on Jul 19, 2015 8:34 AM by mz5005

    One to many relationship problem

    mz5005

      Hi,

       

      a (probably) newbie question.

       

      Have worked with MS Access before, know theoretical concepts of normalizing data etc, but obviously not a pro.

       

      I have read the Help guide to the end, looked through the Portal related subjects on this forum but can't find a solution.

       

      Q: i have a 1 to many relationship which does not work how it should. Can anyone point me in the right direction?

       

      The simplified problem with table structure:

      the system needs to keep track of what customer has received what document. One customer can receive more than one document; the same document can be send to more than one customer (so it’s many-to-many but business wise we are only interested in the What Customer Received What Doc side)

      everything works well… until a customer receives more than 1 document … then the first document sent event is overwritten and replaced by the newer one. (I am not only talking about the presentation in the portal, the data is overwritten in the relevant table).

      table: Customers   field: CustomerID  / table: Mailings   fields: CustomerID, DocumentID, DateSent

      Relationship: Customers>CustomerID —> Mailings>CustomerID  / Allow to create records in Mailings

       

      CustomerID is obviously the link to all customer info like address etc

      DocumentID uniquely identities the document

      The combination CustomerID + DocumentID + DateSent uniquely identities one document sending event.

       

      My key problem is that I, after reading back and forth the Help guide, still not seem to understand well, is how to handle a 1-to-many relationship like described above:

      it is not in my view a multiple field relationship since the Customers table has only one for this case relevant field CustomerID

      if the above is true, then what do I do wrong with the Tables definition and/or relationships?

       

       

      Any help greatly appreciated, am really stuck!

        • 1. Re: One to many relationship problem
          Vyke

          You need a many to many relationship.

           

           

           

          Create a join table to put between customer and document. The table will capture the customer ID and the document ID. Your portal will be based on the join and pull data from the document table.

           

          Here is a very very simple example file for you.

          • 2. Re: One to many relationship problem
            flybynight

            "…we are only interested in the What Customer Received What Doc side"

             

            That statement is a little ambiguous.

            Do you want to look from customers and see a list a documents they have received?

            Do you want to look from documents and see which customers have received that document?

             

            If you are only concerned about the first, let's take a look at that. If customer A and customer B both receive document 27, are you OK with having multiple entries in your documents table (with different DocumentID values), each representing document 27?

            If you think there is ever a possibility that you will need to track/look/report from either direction, or will not want duplicate records for the same document, then you need a join table. Call it CustomerDocuments or Received or whatever you want. All you need is an ID field for that table, and then foreign keys to your CustomersID and DocumentID fields.

             

            All that to say… it sounds like there might be an issue with your script. How are you creating the related record? Perhaps there is something going to the wrong portal row and setting the fields? Overwriting the existing record instead of creating the new one?

             

            HTH,

            -Shawn

            • 3. Re: One to many relationship problem
              coherentkris

              ms5005 said

              "One customer can receive more than one document; the same document can be send to more than one customer (so it’s many-to-many but business wise we are only interested in the What Customer Received What Doc side)"

              This says that a join table is needed.

              If the fk's in the join table are set correctly then ...

              From a layout based on documents with a portal to customers you will see the customers that are related to a document in the portal.

              From a layout based on customers with a portal to documents you will see the documents that are related to the customer in the portal.

               

              If you don"t need to see the Customer>Document portal than don't put it a customer layout.

              • 4. Re: One to many relationship problem
                Extensitech

                As noted, the relationship you need is many-to-many, even though you're only going to display if from one side.

                 

                Vyke's suggestion to add a join table is the conventional way to create a many-to-many relationship. This involves a table containing a customer key and a document key, so that each document and customer has a one-to-many relationship to "distribution" (working title). This also allows you to store information in the distribution table about that particular customer/document combination.

                 

                However, IF you are sure that you're never going to need attributes in your distribution table other than the two keys, FM does actually allow for direct creation relationships. You could enter a list of document keys in the foreighn document key in customers, in a pilcrow-separated list, and your relationship to documents would link to all documents with keys that are in the list. Other databases, afaik, don't allow this "multiline key", but sometimes it's pretty handy.

                 

                a customer receives more than 1 document … then the first document sent event is overwritten and replaced by the newer one.

                 

                In any case, make sure that when dealing with multiple related records, make sure you're using a portal. If you're setting fields via the relationship, without being on a specific portal row, FM will assume the first record.

                 

                HTH

                 

                Chris Cain

                Extensitech

                • 5. Re: One to many relationship problem
                  mz5005

                  Hi Vyke, Shawn, Kris and Chris,

                   

                  Thank you all for your comments, they are very helpful and gave me more insight.

                   

                  First of all probably my explanation was not clear enough but in my view I do have a join table already (only relevant fields shown):

                   

                  Table:     Customers

                  Field:      CustomerID

                   

                  Table:      Documents

                  Fields:     DocumentID

                                 DocumentDescription

                   

                  Table:      Mailings

                                 CustomerID

                                 DocumentID

                                 DateSent

                   

                  Relationships:  Customers>CustomerID —> Mailings>CustomerID  / Allow to create records in Mailings

                                           Documents>DocumentID --> Mailings>DocumentID

                   

                   

                  Shawn:

                  "All that to say… it sounds like there might be an issue with your script. How are you creating the related record? Perhaps there is something going to the wrong portal row and setting the fields? Overwriting the existing record instead of creating the new one?"

                   

                  Chris:

                  "In any case, make sure that when dealing with multiple related records, make sure you're using a portal. If you're setting fields via the relationship, without being on a specific portal row, FM will assume the first record."

                   

                  I think the problem is somewhere as mentioned in the above quoted replies:

                   

                  Weekly it is decided using a Layout (MailingsPrepare) with Customer, Document, last received mailing (and other info) what customer should receive what document now. This is NOT from any kind of portal view (but a 2-dimensional layout). Choosing what document to send is done by choosing from the value list based on the Documents table.

                   

                  How does the Mailings (join) table get updated after?

                  (posting the actual script would be confusing so..)

                   

                  the update MailingsTable script in short does this:

                   

                  Go to Layout [MailingsPrepare]

                  PerformFind (to select records with the same DocumentID in the SendNow field)

                  GoTo RRP first

                  Loop

                       SetField Mailings::CustomerID; Customers::CustomerID

                       SetField Mailings::DocumentID; "documentID selected before with PerformFind

                       SetField Mailings::DateSent; "mailingdate"

                       Go to RRP [Next; Exit after last]

                  End Loop

                   

                  Result: everything goes well, but if a customer has received a document before, the "older" record is overwritten

                   

                   

                  Q:

                  1. Is it with the above description impossible to achieve what I want? (Mailingstable with historic records of ALL docs sent)

                  2. If not, then how should I proceed?

                   

                  I don't expect you guys to take me by the hand, but just point me in the right direction please.

                  Again, thanks to all for your help!

                  • 6. Re: One to many relationship problem
                    erolst

                    It seems that your existing join table models every combination of Customer and Document, and on every mailing event, you simply update the dateLastSent field.

                     

                    What I gather you really want is a table that records/describes the actual send events; so after it has been decided in your weekly get-together what documents to send to which customers, you will want to create new records, rather than “update” existing ones; e.g. using a simple two-level loop:

                     

                    Set Variable [ $listOfDocumentIDs ; … ]

                    Set Variable [ $listOfCustomerIDs ; … ]

                     

                    Set Variable [ $docCount ; ValueCount ( $listOfDocumentIDs ) ]

                    Set Variable [ $cusCount ; ValueCount ( $listOfCustomerIDs ) ]


                    Go to Layout [ Mailings ]

                    Loop

                    # through customer list

                      Exit Loop If [ Let ( $cusCounter = $cusCounter + 1 ; $cusCounter > $cusCount ) ]

                      Set Variable [ $docCounter ; 0 ]

                      Loop

                        # through document list

                        Exit Loop if [ Let ( $docCounter = $docCounter + 1 ; $docCounter > $docCount ) ]

                        New Record/Request

                        Set Field [ Mailing::id_customer ; GetValue ( $listOfCustomerIDs ; $cusCounter ) ]

                        Set Field [ Mailing::id_document ; GetValue ( $listOfDocumentIDs ; $docCounter ) ]

                        Set Field [ Mailing::dateSent ; Get ( CurrentDate ) ]

                      End Loop

                    End Loop

                    • 7. Re: One to many relationship problem
                      mz5005

                      hi eroist,

                       

                      sorry for my late reply to your great help.

                      I will work on your suggested two level loop solution when I return from my

                      business trip.

                      I really appreciate the effort you (and the previous posters) have done to

                      help me on this issue!

                       

                      (I will report the item solved when I have my laptop again, the button

                      doesn't work on my phone).