7 Replies Latest reply on Jan 24, 2013 10:13 AM by philmodjunk

    Problem with value list

    DaveSaunders

      Title

      Problem with value list

      Post

           In a portal I have a need to look up and show subcontractor packages (various labor and materials packages) and their rates based on a selected subcontractor.  I select the subcontractor from a drop-down value list, which is based on a field in a Subcontractors table.  Next field I select a package from a drop-down value list, which is based on a Sub Pkgs table.  However, this list is set to only show packages from the selected subcontractor,  by selecting "Include only values starting from 'SUBCONTRACTORS'" option within the "Use values from field" option in the value list.

           The problem I'm having is that once the subcontractor is selected, the packages list properly shows only packages related to that subcontractor but in the RATE field, the rate value always defaults to the rate associated with the first record of packages for that subcontractor and not the package selected.  I don't know how to make the value in the rate field relate to the selected package (sub pkg).

           Field order is Subcontractor, Package, Rate.  Noticed that rate will populate as soon as subcontractor is selected even before package has been chosen.

           See screenshot for relationship diagram

            

      relationship.jpg

        • 1. Re: Problem with value list
          philmodjunk

               You need two different relationships to Sub PKGS. The one you have that matches by subcontractor is needed for the conditional value list. A second one is needed to match by your package field.

               To do this, click Sub PKGS to select it. Then click the duplicate button (two green plus signs). This wil produce a new occurrence of SUB PKGS named SUB PKGS 2. You can then link subcontractors to this new occurrence by package fields and refer to Sub Pkgs 2 instead of sub pkgs in your rate look up.

          • 2. Re: Problem with value list
            DaveSaunders

                 Okay, as am quite new to FileMaker I will need you to be a bit more specific on the linking aspect.  I've created  a Sub Pkgs 2 but when I link subcontractors the outcome is the same.  I've included the new and expanded relationship graph.  Maybe the linking is still wrong?

            • 3. Re: Problem with value list
              philmodjunk

                   Do you see that while you have two occurrences of Sub PKGS, they both match values using __Subcontracto_fk?

                   You need this set of match fields:

                   Subcontractors::_subpkgID_fk = SUB PKGS 2::_subpkgID_pk

                   This assumes that Subcontractors::_subpkgID_fk is the field that you have formatted with a conditional value list for selecting a Sub PKGs record.

                   PS, going by the field names it appears that you are using a company name as the contractor primary key. Just like people names, company names are not always unique and company names change. Also, a company name must be typed in at least once and if a data entry eror is committed and not immediately discovered, correcting the error is the same as a company name change--updating the company name will disconnect records linked to it by compamy name as the values will no longer match.

                   An auto-entered serial number is a much more solid field to use as your primary key as it avoids all of the above mentioned issues.

              • 4. Re: Problem with value list
                DaveSaunders

                     Okay, to simplify I've created a simple scenario with the same problem (see attached screenshot).  Menu choices are determined by menu type (e.g. Lunch, Dinner, Dessert).  Select Dessert and only Dessert options show up in the conditional value list.  But as you can see by the diagram, before I choose a dessert item, I already have a price, which is the price of the first record of the type in the "menu" layout.  Then when I do choose a dessert item, price does not change.  Can't get it to relate to the dessert choice.

                     In the Rel Diag Graph I have created a duplicate menu table occurance (menu 2) and connected the relationship by type and get no different results.  When price field is set to "display data from" either menu or menu 2 tables, the effect is the same.  I must not be getting enough sleep of something because I can't get this to click in my pea-sized brain and it can't be that complicated.

                • 5. Re: Problem with value list
                  philmodjunk

                       You've simplified to the point where what you show no longer matches the original problem.

                       You need two relationships to two occurrences of the same related table. This new screen shot only shows one relationship, which takes us back to square one--adding the needed second relationship in order to get both a working conditional value list and the correct "lookup" of data from the same related table. You need one relationship that matches by Type and one that matches by item. The relationship that matches by type is needed for the conditional value list. The relationship that matches by item is needed to look up the price.

                       Why change databases in mid stream?

                  • 6. Re: Problem with value list
                    DaveSaunders

                         The reason I changed databases mid stream is because I found a write-up on Conditional Value Lists and they used that menu db as an example.  I wanted to understand the concept using that example I did what you suggested, creating a second table occurrence of the MENU table (Menu 2) and linked the ITEM field to ITEM field in the ORDER table and BAM!, the corresponding price showed up in the Order layout.  So, I got it to work.  Now, going back to my original project, it's different in that I cannot link RATE fields the same way because there is no RATE in the Subcontractor table.  So, in the attached screenshot of the graph (above which, in the screenshot, is also part of the layout I'm using), where do I make the connection using the SUB PKGS 2 table occurrence? 

                         And I do so appreciate your help and patience with the less educated of the human species.

                    • 7. Re: Problem with value list
                      philmodjunk

                           I have already posted what you need to do for that second relationship. Copied and pasted from that earlier post:

                           You need this set of match fields:

                           Subcontractors::_subpkgID_fk = SUB PKGS 2::_subpkgID_pk

                           This assumes that Subcontractors::_subpkgID_fk is the field that you have formatted with a conditional value list for selecting a Sub PKGs record.