Need help with "Search by Bible reference"

[baytonemus]

I'm developing a database of songs that needs to be searchable by multiple criteria/fields. One of those criteria is Bible reference. The trouble is that such a reference consists of 3 distinct parts: book (Matthew), chapter, and verse(s). A record (song) also needs to be able to contain numerous individual references, (i.e., Matthew 26:26-29 and Mark 14:22-25), perhaps more than a dozen. I'm having trouble determining a way of isolating individual references.

For example, if I include both of the above references with some kind of separator in a single field then search for 'Matthew 14', it will return this record even though I don't want it. I've also experimented with creating separate repeating fields for book, chapter, and verse but the results are the same.

Although I've used FM for a long time, I guess I've never needed to perform a find quite like this. Any help - suggestions for resources or tutorials, etc - will be much appreciated.

Thanks - JAY

[Fenton]

Mark 14:22-25 is really, from a relational point of view, these values, separated by returns;

Mark 14:22

Mark 14:23

Mark 14:24

Mark 14:25

It's the sort of job that could be most easily done with a Custom Function. But it doesn't appear that you have Developer 7, so you can't implement that. A series of case statements would also work, but tedious. Or a script; but then how/when to run it?

If it has some Mathew ones, and some Mark ones, what is the structure for that? You haven't really told us enough about that.

Edited November 19, 2007 by Guest

[baytonemus]

Thanks for your reply, Fenton. You are correct, I do not have Developer although I am planning to upgrade to FMP9 shortly. A few more details about the way I'm hoping this can work.

This needs to function as a "search engine" for customers on my website. Because of that, it will need to be pretty simple/intuitive, meaning that I don't think I can expect them to employ special characters for searching. (I realize that "Mark 14" (with quotation marks) or similar would isolate that text string and eliminate most or all erroneous returns, but I doubt most people would go to the trouble to read the instructions I'd have to provide.)

Ideally, I'd like for them to be able to include verse citations in their search. However, if that proves too complicated, book and chapter will probably suffice.

As for the structure of the references about which you asked, there is currently no specific structure at all. The lyrics of each song may either quote directly or simply borrow a metaphor from numerous different passages in the Bible. I currently have those references lumped as text in a single field. Here's one of the longer lists from one of the records:

Exodus 16:1-36; Numbers 11:1-9; Deuteronomy 8:1-20; Matthew 14:13-33; Matthew 26:26-29; Mark 6:32-52; Mark 8:1-10; Mark 14:22-25; Luke 9:10-17; Luke 22:17-20; John 6:1-51; 1 Corinthians 11:23-26

My customers may be looking for music that connects to one of those passages, (or searching in other fields such as title, topic, song text, or liturgical day).

In the end, I'll want it to return results in a list (different layout) with links that will take customers to individual song pages, (not FileMaker pages). So that's my vision for the thing in a nutshell.

Thanks again for your interest.

JAY

[Fenton]

