4 Replies Latest reply on Apr 12, 2011 4:51 PM by c.wagner1

    value list that includes only related field values (cant get it to work)

    c.wagner1

      Title

      value list that includes only related field values (cant get it to work)

      Post

      I don't see why this isn't working right, hopefully one of you guys will see my stupid mistake.  First a bit of background.  

      I have 2 tables sales and trackingInfo joined with a key field saleID.  Sales is a one to many with trackingInfo.  trackingInfo contains shippingCompany and trackingNumber

      Ultimatly I want 2 value lists.  The first one displays all the values in shippingCompany related to sales, the second one displays all of the tracking numbers that where related to that sale and shipping company.

      I have a self join relationship between trackingInfo and trackingInfo 2 that looks like:

      saleID = saleID

      and shippingCompany = shippingCompany

      The first value list works fine, it uses values from the first field trackingInformation::shippingCompany and includes only related values from sale.

      The second value list does not function correctly.  It is set to use values from the first field trackingInformation 2::trackingNumber and includes only related values from trackingInformation

      Thanks,

      Chris

        • 1. Re: value list that includes only related field values (cant get it to work)
          philmodjunk

          Sales::SaleID = TrackingInf::SaleID

          TrackingInfo::SaleID = TrackingInfo2::saleID AND
          TrackingInfo::ShippingCompany = TrackingInfo2::ShippingCompany

          The layout where you are using these value lists is based on TrackingInfo? (confirm that neither Sales, nor TrackingInfo2 is selected in Layout Setup|Show Records from).

          When you say that it "does not function correctly", how does it fail? No values? The wrong values?

          How do you enter a value into TrackingInfo::ShippingCompany? This would seem to be a value that comes from Sales and how it gets a value could be a factor in why it's not working.

          One useful test for debugging a conditional value list like this is to temporarily place a portal to the conditional value list's table occurrence (TrackingInfo2 in your case) and check to see if any records show in the portal. If none show or the wrong records show, you know that the relationship has been set up incorrectly. Once you've fixed the issue keeping it from working, you'll see the correct records in the portal and you can then remove the portal and start using your conditional value list.

          • 2. Re: value list that includes only related field values (cant get it to work)
            c.wagner1

            The layout where these value lists are based is Sales.

            The second value list fails by showing the wrong information.  If using the sample data of:

            sale contains 1 record:

            saleID = 1

            trackingInfo contains 4 records,

            saleID = 1, shippingCompany = "UPS" and trackingNumber = "UPS1"

            saleID = 1, shippingCompany = "FedEx" and trackingNumber = "Fed1"

            saleID = 1, shippingCompany = UPS" and trackingNumber = "UPS2"

            saleID = 2, shippingCompany = "FedEx" and trackingNumber = "Fed2"

            The first value list properly contains the values "UPS" and "FedEx" when displayed on a field in a layout with sales as its scope and saleID 1 as the current record.

            The second value list contains "UPS1" and "UPS2" reguardless of what is choosen in the first value list.

            TrackingInfo::shippingCompany has values entered from a portal on the sales layout that shows trackingInfo::shippingCompany and trackingInfo::trackingNumber.

            That is a very good debugging tip with the portal.  I will experament with that.

            Chris

            • 3. Re: value list that includes only related field values (cant get it to work)
              philmodjunk

              Sales::SaleID = TrackingInf::SaleID

              TrackingInfo::SaleID = TrackingInfo2::saleID AND
              TrackingInfo::ShippingCompany = TrackingInfo2::ShippingCompany

              It is set to use values from the first field trackingInformation 2::trackingNumber and includes only related values from trackingInformation

              And the field is on a sales layout.

              the key detail is that your are doing from sales with a relationship, Sales::SaleID=TrackingInfo::SaleID that is not affected by any value you have selected for the shipping company. Thus, it matches to all TrackingInfo records and you get all the values for records with that SalesID. The selfjoin is from trackinginfo to TrackingInfo2, so one related record with UPS is linking to all the records with UPS and another with FEDEx is linking to all the records with FedEx which then gives you all the values each time.

              Use this relationship:

              Sales::SaleID = TrackingInfobyCompany::SaleID AND
              Sales::ShippingCompany = TrackingINfobyCompany::ShippingCompany

              Draw your values from TrackingINfoByCompany, show only related values starting from Sales and it should work.

              • 4. Re: value list that includes only related field values (cant get it to work)
                c.wagner1

                Thanks Phil, that got it.  I am amazed at your ability to follow people's complicated relationship problems and suggest solutions based on solely on the little bit of information that is given.  I had a hard enough time writing out the problem, I didn't think anyone would be even able to follow what I was trying to say.  Thanks for your help.

                Chris

                wow, this post would work on an dating advice forum as well.  :-)