Creating an email with data from an SQLite table

[Peter K]

I've got code that sends an email from an SQLite table that I've created. Things work, but I'd like to make them more elegant.

Code: Select all

   put "select email from borrowers where borrowers.userID = (select max(userID) from borrowers)" into tSQL
   put revdb_querylist (,,gConID, tSQL) into tList
   handleRevDBerror tList
   if the result is not empty then 
      answer warning "Didn't get email address from borrowers table " &tList
      exit mouseUp
   end if
   
   put "select prop_addr from borrowers where borrowers.userID = (select max(userID) from borrowers)" into tSQL
   put revdb_querylist (,,gConID, tSQL) into tList2
   handleRevDBerror tList2
   if the result is not empty then 
      answer warning "Didn't get property address from borrowers table " &tList2
      exit mouseUp
   end if
   
   put "select * from temp_deals" into tSQL
   put revdb_querylist (,,gConID, tSQL) into tList3
   handleRevDBerror tList3
   if the result is not empty then 
      answer warning "Didn't get mortgage information " &tList3
      exit mouseUp
   end if
   
   put "Mortgage availability for " & tList2 into tList2
   put "Loan Program     Interest Only     Interest Rate     Monthly Payment" into tHeader
   put tHeader & return & tList3 & return & return & return & "Thank you," & return & return & return & return & "Peter Koppelman" into tList4
   revMail tList,, tList2, tList4
end mouseUp

on handleRevDBerror pError
   switch
      case item 1 of pError is "revdberr"
         return "There is a revDB Error: " &pError
         break
      case "syntax error" is in pError
         return "There is a database Error: " &pError
         break
   end switch
end handleRevDBerror

1) I have two SQL statements where I retrieve information from my borrowers table. How could I do this in one statement?
2) I can't figure out how to create headers when I extract information from the temp_deals table. I know that there is a ".headers on" command in SQLite, but I can't figure out how to tell SQLite through Livecode to do this.
3) I've created a header line in the body of my email. How do I format the information that I've retrieved from the temp_deals table to line up with the header? It would be nice if the person that I'm sending the email to could read the information easily.

Thanks,


Peter

[dglass]

1. "select email, prop_addr from borrowers ..."

2. probably can't

3. build the header (tab-separated?) and put it in a variable, append the data from temp_deals. If the data from temp_deals is using the defaults it will be tab-separated, and everything should line up. Which is what it looks like you are doing so I'd say explicitly build the header with 'tab' and see what happens.

[Peter K]

I guess I should have been more specific with my first question. I know that I can retrieve more than one piece of information from a table in a select statement. The issue is that I need to use them as two different variables in my revMail statement. When I retrieve them in one select statement they appear as xyz@gmail.com100 Main Street. I guess the question should be how can I retrieve both of them in one select statement and be able to use them as two different variables in my revMail statement.

Thanks,


Peter

[Peter K]

I guess I should have been more specific with my first question. I know that I can retrieve more than one piece of information from a table in a select statement. The issue is that I need to use them as two different variables in my revMail statement. When I retrieve them in one select statement they appear as xyz@gmail.com100 Main Street. I guess the question should be how can I retrieve both of them in one select statement and be able to use them as two different variables in my revMail statement.

Thanks,


Peter

[Peter K]

How do you build a header with tabs?

Peter

[dglass]

I guess I should have been more specific with my first question. I know that I can retrieve more than one piece of information from a table in a select statement. The issue is that I need to use them as two different variables in my revMail statement. When I retrieve them in one select statement they appear as xyz@gmail.com100 Main Street. I guess the question should be how can I retrieve both of them in one select statement and be able to use them as two different variables in my revMail statement.

Given your revdb_querylist command (you left the first two parameters empty) you should be getting back a variable with the fields separated by tabs, and the records separated by returns.

So your example should be in the variable like:

xyz@gmail.com<tab>100 Main Street<return>
...the next record's data

So, item 1 of the line should be 'xyz@gmail.com' and item 2 should be '100 Main Street'. A repeat with each line should get you iterating over the list.

As to the header, from your original post it looks like the items are separated by tabs, but maybe you just put spaces in there.

Code: Select all

put "Loan Program" & tab & "Interest Only" & tab & "Interest Rate" & tab & "Monthly Payment" into tHeader

[Peter K]

As far as formatting the data is concerned, if I just use the data as it is from my SQL select command, it's not formatted very nicely.

If I use the put command, i.e. put word 1 of tList3 & tab & tab & word 2 of tList 3 & tab & tab & word 4 of tList3 into product

All I get is the first row of the data retrieved from the SQL select command (there are 8 rows in the table). How can I modify the put command to use data from all of the rows that the select statement retrieves?

My other question has to do with revMail. It opens up email, populates all of the information properly but does not send the email. I have to click on the send command. Is there anyway to automate the clicking of the send command?

Thanks,

Peter

[dglass]

Using the command as you have it should give you a list that has its elements separated by tabs, and its records separated by returns:

Record1Field1<tab>Record1Field2<return>
Record2Field1<tab>Record2Field2<return>

My initial guess is that your header text is longer than your data text, and that is causing the tabs to not line up. So, while there is only one tab between each piece of text, it isn't necessarily the same tab, and when viewed as just text it is 'crooked'. If you loaded that same text into a table it would probably line up.

