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?
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
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 )
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.
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.