5 Replies Latest reply on Sep 25, 2014 1:07 PM by philmodjunk

    Changing a field on a report based on user input



      Changing a field on a report based on user input


      I have a database that has 10 different pricing levels (a-j) and need to be able to print a price-list out for customers based on their own pricing level. Instead of creating 10 separate reports for each level, I have created an input box where they put the pricing level they want and that goes into a calculated text field (Report Pricing Level) that overrides the existing value with this formula that goes into a table that used for the report information:
      If ( IsEmpty ( Self ) ; "Sell" ; Self ) where Sell if the default List Price
      I am having trouble on the report on getting the name from Report Pricing Level in the table Report Information and then getting the appropriate value from the Pricing Level (A-J) from table Products to print on the report.








        • 1. Re: Changing a field on a report based on user input

          Have you defined a relationship between the two tables. Is each "Pricing Level" a different record in the Pricing Level table so that you only need to pull up the pricing level records as specified by the value entered/selected in the Report Pricing Level field?

          • 2. Re: Changing a field on a report based on user input

            There is no relationship defined, I don't know how that would work in this case. 
            Each record in the Products table has 10 pricing levels (Pricing Level A-J). 
            I can easily create a report (for example Pricing Level E) by using <<Pricing Level E>> and that will print out the price level e for all of the products. 
            What I am trying to figure out is how to change the text field on the report to whatever letter the user has inputed on the input box.
            I tried <<Pricing Level ::Report Pricing Level>>  but it just prints "Pricing Level E" for example instead of $XX.XX




            • 3. Re: Changing a field on a report based on user input

              If you used a related table of pricing levels linked to products where each pricing level was a different related record, a relationship could be used.

              With your current set up, you could use a calculation field with a case function to get what you want, but that related table of pricing levels is a much more flexible way of managing this type of setup.

              Case ( UserSelectedLevelField = "A" ; Pricing Level A ;
                          UserSelectedLevelField = "B" ; Pricing Level B;
                        // repeat these entries for every pricing level
                         ) // case

              You might also be able to use this calculation field:

              GetField ( "YourTable::Pricing Level " & UserSelectedLevelField )

              • 4. Re: Changing a field on a report based on user input

                The Case function got the field to change to the appropriate field name that the user requested, but it then displays "Pricing Level A-J" and not the value from the Products. The GetField seems to get the first price from the sorted table list and populates the field with that number, and then displays that on every record.
                I will play with putting the pricing levels for each product into a separate table and relating them, but (and I am sure it is because I am not explaining this well) I don't see that changing the issue with the printed report.
                I somehow have to define or redefine the field setup on the report, but not the value, before the report evaluates itself. I tried in the initial script that runs when the report starts using Go To Layout and Set Field, but that just changed the actual price to "Pricing Level A-J".
                I am going to dig into script functions and see if there is a "Create Field" type function I can use to create a field on the fly and define it with the user input. 

                Thanks for all the help, definitely getting me to think in different ways on this.







                • 5. Re: Changing a field on a report based on user input

                  You need to do this from the context of Products. The easiest approach is to define the calculation field in Products, not your report table. Then make the field for the user a global field so that a single value entered/selected there can be used in this calculation field in all products records. You can then pull up a found set of the products records you want for your report and you'll see the results that you want.

                  but it then displays "Pricing Level A-J"

                  It sounds like you put the field names in quotes--probably because you got an error message if you didn't due to defining it in the report table instead of products.