Ticket #29681
Question
I'm trying to create a simple m-Power report that requires I override the SQL statement to insert (TRIM) on my table join fields. Only, I'm not sure how to override the SQL statement and still allow for the user to enter a specific run time selection (in the this case it's PO number).
Answer
If you are overriding the default m-Power SQL query in your report, look at this document here for how to accommodate runtime record selections into the query: (https://www.mrc-productivity.com/docs/m-painter-app-prop/override-default-sql-statement) Look for this section: 'How do I add runtime parameters to my SQL statement?' Now just as a friendly suggestion, there are some other alternative routes to accomplish what you are doing (which appears to be overriding the query to only for the sake of adding a TRIM() function on two join fields) that don't require overriding the SQL statement. Overriding the SQL statement in any application is a topic that has always fallen under the realm of custom code, so if useful to you, here is a more inherently m-Power (and easier) approach to accomplish this: In Manage Tables from the interface, add a logical field calculation (https://www.mrc-productivity.com/docs/general/create-a-logical-field-via-the-data-dictionary) to both tables that does a TRIM() over each field you are using for the table join. Then create a synonym between the fields so you can join over the trimmed fields when building your report. I like this approach since it's very easy, and you wouldn't have to mess with overriding the SQL statement at all. However, either route can do what you are looking for.
Asked on November 8, 2019