The only way I can see the above working is for you to take that long comma-separated list and transform it to something more useful. You cannot search "Exodus 16:1-36" and expect to find "Exodus 16:10". So that has got to be expanded, as I said earlier. You will have to do that to each of these comma-separated "multi-values", each of the many values of each on a separate line, with all the data it needs (or a separate repeat; but I think I'd just do the lines).

It could be done via a looping script, or with a pretty extensive Custom Function (which I'm not very good at writing, and which requires FileMaker Pro Advanced). Since it sounds like you're the one entering the data, then you should be able to run the script, after entry or edit.

Basically the way the data is now looks good to a human being, and is compact, but is badly crippled for database functionality.

Also, if people want to enter it that way in a search, and enter more than one passage, then you're going to have to script the search as well, breaking out the pieces much like the loop for the data.

An alternative way of doing it would be to expand only the numbers within the dashes, the verses, using separate fields for the book and chapter. The would also work, but would require a carefully scripted Find, with Constrain. The date would be more compact, but then the Find script would be (much) more difficult.

[Ocean West]

if every song/lyrics, had each book/chapter/verse/ assigned to it then on the search side of things you could trap for "-" and filter out "spaces" or unknown books

then if someone types in Mark 14:22-25

perhaps 3 global fields

Book: "Mark" - a drop down list of all 66 books

Chapter: 14 - a number field

Verse: 22-25 - a text field

you could create some method to extrapolate the range between 22-25

that would return 22¶23¶24¶25

based upon the valueCount of this list of 4 you could script a find that will perform a find for:

mark 14 22

mark 14 23

mark 14 24

mark 14 25

[David Jondreau]

I think he could do it with a separate table for References keeping track of:

Book

Chapter

Start Verse

End Verse

for each reference. I agree the searching will have to be completely scripted.

[comment]

You can enumerate a range using a repeating calculation field, instead of a custom function.

Separating book, chapter and verse (or starting/ending verse in case of a range) into individual fields is highly recommended - even though they could be recombined in the calc.

[baytonemus]

Thanks to all who have responded. I am out of the office all day today so I'll have to dig back into this tomorrow. I can see that I've got some reading to do in order to fully understand your various recommendations. I'm sure I'll have a few more specific questions at that point.

Thanks again!

JAY

[baytonemus]

Having spent some time reading the FM help files last night in an attempt to understand the recommendations of each of you who replied so graciously, I'm thinking this may be a bit out of my league. Although I'm sure I could learn what would be necessary, I'm not sure I have the time...or if it would be worth it for this one search feature. I'm sure I could hire someone to write the necessary scripts/formulas for me or just do it more simply.

In any case, I'm going to ask a few questions and if any of you are willing to hang in there with me a little further, great!

if every song/lyrics, had each book/chapter/verse/ assigned to it then on the search side of things you could trap for "-" and filter out "spaces" or unknown books

I don't know how to "trap."

then if someone types in Mark 14:22-25

perhaps 3 global fields

I thought global fields contained identical data in every record. How would that help me in this case?

Book: "Mark" - a drop down list of all 66 books

Chapter: 14 - a number field

Verse: 22-25 - a text field

you could create some method to extrapolate the range between 22-25

that would return 22¶23¶24¶25

based upon the valueCount of this list of 4 you could script a find that will perform a find for:

mark 14 22

mark 14 23

mark 14 24

mark 14 25

Yeah, you lost me there. Sorry.

JAY

[baytonemus]

You can enumerate a range using a repeating calculation field, instead of a custom function.

Sorry, but I don't know how to do this either.

Separating book, chapter and verse (or starting/ending verse in case of a range) into individual fields is highly recommended - even though they could be recombined in the calc.

Several of you mentioned separate fields for book, chapter, and verse. However, when I tried that, I got false returns. Consider my original example of Matthew 26:26-29 and Mark 14:22-25. If I have both Matthew and Mark entered in the book field and the numbers 26 and 14 entered in the chapter field, a search for Matthew 14 will incorrectly bring up this record. Is there of way of contraining a search to the same repetition across multiple fields?

JAY

[baytonemus]

I think he could do it with a separate table for References keeping track of:

Book

Chapter

Start Verse

End Verse

Is your thought that this table would have a separate record for each reference/range of verses (or each individual verse) with a song name field that could have multiple entries?

JAY

[David Jondreau]

Not exactly. Each record would only refer to a single song. The idea is to NOT have more than one value in a field.

Each record in the References table will have a unique combination of SongID, Book, Chapter, Start Verse, and End Verse.

You then relate this table to the Song table and each song could have multiple related records depending on the number of verses it references.

[comment]

The global fields were suggested as a means for the user to enter search criteria, to be then used by a script to search for them. As you are discovering, this is not as simple as it might seem. Unless your users know Filemaker quite well, a scripted search would be required to assist them.

If a song record can reference not just multiple verses from a single chapter, but also multiple chapters and/or books, you will really need another table of References, as David has explained.

[baytonemus]

Not exactly. Each record would only refer to a single song. The idea is to NOT have more than one value in a field.

Each record in the References table will have a unique combination of SongID, Book, Chapter, Start Verse, and End Verse.

You then relate this table to the Song table and each song could have multiple related records depending on the number of verses it references.

OK, I'll dig into this first with a few records and see if I can get it happening. Thanks again.

The global fields were suggested as a means for the user to enter search criteria, to be then used by a script to search for them. As you are discovering, this is not as simple as it might seem. Unless your users know Filemaker quite well, a scripted search would be required to assist them.

My users will not know FM at all. Although I have limited experience with scripted searching, I was assuming that this would be the case. I guess I'll fall off of that bridge when I get to it.

Happy Thanksgiving to you all!

JAY

[Ocean West]

I am attaching a sample file...

Here you will have a table of song number song title and lyrics.

By relationship you will reference every book/chapter/verse in each song.

When you wish to search for all songs based upon a passage you perform a search in the reference table for all songs that have the criteria you are searching for...

if you want to find songs that span verses you can simply use the range feature "..." as you are in a find mode.

when you perform the find it will then take you to all related records (back to the songs)

I also included a web viewer that will show you the scriptural reference for the song.

Songs.fp7.zip

[baytonemus]

Wow, this is REALLY generous of you, Stephen. Thanks so much! I'll dig into it tomorrow and study the file. (No football for me.)

JAY

[baytonemus]

OK, I've spent a little time analyzing the example file you created for me, (thanks again), and can now ask the (somewhat) more informed questions that follow.

I am attaching a sample file...

Here you will have a table of song number song title and lyrics.

What is the advantage to creating a "Song Number" field as over and against simply using the "Song Title" which will also be unique?

By relationship you will reference every book/chapter/verse in each song.

When you wish to search for all songs based upon a passage you perform a search in the reference table for all songs that have the criteria you are searching for...

if you want to find songs that span verses you can simply use the range feature "..." as you are in a find mode.

I know this may seem fussy but is there any way to allow the user to enter "-" to indicate a range and have FM substitute "..."?

when you perform the find it will then take you to all related records (back to the songs)

I went ahead and added a couple more songs in the Songs table that reference the same range of verses in Mark and added titles. However, when I run the script searching for any or all of those verses, I get only a single record returned. Any idea why? I've attached my modified file.

I also included a web viewer that will show you the scriptural reference for the song.

That's cool. Will this only be active when actually using Instant Web Publishing? I haven't used that yet. My web hosting company supports FMStudio from FMWebSchool and I'm planning to use that product to integrate the database and search functionality into my website.

Thanks again -JAY

Songs_FMForum_exmpl.fp7.zip

[Ocean West]

What is the advantage to creating a "Song Number" field as over and against simply using the "Song Title" which will also be unique?

Standard method of development, the problem if you use the Title is that what happens when you type it in then assign references then realize you misspelled the title, If you change the title you orphan (unreferenced records) all the references and thus have to re type them in.

I know this may seem fussy but is there any way to allow the user to enter "-" to indicate a range and have FM substitute "..."?

Probably, but not in in "find mode" auto formatting i don't think works in find mode?

you would have to build another scripted find method.

I went ahead and added a couple more songs in the Songs table that reference the same range of verses in Mark and added titles. However, when I run the script searching for any or all of those verses, I get only a single record returned. Any idea why? I've attached my modified file.

what to you put in as search criteria?

i typed in the book chapter and for the verse type ##...## substituting the start and end value such as 22...24

this returns 3 records....

(BTW are you using the latest version of FMP?)

The web viewer is only a sample of what you can do to pull data from a web site based upon

records in your database - it may work if you publish your database to the web, but that was not my intent.

[David Jondreau]

What is the advantage to creating a "Song Number" field as over and against simply using the "Song Title" which will also be unique?

Will it? Link

Regardless, it's a principle of good Filemaker design to use numbers the user never attaches meaning to for relationships. What if the song title is entered incorrectly? If you correct it, all the relationships will break.

I know this may seem fussy but is there any way to allow the user to enter "-" to indicate a range and have FM substitute "..."?

You could, by adding a script step that replaces contents of the verse field. Look up Substitute() and Replace Field Contents[].

That's cool. Will this only be active when actually using Instant Web Publishing?

The Web Viewer is a feature of Filemaker that has nothing to do with IWP.

[baytonemus]

Standard method of development, the problem if you use the Title is that what happens when you type it in then assign references then realize you misspelled the title, If you change the title you orphan (unreferenced records) all the references and thus have to re type them in.

OK, that makes sense.

I'm doing the same as you, I think - selecting "Find Songs" from the Script Menu; choosing "Mark" from the book pop-up list; typing "14" in the chapter field; entering "22...25" or a single verse, such as "22" in the verse field; then I press "continue" or hit Enter. Just tried it again. Returned only the first record.

FMP 7.0v3. Planning to buy v.9 soon, just haven't gotten around to it yet.

JAY

[baytonemus]

Seems like I screwed up this last reply so I'm attempting to fix it now.

You could, by adding a script step that replaces contents of the verse field. Look up Substitute() and Replace Field Contents[].

Alright, I'll give that a try. Stephen made some reference to auto formatting perhaps not working in find mode. I'll let him weigh in if this is what he was referring to.

The Web Viewer is a feature of Filemaker that has nothing to do with IWP.

So maybe this should be working for me right now? Or, is it a FMP8 or later feature?

Edited November 24, 2007 by Guest

[Ocean West]

Download the 30 day demo of FMP9

http://www.filemakertrial.com/nskto/form/entry.aspx?ovmkt=78D2413C106A4E5FAB7AD2615CB53039&WT.mc_id=78D2413C106A4E5FAB7AD2615CB53039

give it a try . its been awhile since i worked in fm7

[baytonemus]

Download the 30 day demo of FMP9

http://www.filemakertrial.com/nskto/form/entry.aspx?ovmkt=78D2413C106A4E5FAB7AD2615CB53039&WT.mc_id=78D2413C106A4E5FAB7AD2615CB53039

give it a try . its been awhile since i worked in fm7

I just installed the FMP9 demo. The web viewer feature now works correctly. Pretty cool. I'll be buying that today.

Also, it appears I was able to permit the use of a hyphen in the verse search field by adding an Insert Calculated Result/Substitute script step. Thanks, David J.

I'm still a long ways from having this ready so I may be back. But, for now, let me just say "thanks" to those who have helped - especially Stephen for creating the example file. I can't tell you how much I appreciate it.

Peace - JAY

[baytonemus]

I've had a very productive week working in FMP9. Thanks for the nudge to upgrade. I am hung up right now with one aspect of my find script, however.

I've attached two files. The first is a renamed and slightly adapted version of the example file Stephen created for me (Songs_FMForum_exReplace). I've added the substitute calculation to his script to allow users to search verse range using "-" instead of "..." and have also added a few records.

The other file is my full database (SongRef for FMForum) which I've tried to adapt to match Stephen's design.

There seems to be some slight difference in the search functionality because I'm getting false returns in my database find script but not in Stephen's. I've compared the scripts and the database/fields and I can't find any significant difference. Anyone willing to take a look at this please try the following:

Open SongRef for FMForum

Run the Search by ScriptureRef script

Search for Book:John Chapter:10 Verses 4-5 (or 4...5)

It will return all of the songs John 20 although it should only return one.

Next

Open Songs_FMForum_exReplace

Run Find Songs script

Search for Book:Mark Chapter:14 Verses 4-5 (or 4...5)

It will return only the song with those specific verses and NOT the other records from Mark 14.

See anything I'm missing?

Two items that could be issues...

When I created the records in the Scripture References table of my database, I mistakenly made the Chapter field a text field. I think I was remembering this suggestion from an early response to this thread and didn't double check Stephen's fields. In any case, I eventually caught it and changed the field to a number field. Maybe this is the problem.

The other thing that has occurred to me is that this database was originally created in an earlier version of FMP, perhaps as early as v.4. Could that be causing problems? Just looking for ideas.

Thanks again - JAY

Edited December 2, 2007 by Guest

[Fenton]

You didn't retarget his field references after copy/pasting his script:

/*Substitute ( Reference::Verse ; "-" ; "..." )*/

The /*whatever*/ is FileMaker's way of commenting out something that just won't work. It preserves the content, so you can see what it was, so you can fix it. But anything between the /* */ is disabled.

This works:

Substitute ( Scripture References::BibleVerse ; "-" ; "..." )

[baytonemus]

That's it! Man, I looked at that thing 100 times and I don't know why I couldn't see it. Thanks so much. I also appreciate the heads-up in the FM /*x*/ warning mechanism. I'm sure that will be helpful in the future.

JAY

[Fenton]

It's more of a commenting syntax than a warning mechanism. It can be used to comment out multiple lines of, well, comments in calculations.

Another thing you might want to do is read the Import.log text file, which is created when you import or copy/paste FileMaker objects from one file/table to another. It shows where something didn't match up. If you're working on the file locally Import.log will be in the same folder. Otherwise, with hosted files, someplace like your Documents folder.

[baytonemus]

I found the Import.log file and looked it over. Also very helpful. Thanks again.

JAY

[ralph.nusser]

1. Serializing the bible books and

2. serializing the bible verses.

The needed solution is very easy to accomplish now with the custom function 14 of Ray Cologon, NightWing Enterprises

Custom Function: _NumRange.

See attached example file.

Happy FileMaking Ralph Nusser

Bible_Verses_SOGETES_1.0.0.zip

[baytonemus]

Thanks, Ralph. That certainly would make the record creation task less tendious on the reference side.

JAY

[Lee Smith]

Hi Ralph,

Good example of how to use the CF, unfortunately, the OP has v7 (SE, and not Developer). The CF uses recursion, so doing this as a Let Calculation isn't an option either.

[ralph.nusser]

It is easily possible to do it without a custom function with recursion.

Number range relationship:

1. From serial ≤ serial bible verse reference

2. To serial ≥ serial bible verse reference

See example in the attachment.

Happy FileMaking Ralph Nusser

Bible_Verses_SOGETES_1.0.1.zip