Emailing to PDA

Classic Servoy
1

Hello,

We are currently sending estimates to our sales reps from filemaker, all our reps have blackberries. Capturing and formatting the data was a pain in Filemaker. As I rebuild the solution in Servoy, I want to add this fuctionality.

Now that I have sucessfully created / emailed PDF’s via the mail plugin, I want embed estimate info into the body of an email.

These estimates will be very basic:
order info,account info, a few dates, Line Item Detail, and a few totals.

I know this can be much more robust using html , etc. but I just wanted to keep it simple. Should I try to capture all this info into a global and format, or merge a few globals into the message body.

I was just looking for a point in the right direction.

Thanks,
Erich

2

I guess my main question is regarding the lineitems. In filemaker I had to loop through the line items and paste them into a global. I figured there was an easier way to capture them in Servoy.

3

Hi Erich,

In Servoy you also loop though the records but you can do all this in code. So no screen updates as with FileMaker.
Also you don’t have to use globals when you want to produce the HTML content only 1 time. You simply use a local variable in a method and append new data to it like this:

var myHTMLbody = '<html><head></head><body>';
myHTMLbody += 'some data';
myHTMLbody += '</body></html>';

Hope this helps.

4

Thanks, So looping is still the way to go, opposed to Queries / Datasets/ arrays?

5

If you have a query that gets the data you want, use a query and for example the JSDataSet functions like

JSDataSet.getAsHTML()
JSDataSet.getAsText(String column_separator, String row_separator, String value_delimiter, boolean addColumnNames)
JSDataSet.getColumnAsArray(number)
JSDataSet.getMaxRowIndex()
JSDataSet.getRowAsArray(number)

you will end up looping, though :wink:. But there is nothing wrong with looping, no looping, no programming…

6

ok, thanks thats the route I was thinking about, but was going to convert a dataset to an array, but looks like it will be easier to format directly as html using the JSDataSet functions

7

I am having a problem, with capturing my dataset. I wentr into the query analyzer to run my query, then pasted my query into the Servoy Method.

I just wanted to see the data in simple format before I started formating into html.

I am getting the following error:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ‘order_id’. [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ‘order_id’. is not a function.

Here is my method:

//Get a dataset based on query
var maxReturnedRows = 30;
var query =
“SELECT Item_Condition, Item_Description, Item_Total”+
“FROM orderlineitems”+
"WHERE order_id = order_number AND Item_Description LIKE ‘%’ ";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,null, maxReturnedRows);

//assuming the variable dataset contains a dataset, you can create csv or tab delimited results
var tab = dataset.getAsText(‘\t’,‘\n’,‘"’,true)

globals.Text10 = tab

I am a bit bewildered

8

That string concatenation produces this:

SELECT Item_Condition, Item_Description, Item_TotalFROM orderlineitemsWHERE order_id = order_number AND Item_Description LIKE ‘%’

This goes wrong. You should add some spaces between for example Item_TotalFROM. Then I don’t really understand

Item_Description LIKE '%'

Like what? If you want to find those records that have a value you could use ```
item_description IS NOT NULL


How does it look then?
9

OK, that helped allot, but I founf my main issue. I want the
WHERE Order_ID =" the selected records data provider {order_ID}" from my Orders Table.

How do I pass this value from my current record into my select statement making it dynamic.?

10

//Get a dataset based on query
var maxReturnedRows = 30;
var query =
“SELECT Item_Condition, Item_Description, Item_Total”+’ '+
“FROM OrderLineItems”+ ’ ‘+
“WHERE Order_ID =”+’ '+ order_number;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query,null, maxReturnedRows);

//assuming the variable dataset contains a dataset
var dataHtml = dataset.getAsHTML() //puts the contents of the dataset into the variable dataHtml formatted as an html table

globals.Text10 = ‘’+dataset.getAsHTML()+‘’

The order_Number is the data provider from the selected record node. If I replace it with a record number “S243” , it works fine. I must be missing some simple syntax.

11

what’s the problem now? what error do you get?

12

Basically what I guess is happening is that the order number contains a value of the current record “S240”, it says that is cannot find the column ‘S240’ to complete the query in the table. I am at home so I cant capture the error.

Seems like it cant see the difference of the hardcoded Select statement and the data provider value I selected order_number, I selected from the node in the method editor

13

Then I think I see what goes wrong. That column order_id is a string column. So you cannot write

WHERE Order_ID = S1234

but

WHERE order_id = 'S1234'

Try this code:

var query =
'SELECT Item_Condition, Item_Description, Item_Total ' +
'FROM OrderLineItems '+
'WHERE Order_ID = \'' + order_number + '\''

;

What I usually do is copy the variable “query” from the debugger into a SQL tool and send that to the database. Then you see, if

  • either your query is wrong
  • or your string concatenation when construction the query is wrong

Hope this works.

14

another possibility is to add this line

application.output(query)

before you send the query to the database and check that statement.

15

Voila,

I new it was a concatenation issue, your code worked great.

I guess I am missing the significance of the ****. That’s what made the difference.

application.output

I tried to view in the debugger, but I could not view the entirt statment, I will use the output method when debugging.

Thanks for your patience.

Erich[/b]

16

How can I edit the default HTML output.
For example:
I want to format the Item_Total to refelct currency format w/ 2 decimal spaces.

I also want to edit the table padding etc. Would I need to capture the dataset as an array, so that I could format specific rows, and tables with html

Or can I edit the output after I run this code.
var DataHtml = dataset.getAsHTML() //puts the contents of the dataset into the variable dataHtml formatted as an html table
globals.Text10 = ‘’+dataset.getAsHTML()+‘’

I noticed if I see my output global to text area I can see the html.

17

Well, I tested and Blackberries Strip the HTML, and create links for graphics. I will have to go a different output route.