Extracting 'The', 'And', etc.from lists 
Author Message
 Extracting 'The', 'And', etc.from lists
What script will allow me to list a very simple database of books,
alphabetized ignoring the words 'The', 'A' and 'An' in the title field?

I'm using FileMaker Pro 4 for the Mac.

I'd really appreciate any help, it's been baffling me for some time.

Many thanks in advance if you can help.




Wed, 17 Jan 2001 03:00:00 GMT
 Extracting 'The', 'And', etc.from lists

Quote:
>>What script will allow me to list a very simple database of books,

alphabetized ignoring the words 'The', 'A' and 'An' in the title field?<<

You don't need a script at all. Define a calculated field (set to text) with
the following expression:

Case(
LeftWords(Title, 1) = "A" or LeftWords(Title, 1) = "An" or LeftWords(Title, 1)
= "The",
Right(Title, Length(Title) - Length(LeftWords(Title, 1)) - 1) & ", " &
LeftWords(Title, 1),
Title
)

The above will append the leading "article" preceded by a comma at the end of
the title. E.g.,

    Magnificent Ambersons, The

If you wish to omit the leading article entirely, use the following expression:

Case(
LeftWords(Title, 1) = "A" or LeftWords(Title, 1) = "An" or LeftWords(Title, 1)
= "The",
Right(Title, Length(Title) - Length(LeftWords(Title, 1)) - 1),
Title
)

I hope this helps.

Regards,

Stu Bulman
Computer Services



Wed, 17 Jan 2001 03:00:00 GMT
 Extracting 'The', 'And', etc.from lists

Quote:
}What script will allow me to list a very simple database of books,
}alphabetized ignoring the words 'The', 'A' and 'An' in the title field?
}
}I'm using FileMaker Pro 4 for the Mac.
}
}I'd really appreciate any help, it's been baffling me for some time.
}

probably be easier to make a calculation field rather than a script..
then you could have an ascending "sort" done on the calculation. the
calculation field does not "need" to actually show up (be visible) on
the page/layout. you can just use it for the sort..

i guess you already have a field called "title" so you can make another
field that you will do the sort on.. call it "field to sort title on",
or whatever will make sense to you when you are lookin at it again in
the distant future..

the calculation could look something like this.. with as many cases as
you may have words.. ie 5 6 7.. whatever you will limit longest title
to..

Case(
MiddleWords(title, 1,1)  = "the", "",
MiddleWords(title, 1,1)  = "a", "",          one case per word
MiddleWords(title, 1,1)  = "an", "",
MiddleWords(title, 1,1) & " ") &  
Case(
MiddleWords(title, 2,1)  = "the", "",
MiddleWords(title, 2,1)  = "a", "",        
MiddleWords(title, 2,1)  = "an", "",
MiddleWords(title, 2,1) & " ") &
Case(
MiddleWords(title, 3,1)  = "the", "",
MiddleWords(title, 3,1)  = "a", "",
MiddleWords(title, 3,1)  = "an", "",
MiddleWords(title, 3,1) & " ")  &
                                         |< same as last case    
MiddleWords(title, 4, WordCount(title) - 3)  <<in case more words
                   ^ one higher than last case      

you may/maynot want this for other than the first word.. and if you
do, then it has the fault of only working for as many words as you
setup cases for.. so in the above example

"the long a train" title would get chopped to "long train", while
"the long a train an exposition" gets chopped to "long train an
exposition"..

if you only want the first occurrence of (the, a, an whatever) stripped, then
you could do something like:

Case(
MiddleWords(title, 1,1)  = "the", "",
MiddleWords(title, 1,1)  = "a", "",
MiddleWords(title, 1,1)  = "an", "",
MiddleWords(title, 1,1)  = "whatever", "",
MiddleWords(title, 1,1) & " ") &
MiddleWords(title, 2, WordCount(title) - 1)

these are a bit bulky, but seems to work. you can not self-reference
a field in a calc, nor does there seem to be anything setup for
loops or variables, but the nice thing about setting up the field as
a calc, is that you do not have to remember to "run the script" if
you change the record, or make a new record.. a calc just happens..

if you wanted to do the same in a script, you can self reference the
field, and setup a loop that would "walk thru" it one word at a time
and then either
set title to title & thisword IE title = (title & thisword) OR
do nothing, and leave the title as it is, for the next loop..

hope this helps..


Sur-Tech / Ami-Tech    (408) 496-6348 Xt 247     Santa Clara CA 95050
                          www.Sur-Tech.com



Wed, 17 Jan 2001 03:00:00 GMT
 Extracting 'The', 'And', etc.from lists
Hi boink,

Vince and Patrik gave you perfectly good solutions, but here is an
alternative that may save you some typing.  As I read it, you are not
interested in changing the titles of the books, so all you need to do is
figure out what word to sort by.  A big compound Boolean using "OR" is fine,
but how about just this?:

sort_Title (text, calc, indexed) =

If( PatternCount("a an the", Left(Title, 1)),
    MiddleWords(Title, 2, 1),
    LeftWords(Title, 1)
   )

The PatternCount() function will thus return a positive integer (=true) when
the first word of Title is "a", "an", or "the".  Then the sort_Title field
will result to the second word of Title.  Else, sort_Title will be the first
word of Title.  Sort on the new field, sort_Title.

This technique is also useful when trying to filter prefixes from Name
fields.  In that case, you might use something like:

PatternCount("mrsmistermsdrdoctormissprofessor", Left(Name,1))

Of course, you can add many prefixes to the test string.  Just be careful
not to accidentally include real names in the text string - in my string
here, "doc" would be seen as a prefix, when it might also be a nick name.

Of course, you might want to extend the concept to examine the second word
of the title as well.  Patrik's solution, I believe did this.

Good luck,

Andrew LeCates

-----------


Quote:

>What script will allow me to list a very simple database of books,
>alphabetized ignoring the words 'The', 'A' and 'An' in the title field?

>I'm using FileMaker Pro 4 for the Mac.

>I'd really appreciate any help, it's been baffling me for some time.

>Many thanks in advance if you can help.





Thu, 18 Jan 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. **************!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Help me !!!!!!!!!!!!!!!!!!!!!!!!'''''''''''''''''''''''*************

2. Separate 'StringName' into 'Last', 'First', 'Middle'

3. How set parameters for 'Select * from Suppliers where SuppID in ('111', '222', '333')'

4. xp_grantlogin 'Administrators','admin'

5. type field ''money''

6. IndexDefs.Add('Help','Please',[ixD2WorksGreat, ixD3Errors])

7. ''''

8. dbms_utility.analyze_schema('SYS', 'COMPUTE')

9. Base vidé'o'tek et disk'o'tek

10. Error that stored procedures aren't in collection, but they're listed in EM

11. DataList Control won't match user's keystrokes when list too long

12. 4GL-Module/Modify doesn't list *.4gl's


 
Powered by phpBB® Forum Software