Change database text field in Report Designer?

#1 · January 15, 2020, 7:42 am

I created a report in Report Designer with a repeating body. It's working fine, except I'd like to alter the content of one of the db fields before it's printed. Is that possible?

The database contains a text field with a separator character. When I print it, I'd rather have that character removed, but I don't see any way to do that in the Report Designer.

What I need to do is something like attaching a function to the text field to alter the content and return the changed text to the report builder.

Would a dbpExecSQL command work for this?

#2 · January 16, 2020, 4:42 am

@vwatson

I would suggest a simplistic (but Brute Force) solution ...

a) create an additional field/column ... say the original field is called Category ... say you call this new field CategoryPrinted

b) before running the report, update the CategoryPrinted field using the Category field ...

dbpOpenTable "yourID" "yourTable" ""

... if your report is on a filtered set of records
dbpQuery "yourID" "yourTable" "yourFilter"

Loop "1" "[yourID.yourTable.$RecCount]" "[junk]"
   StrReplace ""[yourID.yourTable.Category]" "!yourSeparatorCharacterHere" "" "[yourID.yourTable.CategoryPrinted]" ""
   dbpNext "yourID" "yourTable"
EndLoop

Would a dbpExecSQL command work for this?

This web page ... https://www.sqltutorial.org/sql-string-functions/sql-replace/ ... shows you how to use an SQL command.

If it still takes too long to cycle through the filtered records, you could do something like ...

UPDATE yourTable
SET
CategoryPrinted = REPLACE(Category,'separatorCharacter','');

... with an (optional) WHERE clause to filter only those records that would be used in the Report.

#3 · January 16, 2020, 5:51 am

Thanks. I like the brute force approach. :)

I'll try to implement that tomorrow.

#4 · January 18, 2020, 7:45 pm

Finished that part and it works very nicely. :)

Change database text field in Report Designer? - Forum

Change database text field in Report Designer?