4 Replies Latest reply on Jul 25, 2015 9:37 AM by henry369

    Auto-enter based on an indicator field

    henry369

      Title

      Auto-enter based on an indicator field

      Post

      I am trying to figure out how to auto-enter into a field, one of several options of  values, from a related table, based on an indicator field in the destination table.   Specifically, a product has different pricings, depending on who the buyer is.  So there is a retail price, a government price, and a non profit price.   The source table is "pricing" with 4 fields.  The product code (key field) and the 3 pricing fields for each of the different buyers.   The destination table is an invoice, with the product code field, an indicator field which is a value list of the 3 different pricing categories (govt, retail, and non profit) and the price field.    When, say,  the govt indicator is selected in the indicator field, for the specific product code, I would like to have the govt pricing appear in the price field in the destination table.  I am wondering what is the best way to accomplish this.  Any help would be greatly appreciated.  Henry

       

       

        • 1. Re: Auto-enter based on an indicator field
          SteveMartino

          So on an invoice, you pick the type (government, retail, non-profit), and you want to only have those available when making a selection.  IOW, when government is selected, only the products/pricing for government will be available.

          I would say the standard method of Invoice, LineItems, Products, with work.  I would make the dropdown list to select products for line items a conditional value list that filters by type.  Inserting would be either auto enter by calculation or a lookup.

          • 2. Re: Auto-enter based on an indicator field
            henry369

            Thank you Steve.  however this does not answer my question.   I was thinking perhaps  some type of logical calculation, something along a case () or choose () that would auto-enter the appropriate price value,  but no sure of syntax or how to do it.  I am hoping to avoid a nested if () situation.    what i would really like to use is some type of toggle to point to the correct field on the source table.  

            • 3. Re: Auto-enter based on an indicator field
              philmodjunk

              Case ( IndicatorField = "retail" ; Products::retailPrice ;
                           IndicatorField = "Government" ; Products::GorvernmentPrice ;
                           IndicatorField = "NonProfit" ; Products::NonProfitPrice
                         )

              Will work with your current set up once you substitute your names and values for mine. (I only used 3 values for the indicator. You mention 4 but I only spotted examples for 3 in your post.)

              What steve is suggesting is a good approach, but would require a separate pricing record for each price for each product. I think that's the more flexible approach, but it would require restructuring your data.

              • 4. Re: Auto-enter based on an indicator field
                henry369

                Thank you very much -  that did it nicely.   and yes you are correct that Steve's method would require the restructuring of data.  very basic stuff here, but very grateful for your help.  have a good day.