King James Bible Reader Lesson 3: Building Text Filter Strings

Hello everyone.
As I stated in the previous lesson we would look at the life issues portion of the King James Bible Reader as a separate lesson. This is that lesson. To start, you should watch the short video below to see how the Life Issues portion of the application works.

Hope you liked the video. Before we look at the filter strings lets look at Alpha Software’s Posting Operation.

The Post operation is a high level function which takes field values from records in a transaction table ( the source) and uses them to replace, add to, or subtract from field values of records in the master table. In our example, we are using verses as the transaction table and reader as the master table. Now if you read the help file from Alpha Software they make a point of saying you cannot post to a memo field or use a memo field as a source for posting. Sorry Alpha Software, but we actually do both.

Our filter parses through the verses database table and finds the records we wish to post. The posting rule, post the matching records in the transaction table to the single memo field record in our reader table. Lets look at how it’s done by starting with the Life Issues List.

if vSelection = "Life Issues" then
vList = <<%Str%
Illness|NA
Illness.Psalms 23|BC__ Psalms 23
Illness.Psalms 27: 13-14|BCVR Psalms 27 13-14
Illness.Psalms 73:26-28|BCVR Psalms 73 26-28
Illness.Psalms 91|BC__ Psalms 91
Illness.Psalms 103|BC__ Psalms 103
Illness.Isaiah 40:31|BCV_ Isaiah 40 31
Illness.Matthew 8:17|BCV_ Matthew 08 17
Illness.John 14:1-6|BCVR John 14 01-06
Illness.John 16:33|BCV_ John 16 33
Illness.Philippians 4:7|BCV_ Philippians 04 07
Illness.1 Thessalonians 4:13-18|BCVR 1 Thessalonians 04:13-18
Illness.James 5:15|BCV_ James 05 15
Financial.1 Samuel 2:7-8|BCVR 1 Samuel 02 07-08
Financial.Psalms 50:10-15|BCVR Psalms 50 10-15
Financial.Psalms 73|BC__ Psalms 73
Financial.Proverbs 11:4|BCV_ Proverbs 11 04
Financial.Proverbs 11:25|BCV_ Proverbs 11 25
Financial.Proverbs 30:8-9|BCVR Proverbs 30 08-09
Financial.Ecclesiastes 5:10-20|BCVR Ecclesiastes 05 10-20
Financial.Jeremiah 9:23-24|BCVR Jeremiah 09 23-24
Financial.Malachi 3:8-10|BCVR Malachi 03 08-10
Financial.Matthew 6:19-21|BCVR Matthew 06 19-21
Financial.Mark 4:1-20|BCVR Mark 04 01-20
Financial.Mark 10:17-31|BCVR Mark 10 17-31
Financial.Luke 16:19-31|BCVR Luke 16 19-31
Financial.2 Corinthians 9:6-14|BCVR 2 Corinthians 09 06-14
Financial.Philippians 4:11-13|BCVR Philippians 04 11-13
Financial.Philippians 4:19|BCV_ Philippians 04 19
Financial.1 Timothy 6:10|BCV_ 1 Timothy 06 10
Financial.James 1:9-11|BCVR James 01 01-11
Financial.James 2:1-7|BCVR James 02 01-07
Financial.1 John 5:14-15|BCVR 1 John 05 14-15
Depression.Psalms 16:11|BCV_ Psalms 16 11
Depression.Psalms 34|BC__ Psalms 34 
Depression.Psalms 40:1-3|BCVR Psalms 40 01-03
Depression.Psalms 42:5|BCV_ Psalms 42 05
Depression.Psalms 55|BC__ Psalms 55
Depression.Psalms 62|BC__ Psalms 62
Depression.Psalms 138:7|BCV_ Psalms 138 07
Depression.Psalms 139|BC__ Psalms 139
Depression.Proverbs 3:5-6|BCVR Proverbs 03 05-06
Depression.Proverbs 15:13|BCVR Proverbs 15 13
Depression.Isaiah 53:4-5|BCVR Isaiah 53 04-05
Depression.Isaiah 61:1-3|BCVR Isaiah 61 01-03
Depression.Matthew 11:28-30|BCVR Matthew 11 28-30
Depression.Romans 8|BC__ Romans 08
Depression.Romans 12:1-2|BCVR Romans 12 01-02
Depression.1 Corinthians 10:13|BCV_ 1 Corinthians 10 13
Depression.2 Corinthians 4:8-9|BCVR 2 Corinthians 04 08-09
Depression.Galatians 2:20|BCV_ Galatians 02 20
Depression.Colossians 1:11-13|BCVR Colossians 01 11-13
Depression.Peter 1:13-16|BCVR Peter 01 13-16
Loneliness.Psalms 23|BC__ Psalms 23
Loneliness.Psalms 27:10-14|BCVR Psalms 27 10-14
Loneliness.Psalms 38:21|BCV_ Psalms 38 21
Loneliness.Ecclesiastes 4:9-11|BCVR Ecclesiastes 04 09-11
Loneliness.Isaiah 58:10|BCV_ Isaiah 58 10
Loneliness.Matthew 28:20|BCV_ Matthew 28 20
Loneliness.Romans 8:28-39|BCVR Romans 08 28-39
Loneliness.Hebrews 13:1-6|BCVR Hebrews 13 01-06
Loneliness.1 John 2:9|BCV_ 1 John 02 09
Fear.Psalms 34:4|BCV_ Psalms 34 04
Fear.Psalms 42:5|BCV_ Psalms 42 05
Fear.Psalms 56:3-4|BCVR Psalms 56 03-04
Fear.Psalms 91|BC__ Psalms 91
Fear.Psalms 118:6|BCV_ Psalms 118 06
Fear.Proverbs 1:32-33|BCVR Proverbs 01 32-33
Fear.Isaiah 41:8-10|BCVR Isaiah 41 08-10
Fear.Isaiah 43:1-2|BCVR Isaiah 43 01-02
Fear.Jeremiah 29:11|BCV_ Jeremiah 29 11
Fear.Matthew 10:26-31|BCVR Matthew 10 26-31
Fear.Luke 12:4-7|BCVR Luke 12 04-07
Fear.Luke 12:32|BCV_ Luke 12 32
Fear.John 14:1|BCV_ John 14 01
Fear.John 14:27-31|BCVR John 14 27-31
Fear.Romans 8:31-39|BCVR Romans 08 31-39
Fear.1 Thessalonians 5:9-11|BCVR 1 Thessalonians 05 09-11
Fear.Peter 5:6-7|BCVR Peter 05 06-07
Worry.Psalms 46|BC__ Psalms 46
Worry.Psalms 50:15|BCV_ Psalms 50 15
Worry.Psalms 127:1-2|BCVR Psalms 127 01-02
Worry.Isaiah 26:3|BCV_ Isaiah 26 03
Worry.Matthew 6:25-34|BCVR Matthew 6 25-34
Worry.Matthew 11:25-30|BCVR Matthew 11 25-30
Worry.Matthew 18:12-14|BCVR Matthew 18 12-14
Worry.John 10:11|BCV_ John 10 11
Worry.John 10:27-30|BCVR John 10 27-30
Worry.John 14:13|BCV_ John 14 13
Worry.John 14|BC__ John 14
Worry.John 27|BC__ John 27
Worry.John 16:33|BCV_ John 16 33
Worry.Romans 8|BC__ Romans 08
Worry.2 Corinthians 2:14|BCV_ 2 Corinthians 02 14
Worry.Philippians 4:4-8|BCVR Philippians 04 04-08
Worry.Hebrews 12:3-6|BCVR Hebrews 12 03-06
Worry.Peter 5:6-8|BCVR Peter 05 06-08
Worry.1 John 5:14|BCV_ 1 John 05 14
Trouble.Job 42:1-6|BCVR Job 42 01-06
Trouble.Psalms 23:4|BCV_ Psalms 23 04
Trouble.Psalms 27:1|BCV_ Psalms 27 01
Trouble.Psalms 34:19|BCV_ Psalms 34 19
Trouble.Psalms 46:1-3|BCVR Psalms 46 01-03
Trouble.Psalms 50:15|BCV_ Psalms 50 15
Trouble.Psalms 91|BC__ Psalms 91
Trouble.Psalms 103:15-19|BCVR Psalms 103 15-19
Trouble.Isaiah 40:29-31|BCVR Isaiah 40 29-31
Trouble.Isaiah 66:13|BCV_ Isaiah 66 13
Trouble.Matthew 6:19-34|BCVR Matthew 06 19-34
Trouble.John 14:27|BCV_ John 14 27
Trouble.John 16:33|BCV_ John 16 33
Trouble.2 Corinthians 1:3-4|BCVR 2 Corinthians 01 03-04
Trouble.2 Corinthians 4:7-10|BCVR 2 Corinthians 04 07-10
Trouble.2 Corinthians 4:17-18|BCVR 2 Corinthians 04 17-18
Trouble.Hebrews 12:1-9|BCVR Hebrews 12 01-09
Trouble.James 1:2-4|BCVR James 01 02-04
Trouble.1 Peter 5:6-11|BCVR 1 Peter 05 06-11
Decision.Joshua 1:1-9|BCVR Joshua 01 01-09
Decision.Joshua 24:15|BCV_ Joshua 24 15
Decision.Judges 21:25|BCV_ Judges 21 25
Decision.1 Kings 18:21|BCV_ 1 Kings 18 21
Decision.Psalms 37:5|BCV_ Psalms 37 05
Decision.Psalms 119:9|BCVR Psalms 119 09
Decision.Psalms 119:105|BCV_ Psalms 119 105
Decision.Proverbs 3:5-6|BCVR Proverbs 03 05-06
Decision.Matthew 6:24|BCV_ Matthew 06 24
Decision.Matthew 7:12-14|BCVR Matthew 07 12-14
Decision.Matthew 17:20|BCV_ Matthew 17 20
Decision.Matthew 26:41|BCV_ Matthew 26 41
Decision.Ephesians 2:8-10|BCVR Ephesians 08 08-10
Decision.Ephesians 4:11-14|BCVR Ephesians 04 11-14
Decision.Hebrews 13:9|BCV_ Hebrews 13 09
Decision.James 1:5-8|BCVR James 01 05-08
Decision.James 1:5-16|BCVR James 01 05-16
Decision.1 Peter 3:12|BCV_ 1 Peter 03 12
Bereavement.Psalms 46|BC__ Psalms 46
Bereavement.Psalms 55:22|BCV_ Psalms 55 22
Bereavement.Isaiah 25:8|BCV_ Isaiah 25 08
Bereavement.Isaiah 61:1-3|BCVR Isaiah 61 01-03
Bereavement.Matthew 5:4|BCV_ Matthew 05 04
Bereavement.Luke 6:21|BCV_ Luke 06 21
Bereavement.Luke 7:11-17|BCVR Luke 07 11-17
Bereavement.John 11:25|BCV_ John 11 25
Bereavement.John 14:1-3|BCVR John 14 01-03
Bereavement.John 14:27|BCV_ John 14 27
Bereavement.John 16:20|BCV_ John 16 20
Bereavement.1 Corinthians 15:20-22|BCVR 1 Corinthians 15 20-22
Bereavement.2 Corinthians 1:3-4|BCVR 2 Corinthians 01 03-04
Bereavement.2 Corinthians 5:1-8|BCVR 2 Corinthians 05 01-08
Bereavement.Philippians 1:21-23|BCVR Philippians 01 21-23
Bereavement.Philippians 3:20|BCV_ Philippians 03 20
Bereavement.1 Thessanlonians 4:13-18|BCVR 1 Thessalonians 04 13-18
Bereavement.Revelation 14:13|BCV_ Revelation 14 13
Death.Psalms 23|BC__ Psalms 23
Death.Psalms 138:7|BCV_ Psalms 138 07
Death.Daniel 12:13|BCV_ Psalms 12 13
Death.John 6:47|BCV_ Psalms 06 47
Death.John 11:25-27|BCVR John 11 25-27
Death.John 14:2-3|BCVR John 14 02-03
Death.John 17|BC__ John 17
Death.John 20|BC__ John 20
Death.Romans 8|BC__ Romans 08
Death.Romans 14:8|BCV_ Romans 14 08
Death.1 Corinthians 15|BC__ 1 Corinthians 15
Death.2 Corinthians 4:17-18|BCVR 2 Corinthians 04 17-18
Death.1 Thessalonians 4:13-18|BCVR 2 Thessalonians 04 13-18
Death.Revelation 21:1-4|BCVR Revelation 21 01-04
Sin.Genesis 3|BC__ Genesis 03
Sin.Genesis 6:5-6|BCVR Genesis 06 05-06
Sin.Genesis 6:11|BCV_ Genesis 06 11
Sin.Genesis 19|BC__ Genesis 19
Sin.Exodus 20|BC__ Exodus 20
Sin.Psalms 51|BC__ Psalms 51
Sin.Psalms 130|BC__ Psalms 130
Sin.Matthew 5:48|BCV_ Matthew 05 48
Sin.Matthew 26:69-75|BCVR Matthew 26 69-75
Sin.Romans 3:23-24|BCVR Romans 03 23-24
Jesus Christ.Genesis 3:15|BCV_ Genesis 03 15
Jesus Christ.Psalms 22|BC__ Psalms 22
Jesus Christ.Isaiah 7:14|BCV_ Isaiah 07 14
Jesus Christ.Isaiah 53|BC__ Isaiah 53
Jesus Christ.Luke 2|BC__ Luke 02
Jesus Christ.Luke 9:28-36|BCVR Luke 09 28-36
Jesus Christ.Luke 9:22-24|BCVR Luke 09 22-24
Jesus Christ.John 11|BC__ John 11
Jesus Christ.John 14|BC__ John 14
Jesus Christ.John 15|BC__ John 15
Jesus Christ.John 19:21|BCV_ John 19 21
Jesus Christ.Ephesians 1:15-23|BCVR Ephesians 01 15-23
%str%

