Finding Range of Records

[JScibello]

I have a database of 25,000 songs that I have on CD. I don't know how to search for a range of artists say between the Beatles & the Blues Brothers. Or Titles say between ABC & Call Me. Can you help me. I'm pretty new at this.

[LiveOak]

This will get a little complex. FM has no notion of "range" for text values. You would need to create one, say by assigning a number to each title or artist's name based upon some calculation. A very simple case is to take the first letter of the title and assign a number from 1-26 (what to do with the titles starting with numbers!). The search entry text would also be converted to a number. Searching from ABC to Call Me would become from 1 to 3. This is too simple to be useful, but a number could be based upon the first two, three or four letters. You would probably have to assign numbers for each letter, for a blank, and for numbers. For instance:

Case(

Left(Title,1) = "0", 0,

Left(Title,1) = "1", 1,

Left(Title,1) = "2", 2,

Left(Title,1) = "3", 3,

Left(Title,1) = "4", 4,

Left(Title,1) = "5", 5,

Left(Title,1) = "6", 6,

Left(Title,1) = "7", 7,

Left(Title,1) = "8", 8,

Left(Title,1) = "9", 9,

Left(Title,1) = "A", 10,

Left(Title,1) = "B", 11,

Left(Title,1) = "C", 12,

Using this scheme, you could do three letters by padding out the single digit numbers to 01,02 etc. for each letter, concatenating them to form a text string (012635) and converting this to a number. This would turn the alphabetical order into a number which preserved the same order (call it a code of index). Your start and end search phrases would also be converted to two numbers. The search presented to FM would then just be for a number range 126...348.

Hope this makes sense, it is almost easer to do than describe.

-bd

[Bruenor]

the filemaker user's guide says it all in section 3-4.

in case you don't have the guide, enter find mode and look at the symbols you can use in the left hand column. Should be self-explanatory from there on.

~Ati, Jedi Master.

[ July 20, 2001: Message edited by: Bruenor ]

[JPaul]

I have posted this tip a month ago (but it's disappeared) in replying to a swedish fellow having the same problem.

This version of ‘partial search’ does work with any version of FM.

Keep in mind that i’ve designed this “BABYLONIA” in order to issue searches “from …to” in literal fields (a type of search that usually fails in FM) , then you can investigate it and adapt it accordingly to your specific needs.

Because i’m using my localized version of FM (i’m italian) I have a job in the job: the translation of the statements; if this is not correct then pardon me and do you too a little translation effort !

Before starting the job there are few points to clarify:

Create a calculated field in which you store the first capitalized letter of your TITLE: we'll name this field as TPARTIAL.

create two global text fields (g_From and g_To) we’ll use as aliases of the starting search text and ending search text, then put these fields on your search layout (they works fine also if defined as having data-source in some value list!).

We also call your title field as “TITLE”.

design, on this layout, a “SEARCH” button that will launch the following script

FIND ALL

ENTER FIND MODE

SET FIELD (TPARTIAL,LEFT(G_FROM,1) & “…” & LEFT(G_TO,1))

PERFORM SEARCH

ENTER BROWSE MODE

SORT(by TITLE,ASCENDING)

SET ERROR ON

DISPLAY (OR VIEW) AS LIST

LOCK WINDOW

GOTO RECORD,FIRST

(WE FIND THE FIRST EXACT MATCHING REC. IN THE FOUND SET, OMITTING OTHERS)

LOOP

EXIT LOOP IF (TITLE=G_FROM)

OMIT RECORD

END LOOP

(WE FIND THE LAST EXACT MATCHING REC. IN THE FOUND SET, OMITTING OTHERS JUST TO THE END OF FILE)

GOTO RECORD,LAST

LOOP

EXIT LOOP IF (TITLE=G_TO)

GOTO RECORD(PREVIOUS,EXIT IF LAST)

END LOOP

IF (STATUS(CURRENT RECORDNUMBER) <> STATUS(CURRENT FOUNDSET)

GOTO RECORD (NEXT,EXIT IF LAST)

OMIT MULTIPLE (‘999999999’, NO DIALOG)

END IF

ENTER BROWSE MODE

EXIT RECORD

GOTO RECORD (FIRST)

SET ERROR OFF

Take note that this script MUST be launched after the the two globals (g_From and g_TO) are filled.

I have this solution running on a moderate PC (PIII 500) and it resolves searches on 120/150000 records in reasonnable time.

For any questions please post.

Have nice job.

[ July 24, 2001: Message edited by: JPaul ]