Hopefully that makes sense.

Given all of that, it doesn't seem like it would be necessary for you to have to rebuild the returned data.

[Peter K]

I don't know how many rows will be in the table that I extract information from. Each time it can change. It's dynamic.

Is there a way to extract data from an SQL table into an array? If I can do that I can create the body of the email using a repeat until loop.

thanks,

Peter

[dglass]

I don't think we are communicating.

The command you are using to get the data from the database:

Code: Select all

put revdb_querylist (,,gConID, tSQL) into tList2

returns a list of records, one per line, with the fields separated by tabs.

As far as LC is concerned that list is just as effective (more?) as creating an array out of all the data, and you can repeat over the list using

Code: Select all

repeat for each line thisLine in tList2
---do stuff with each record line
end repeat

[Peter K]

I agree. Maybe we are miscommunicating. I've rewritten some of the code so here it is...

Code: Select all

put "select email, pur_price, fin_amt, name, prop_addr from borrowers where borrowers.userID = (select max(userID) from borrowers)" into tSQL
   put revdb_querylist (comma,,gConID, tSQL) into tList
   handleRevDBerror tList
   if the result is not empty then 
      answer warning "Didn't get property address from borrowers table " &tList
      exit mouseUp
   end if
   
   put item 1 of tList into temail
   put item 2 of tList into tpur_price
   put item 3 of tList into tfin_amt
   put item 4 of tList into tname
   put item 5 of tList into taddr
   
   put " " into tList3
   put "select loan_type, deal_type, int_rate, payment from temp_deals" into tSQL
   put revdb_querylist (comma,comma, gConID, tSQL) into tList3
   handleRevDBerror tList3
   if the result is not empty then 
      answer warning "Didn't get mortgage information " &tList3
      exit mouseUp
   end if
   
   put "select count(*) from temp_deals" into tSQL
   put revdb_querylist (,, gConID, tSQL) into tList4
   handleRevDBerror tList4
   if the result is not empty then 
      answer warning "Didn't get count information " &tList4
      exit mouseUp
   end if
   
   put " " into product
   repeat with linecount = 0 to tList4  
      put product & return & item 1 of tList3 & tab & tab & item 2 of tList3 & tab & tab & item 3 of tList3 & tab & tab & item 4 of tlist3 into product
   end repeat
   
   ## send email to client ##
   put "Mortgage availability for " & taddr into taddr
   put "Purchase Price: $ " & tpur_price & return & "Financing Amount: $ " & tfin_amt into tmoney_info
   put tmoney_info & return & return & "Loan Program" & tab & "Interest Only" & tab & "Interest Rate" & tab & "Monthly Payment" into tHeader
   put tHeader & return & product & return & return & return & "Thank you," & return & return & return & return & "Peter Koppelman (NMLS 24869)" \
         & return & return & "The Manhattan Mortgage Company (NMLS 1546)"& return & "26 West 23rd Street" & return & "New York, NY 10010" into tbody
   revMail temail,, taddr, tbody
   

The problem is with the repeat command. There are 11 records in the temp_deals table and with the code the way it is, the first record shows up 11 times in the body of the email. I can't get the repeat command to increment to the next record in the temp_deals table.

Thanks,


Peter

[dglass]

It looks like, in the code below, you've set both the field separator, and the record separator to 'comma'. I haven't any idea what the result of that would be, but it's highly unlikely it is what you expect it to be.

Code: Select all

put " " into tList3
   put "select loan_type, deal_type, int_rate, payment from temp_deals" into tSQL
   put revdb_querylist (comma,comma, gConID, tSQL) into tList3
   handleRevDBerror tList3
   if the result is not empty then
      answer warning "Didn't get mortgage information " &tList3
      exit mouseUp
   end if

After that, you don't really need to get the count, you already have a list of records from temp_deals, and provided you have them separated by the defaults (comma and return), you can do this:

Code: Select all

repeat for each line thisLine in tList3
put product & return & item 1 of thisLine & tab & tab & item 2 of thisLine & tab & tab & item 3 of thisLine & tab & tab & item 4 of thisLine into product
end repeat

EDIT: fixed the syntax for the repeat loop

[Peter K]

Thank you. That helped a lot. The first field from the temp_deals table is a variable length character field. Sometimes it's 13 characters and sometimes it's 9 or 10. The variable length seems to affect the starting point in the body of the email for the second field (even after the tabs). I really want each line of the body of the email to line up. It's going to clients.

One other thing. I'm using a Mac. When the email populates, I still have to manually send it. Is there a way to automatically send it?

Thanks - you've been really helpful.

Peter

[dglass]

Thank you. That helped a lot. The first field from the temp_deals table is a variable length character field. Sometimes it's 13 characters and sometimes it's 9 or 10. The variable length seems to affect the starting point in the body of the email for the second field (even after the tabs). I really want each line of the body of the email to line up.

Most likely the first field is overrunning the tab stop which is pushing the next tab over. Probably isn't an elegant way of solving that other than to make sure there are enough tabs between the fields when the first field is at its longest. In other words, trial and error.

[dglass]

One other thing. I'm using a Mac. When the email populates, I still have to manually send it. Is there a way to automatically send it?

The revMail command only creates the message. You might be able to send an AppleEvent or AppleScript command to send the message.