2 Replies Latest reply on Jan 12, 2017 10:53 AM by rigger

    Value List/Lookup question

    rigger

      I have a labor rate field I am populating from a dropdown. The record with the rate contains a cost code, say 1234.

       

      The value list the dropdown uses is based on fields in a file called Labor Rates. The relationship for the value list shows all the labor rates and labor rate descriptions that match the current cost code.When the user picks a value from the dropdown, a lookup triggers that grabs the unique ID from the record in Labor Rates. The relationship the lookup uses does the lookup based on costcode & price.

       

      My problem is that it turns out that multiple items in the labor rates table can have the same costs, so that means my lookup grabs only the first matching record based on costcode & price, not the ID of the item the user actually selected.

       

      I need to get a unique ID from the Labor Rates table for the rate the user selects from the dropdown. Can I do this using a dropdown and script trigger, or do I need to have the user pick from a portal? The other gotcha is the user wants the labor rates drop down to sort a specific way.

       

      I thought about using a popup menu on my ID field and grabbing the value that way, but the sorting requirement is messing me up, plus the popup menu is unwieldy with a large list of values.

       

      Anyone have any suggestions?

        • 1. Re: Value List/Lookup question
          philmodjunk

          I would say that a FileMaker Value list is unwieldy for long lists of values--no matter what format is specified for the value list. I'd recommend either some type of conditional value list scheme that filters the list down to a shorter list of matching values or some sort of script based system--a selection portal would be one such option.

           

          But it seems that you need to select an ID based on two values: "costcode" and "Price". In the table from which you are looking up rates, Does combining the values of the costcode and price fields produce a unique value? If so, then your relationship should work. If not, then you have a more basic problem to solve. How, just by looking at the data available, would you know which of these rates to select-say if you were just doing this as a paper and pencil excersize?

           

          Let's assume that Price is not unique, but the combination of cost code and price is unique. If so, then you can enter or select the price, then use a conditional value list to list only the cost codes that match that price--but this still leaves me wondering how you would tell which cost code is the correct one to select since this value is just a number apparently.

          • 2. Re: Value List/Lookup question
            rigger

            Yeah, couldn't do it with a dropdown. Ended up using a selection portal in a popover window and scripted it.

             

            Thanks for the input.