Problem with value list
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