Hello everyone
This is our final lesson on building a King James Bible reader in Alpha Software. Below is a short video which wraps up the final pieces of object code for our application and shows the solution for our new range query function.
First we will look at the code for our new range feature.
'Date Created: 04-Mar-2013 12:44:02 PM
'Last Updated: 07-Mar-2013 01:26:10 PM
'Created By : cdc
'Updated By : cdc
parentform.commit()
tbl = table.open("reader")
tbl.change_begin()
tbl.Written_text = " "
tbl.change_end(.t.)
tbl.close()
if BVrs <> "" then
if BVrs < EVrs .and. BChpt = EChpt then
CName = BBook+":"+BChpt+":"+BVrs+":"+EVrs
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "verses"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.val(word(Var->CName,2,\":\",1))=Chapter_Nbr.and.between(Verse_Nbr, val(word(Var->CName,3,\":\",1)), val(word(Var->CName,4,\":\",1)))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@verses->Kjb_By_Verse)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
else if BVrs = EVrs .and. BChpt = EChpt then
CName = BBook+":"+BChpt+":"+BVrs+":"+EVrs
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "verses"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.val(word(Var->CName,2,\":\",1))=Chapter_Nbr.and.between(Verse_Nbr, val(word(Var->CName,3,\":\",1)), val(word(Var->CName,4,\":\",1)))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@verses->Kjb_By_Verse)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
else if BVrs < EVrs .and. BChpt < EChpt then
CName = BBook+":"+BChpt+":"+EChpt+":"+BVrs+":"+EVrs
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "chapters"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.between(Chapter_Nbr, val(word(Var->CName,2,\":\",1)), val(word(Var->CName,3,\":\",1))-1)"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@chapters->Kjb_By_BookandChapter)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
'Pull range last chapter
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "verses"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.val(word(Var->CName,3,\":\",1))=Chapter_Nbr.and.between(Verse_Nbr, val(word(Var->CName,4,\":\",1)), val(word(Var->CName,5,\":\",1)))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@verses->Kjb_By_Verse)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
else if BVrs = EVrs .and. BChpt < EChpt then
CName = BBook+":"+BChpt+":"+EChpt+":"+BVrs+":"+EVrs
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "chapters"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.between(Chapter_Nbr, val(word(Var->CName,2,\":\",1)), val(word(Var->CName,3,\":\",1))-1)"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@chapters->Kjb_By_BookandChapter)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
'Pull range last chapter
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "verses"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.val(word(Var->CName,3,\":\",1))=Chapter_Nbr.and.between(Verse_Nbr, val(word(Var->CName,4,\":\",1)), val(word(Var->CName,5,\":\",1)))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@verses->Kjb_By_Verse)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
end if
else if BVrs = "" then
if BChpt = "" then
CName = BBook
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "books"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "Var->CName=Bookname"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@books->Kjb_By_Verse)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
else if BChpt < EChpt then
CName = BBook+":"+BChpt+":"+EChpt
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "chapters"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.between(Chapter_Nbr, val(word(Var->CName,2,\":\",1)), val(word(Var->CName,3,\":\",1)))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@chapters->Kjb_By_BookandChapter)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
else if BChpt = EChpt
CName = BBook+":"+BChpt+":"+EChpt
tbl = table.open("Reader")
tbl.batch_begin()
post.t_db = "chapters"
post.m_key = "Id"
post.t_key = "Id"
post.m_filter = ""
post.t_filter = "word(Var->CName,1,\":\",1)=Bookname.and.Chapter_Nbr=val(word(Var->CName,2,\":\",1))"
post.m_count = 1
post.m_field1 = "WRITTEN_TEXT"
post.m_exp1 = "WRITTEN_TEXT+Alltrim(@chapters->Kjb_By_BookandChapter)+crlf(2)"
post.t_count = 0
tbl.post()
tbl.batch_end()
tbl.close()
end if
end if
parentform.Refresh_Layout()
This looks like a fair amount of code, but if you review the previous lesson, we use the same framework as in that lesson only we now build a filter based on how the user fills out the range form.
Now lets look at the code for our export dialog on the bookmark form.
'Date Created: 23-Feb-2013 03:08:21 PM
'Last Updated: 23-Feb-2013 03:08:21 PM
'Created By : cdc
'Updated By : cdc
parentform.commit()
dim Shared sachoice as C
dim Shared BMchoice as C
DIM SHARED NTchoice as C
DIM SHARED exchoice as C
DIM SHARED varC_result as C
ok_button_label = "&Continue"
cancel_button_label = "&Cancel"
Delete XdialogStyle
dim XDialogStyle as p
'#127+127+127
XDialogStyle.AccentColor = "Canvas"
XDialogStyle.Color = "Pink White"
varC_result = ui_dlg_box("TakeCharge Export Dialog",<<%dlg%
{Windowstyle=Gradient Radial Bottom Right}
;
{lf};
{region}
{ymargin=0,0}
{xmargin=1,1}
{ysize=0}
{blueframe=1,1: Export What? }
{sp=5}{region}(NTchoice:Book Mark);
{sp=5}(NTchoice:My Thoughts);
{sp=5}(NTchoice:Both)
;
{endregion};
{lf};
{line=1,0};
{lf};
{ymargin=0,0}
{xmargin=1,1}
{ysize=0}
{blueframe=1,1: Export To? }
{sp=5}{region}(exchoice:E-Mail)|
{sp=3}(exchoice:EM Attach);
{sp=5}(exchoice:Plain Text)|
{sp=3}(exchoice:PDF);
{sp=5}(exchoice:Rich Text)|
{sp=3}(exchoice:HTML)
{endregion};
;
{endregion};
{lf};
{region}
;
{sp}<*15=ok_button_label!CONTINUE> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%,<<%code%
if a_dlg_button = "Continue" then
BMchoice = NTchoice
sachoice = exchoice
else
End
end if
%code%)
if sachoice = "PDF" then
record_number = rnbr
query.filter = "BM_Name = '" + AName + "' "
query.order = ""
if BMChoice = "Book Mark" then
:Report.SaveAs("Bookmark","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-BM "+time("dd-MM-yyyy")+".pdf",.f.)
else if BMchoice = "My Thoughts" then
:Report.SaveAs("My Thoughts","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-MT "+time("dd-MM-yyyy")+".pdf",.F.)
else
:Report.SaveAs("Bookmark w Notes","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-Both "+time("dd-MM-yyyy")+".pdf",.F.)
end if
else if sachoice = "Plain Text" then
record_number = rnbr
query.filter = "BM_Name = '" + AName + "' "
query.order = ""
if BMChoice = "Book Mark" then
:Report.SaveAs("Bookmark","TXT",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-BM MT "+time("dd-MM-yyyy")+".txt",.F.)
else if BMchoice = "My Thoughts" then
:Report.SaveAs("My Thoughts","TXT",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-"+time("dd-MM-yyyy")+".txt",.F.)
else
:Report.SaveAs("Bookmark w Notes","TXT",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-Both"+time("dd-MM-yyyy")+".txt",.F.)
end if
else if sachoice = "Rich Text" then
record_number = rnbr
query.filter = "BM_Name = '" + AName + "' "
query.order = ""
if BMChoice = "Book Mark" then
:Report.SaveAs("Bookmark","RTF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-BM "+time("dd-MM-yyyy")+".rtf",.F.)
else if BMchoice = "My Thoughts" then
:Report.SaveAs("My Thoughts","RTF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-MT "+time("dd-MM-yyyy")+".rtf",.F.)
else
:Report.SaveAs("Bookmark w Notes","RTF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-Both "+time("dd-MM-yyyy")+".rtf",.F.)
end if
else if sachoice = "HTML" then
record_number = rnbr
query.filter = "BM_Name = '" + AName + "' "
query.order = ""
if BMChoice = "Book Mark" then
:Report.SaveAs("Bookmark","HTML",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-BM "+time("dd-MM-yyyy")+".html",.F.)
else if BMchoice = "My Thoughts" then
:Report.SaveAs("My Thoughts","HTML",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-MT "+time("dd-MM-yyyy")+".html",.F.)
else
:Report.SaveAs("Bookmark w Notes","HTML",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+"-Both "+time("dd-MM-yyyy")+".html",.F.)
end if
else if sachoice = "EM Attach" then
xbody = ""
record_number = rnbr
query.filter = "BM_Name = '" + AName + "' "
query.order = ""
if BMChoice = "Book Mark" then
:Report.SaveAs("Bookmark","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+".pdf",.F.)
attachments = Alltrim(Var->AName)+".pdf"
else if BMchoice = "My Thoughts" then
:Report.SaveAs("My Thoughts","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)+".pdf",.F.)
attachments = Alltrim(Var->AName)+".pdf"
else
:Report.SaveAs("Bookmark w Notes","PDF",query.filter,query.order,a5.get_path()+chr(92)+"Documents"+chr(92)+Alltrim(Var->AName)++".pdf",.F.)
attachments = Alltrim(Var->AName)+".pdf"
end if
goto em
else
attachments = ""
t = table.open("bookmarks")
t.fetch_goto(rNbr)
if BMChoice = "Book Mark" then
xbody = Var->Aname+crlf()+t.written_text
else if BMchoice = "My Thoughts" then
xbody = Var->Aname+crlf()+t.my_thoughts
else
xbody = Var->Aname+crlf()+t.written_text+t.my_thoughts
end if
goto em
end if
end
em:
xSubj = Var->AName
email_client_external(xEM,xSubj,xBody, attachments)
You should notice we use the function .SaveAs() to create the export file for all options except E-Mail. There we fetch the current record in the bookmark table then assign the value of written text to a variable xBody which is used in the email_client_external() function.
There you have it. We created an application from the ground up and met all of our objectives.
Well that’s it for today. I hope you found this lesson helpful and I hope you enjoyed this series.
Our next series will be on graphics in Alpha Software and how to do animation. I hope to have it up in a few days.
Remember, if you need help with an Alpha Software application or wish to inquire about a custom application for your business go to our website
and inquire or contact
NLawson@cdc-TakeCharge.com
Have a great day.
Custome Database Design with Alpha Software 
Leave a comment