end if
display:
if scdv = 1 then
    scdv = 0
else
    scdv = 1
end if

This list is created when the user selects life issues from the Volume List on our form which is represented with the variable vSelection. When a user chooses a life issues from our displayed list, vBooks the user sees only the portion of the list value to the left of the pipe character ‘|’ the software see the portion to the right. As an example, If the user selects

Illness.Psalms 27: 13-14

from the Life Issue List the software sees

BCVR.Psalms 27 13-14

We use this string to build our filter. BCVR stands for Book Chapter Verse Range so the software now looks for the book name ‘Psalms’, the chapter ’27’ and the range ‘verse 13 to 14’. In our verses table we have a field called vLink which consist of the Book Name the chapter number and the verse number.

Psalms 27 13 (record nbr 14299)

Psalms 27 14 (record nbr 14300)

Now all we had to do in our filter is set the value of vlink to equal the book name, the chapter number and the verses beginning and ending range values. The entire filter string is listed below.

post.t_filter = "Between(Alltrim(Vlink),Left(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5),Len(Alltrim(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5)))-6)+\" \"+Right(Left(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-3),2),Left(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5),Len(Alltrim(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5)))-6)+\" \"+Right(Alltrim(Var->vBooks),2))"

As you can see, we use Alltrim, Left, Right  and Len to break the user selected string into the components we needed and the posting rule does the rest. A similar process is used for finding ‘BC’ (Book Chapter) and ‘BCV’ (Book Chapter Verse). Here is the entire script ‘Display Life Issues’

