You can embed the text you wish to display in the message inside your SQL statement. For example:
SQL: SELECT 'This customer has ' + CAST(count(*) AS NVARCHAR(254)) + ' outstanding invoice/s.' FROM OINV T0 WHERE T0.[CardCode] =$[$4.0.0] and T0.[DocDueDate] < getdate()
By using a UF Macro with MessageBox, you can get multi-line SQL results by using the <NEWLINE> command.
Comments
2 comments
I'm having problems getting this to work. What I need is a combination of text and query results to be returned in the message.
When I try the following (the sql here is just a simple example - I know it doesn't make sense to write it this way if I actually just wanted the Item Code returned):
SQL:SELECT 'Test ' + "OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]
I get this error:
General error;339 invalid number: not a valid number string 'Test '
So I try using COALESCE:
SQL:SELECT COALESCE('Test ',"OITM"."ItemCode") from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]
But that just returns "Test" in the message box.
Just to exhaust all options, just displaying the Item Code does work, using this:
SQL:SELECT "OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]
And this:
SQL:SELECT COALESCE('Test ','Test') from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]
Only returns "Test".
So using + or using coalesce both are having issues. Any thoughts?
Nevermind. The following worked:
SQL:SELECT 'Test '||"OITM"."ItemCode" from "OITM" WHERE "OITM"."ItemCode"=$[$5.0.0]
Please sign in to leave a comment.