1 2 Previous Next 23 Replies Latest reply on Sep 13, 2011 2:34 PM by philmodjunk

    Relationship cycle

    Matty_1

      Title

      Relationship cycle

      Post

      Hello,

       

      I'm not too sure how to approach this issue but I've been battling with a relationship cycle and I'm unsure how to get myself out of it.  What I have are the following tables:

      Sales_Contract

      SFreight_Contract

      Sales (all sales transactions are stored here)

       

      First off a sales contract is issued, from there the user can either create a sales freight contract (there can be multiple freight companies associated to one sales contract) or the user can start entering sales transactions.  The order doesn't matter.  The issue is that at some point (for tracking purposes) each individual sales transaction needs to be assigned to a particular freight contract under the parents sales contract.  This leaves me in a relation loop and I'm stuck.  I can't seem to find a way to have each sale transaction associate themselves to the proper freight contract in a portal all while having proper freight calculation totals.

      I hope I'm not being to general, help would be greatly appreciated!

      RelationGraphRECONFIG.jpg

        • 1. Re: Relationship cycle
          philmodjunk

          Sounds like you have a many to many relationship between a Sales_Contract and the SFreight_Contract table. Does each record in SFreight_Contract represent a different Freight company, thus any given sales contract can list more than one freight company, but any given freight company can have more than sales contract?

          If so, you need a join table:

          Sales_Contract----<SC_SFreight>----SFreight_Contract

          Sales_Contract::SC_ID = SC_Freight::SC_ID
          SFreight_Contract::SFreightID = SC_Freight_Contract::SFreightID

          Let me know if this is the case and I'll provide more detail and a demo file in a follow up post. (But maybe SFreight_Contract is your join table and you have this set up already.)

          To link every record in sales to a record in SFreight_Contract while still maintaining relationships to Sales_Contract requires add a new occurrence of SFreight_Contract to your relationship graph so that you can have both relationships without getting the "cycle".

          Select SFreight_Contract in Manage | Database | Relationships and click the button with two plus signs to duplicate it. You duplicated your table, just created a new "label" for the existing table. Now link this new occurrence to Sales. You can double click the new occurrence to open a dialog where you can give it a more descriptive name.

          If table occurrences are a new concept, you may want to read this tutorial:  Tutorial: What are Table Occurrences?

          • 2. Re: Relationship cycle
            Matty_1

            Hey Phil,

             

            Occurrences aren't a new concept to me, I have a fairly complex system running right now.

            You are correct with your first question.  There is only one sales contract, but within one sale contract there are several sales tranctions as well as freight contracts.  I was able to display my information correctly which is: assigning a single freight contract to each sales transaction and then having it show up in a portal on the freight contract side but I'm unable to make it calculate the freight rates properly.  The freight rates live on the freight contract.  

             

            Sales_Contract is placed in the middle and on one side I have the Sales table and SFreight_Contract on the other.  They are both linked by contract number which is the cause of the issue but I can't seem to wrap my hard around solving it.  I then added several occurrences to the SFreight_Contract table to display my drop downs properly.  I have two occurrences with my contacts page one which allows me to isolate the freight companies and then of course the Id relationship.  I then set up a relationship with sales ... I'm stuck here because all I want to see on the sales side is a drop down of potential freight companies for this particular Sales_contract I've achieved this, the tough part is properly associating the sales transaction to the freight contract and have my calculation fields display the correct information.

             

            I unfortunately don't know where to start giving you more indepth information about the relationships.  Maybe a picture?

            • 3. Re: Relationship cycle
              philmodjunk

              I unfortunately don't know where to start giving you more indepth information about the relationships.  Maybe a picture?

              I used a text based method for documenting relationships in my last post that you can use as a model. You can also captuer a screen shot of manage | Database | Relationships, click Edit Post on your original post and upload the image file there. You can also upload such an image file to a file sharing site, then include a link to that shared file here in any follow on post.

              You might also describe the calculation that isn't working and how you've tried to implement it. This sounds like a very straight forward problem to me, so I don't know where you get into trouble trying to implement it.

              • 4. Re: Relationship cycle
                Matty_1

                Ok I'll post an image of my relationships, you might screech at the site.  I'm no guru so it may or may not be a mess, I can't tell.  All I knwo is it works.

                • 5. Re: Relationship cycle
                  Matty_1

                  Ok I've posted the image.  Notice that the example I'm using in this thread is actually disconnected from the rest of the graph at this point.  I was trying different options.  I originally hadit looking exactly like the Interim in the center at the top.

                  • 6. Re: Relationship cycle
                    philmodjunk

                    Even when I zoom my browser in, I can't read the text in your screen shot. Could you capture a new one and crop it to eliminate all the white space or use the magnifying glass to zoom it in so it will display the boxes with a larger scale?

                    • 7. Re: Relationship cycle
                      Matty_1

                      I've zoomed into this one and I can see everything.  Please let me know if this isn't good enough.  Thank you.

                      • 8. Re: Relationship cycle
                        philmodjunk

                        If I am reading the graph correctly, (still hard to read and some names aren't fully displayed), you have the following relationships:

                        SalesSFreight_Contracts::FreightContractID = SFreight_Contract::FreightID

                        Sales_Contract 2::ContractNumber = SFreight_Contract::PurchaseContractNumber

                        SalesSFreight_Contracts 2::ContractNumber = SFreight_Contract::PurchaseContractNumber

                        SFreightContacts::ContactID = SFreight_Contract::FreightCOID

                        SFreightContactsDR...::FreightCompany = SFreight_Contract::constFreightY

                         

                        Which of these are occurrences of Sales? Sales_Contract? SFreight_Contract?

                        I'm not sure I'm decoding your naming conventions here correctly.

                        Please explain how your "freight rates" calculation is supposed to work.

                        • 9. Re: Relationship cycle
                          Matty_1

                          Ok, for simplicity's sake I've reconfigured the graph to how I had it when it was displaying properly but not calculating properly.  I've also switched a few table names I accidentally reversed.  My naming convention is as follows:

                          IF there is more then one table name, the table it is directly relating with will be listed first, then the actual table occurrence followed by a descriptor. Example break down:

                          SFreight_ContractSalesPortal: (SFeight_Contract)(Sales)(Portal) -- The first is the table to which this occurrence is relating to. The second is the table this occurrence belongs to and last is the descriptor is applicable.

                           

                          Formula:

                          FreightRate * Sum(Sales::Quantity) -- yields a total of ALL sale transactions that have the same contract number.

                          FreightRate * Sum(SFreightSalesPortal::Quantity) -- yields a 0.

                          • 10. Re: Relationship cycle
                            philmodjunk

                            Did you know you could drag the right hand edges of these boxes out to make them wide enought to show the complete name?

                            I see two occurrences named "SalesSFreight_Cont..." Presumably one is SalesSFreight_Contract and the other is SalesSFreight_Contract 2.

                            I don't see what you've changed here, this looks like the same screen shot on my end--I've seen this before and think there may be a glitch in the forum software that messes up these kind of image updates, but others have been able to upload new images without trouble and I don't know what the difference is. And I don't see any occurrence named "SFreightSalesPortal".

                            I can see that the second expression doesn't produce the value you want, but does the first one produce the total you want? (And if it works, why do you need the second one?)

                            • 11. Re: Relationship cycle
                              Matty_1

                              The image is the same??? I've refreshed twice and my side most definitely has the new set up.  The sales occurrence should be highlioghted in orange.  I'n my update I also stretched the boxes so you cold see all the names.  You don't see all this???

                              • 12. Re: Relationship cycle
                                philmodjunk

                                Nope.

                                You may need to either break it down in text like I've done previously or upload to a share site and post the link to it in a new post. I'm going to click Edit Post myself to see if I see anything different. (As "community leader", I can edit the posts of others...)

                                • 13. Re: Relationship cycle
                                  philmodjunk

                                  I definitely see the original image, named: RelationGraphRECONFIG.jpg

                                  I'm going to edit your post by using the delete this image option. This may remove your latest image as well, but hopefully will then enable you to re-upload. I'm also going to report this issue to Modman so that he can complain to the RightNow programmers.

                                  • 14. Re: Relationship cycle
                                    philmodjunk

                                    As expected, that removed all images from your post, but maybe now you can upload a new copy and I'll be able to see it...

                                    1 2 Previous Next