'Display Life Issues

if Left(Alltrim(var->vBooks),4) = "BCVR" Then
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 = "Between(Alltrim(Vlink),Left(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5),Len(Alltrim(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5)))-6)+\" \"+Right(Left(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-3),2),Left(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5),Len(Alltrim(Right(Alltrim(Var->vBooks),Len(Alltrim(Var->vBooks))-5)))-6)+\" \"+Right(Alltrim(Var->vBooks),2))"
post.m_count = 1
else if Left(Alltrim(var->vBooks),4) = "BCV_" Then
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 = "Right(Alltrim(var->vBooks),Len(Alltrim(var->vBooks))-5) = Alltrim(Vlink)"
post.m_count = 1
else if Left(Alltrim(var->vBooks),4) = "BC__" Then
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 = "Right(Alltrim(var->vBooks),Len(Alltrim(var->vBooks))-5) = Left(Vlink,len(Alltrim(Vlink))-3)"
post.m_count = 1
end if    

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()

There you have it. We have successfully posted memo fields from a transaction table to a master table using a filter based on user selection from a list object. Pretty impressive if I do say so myself.

Well that’s it for today. I hope you found this lesson helpful and our next lesson will cover searching memo fields for keywords or phrases and posting the results back to our VBooks List. 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

www.cdc-takecharge.com

and inquire or contact

NLawson@cdc-TakeCharge.com

Have a great day.