What on earth am I doing wrong? Thanks to various posts here over the
years, I have been experimenting with using a self-join to populate a
portal with ONLY contact names starting with a specific letter. This
is what I have done:

Designed a new database with only one table, and three fields - Name,
Calc, and Filter.

Name is a text field with surnames
Calc is a global field which calculates the first letter of each name
using Left (Name ; 1) This works perfectly
Filter is a text field which uses a value list of each letter of the
alphabet.

There is a self-join between Filter and Calc, with Filter = Calc (so
Filter is on the left side, and Calc is on the right looking at the
screen defining the relationship. The two tables are TestSelfJoin,
and TestSelfJoin 2.

I have then created a layout with three elements:

a) A portal from the self-join relationship (TestSelfJoin 2) In that
portal are two fields from TestSelfJoin 2) - name, and Calc.

b) A field for Filter from TestSelfJoin (the original table)
formatted as a series of radio buttons down the screen using the
alphabet value list, so you can see a long list of single letters.

c) Fields from TestSelf Join for single records.

What I had hoped would happen is that as I clicked a letter of the
alphabet in the Filter (TestSelfJoin) field at a), the portal from
TestSelfJoin 2 at b) would populate with Names beginning ONLY with
that letter of the alphabet. I could then do a script which would Go
to Related Record and that would display in c)

It doesn't. The portal continues to display the complete list of
names in TestSelfJoin. How can I get it to display only names
beginning with the (correct) filter character?

Any help gratefully received, as I struggle to understand how self-
joins work.

Katie.

In article
<26011292-cb44-4d61-a551-ac311e2a5103 (AT) m5g2000vbe (DOT) googlegroups.com>, Katie
Blake <kathlynblake (AT) gmail (DOT) com> wrote:

Hopefully that's a typo. If Calc is actually a Global field, then it will
only ever calculate as the first character of the first record.

Calc would usually be a normal Calculation field, not a Global field (but
see below) and Filter should be the Global field.

The reason is so that the user can pick a letter from the Filter value
list, which is then matched to the appropriate records with that same data
in their Calc field.
i.e.
rel_ChosenLetter
Match records in TableA with records in TableA
when Filter = TableA::Calc

The on the Layout you can have the Filter field set to display the Value
List (normall as a pop-up menu or drop-down list so it uses up less screen
space).

Also make sure the Portal contains fields via the same Relationship.



BUT,
unfortunately there's a hiccup here - you can't have a Calculation field
on the right hand side of a Relationship since it can't be indexed (this
may depend on what version of FileMaker you're using). That means the Calc
field must instead be a normal Text field which uses an auto-enter
calculation to obtain the result, but only once data has been entered into
the Name field - the options for an auto-enter calculation are meant to do
this, but are a bit flakey, so it's usually best to do it yourself.
e.g.
Calc Text
auto-enter by calculation:
If(IsEmpty(Name); ""; Left(Name;1))

This way Calc remains empty while Name is empty, and FileMaker will
perform the auto-enter calculation once Name has data entered into it.
(Normally FileMaker will perform auto-enter options when the record is
created.)


This then causes a second problem - because Calc is a normal Text field,
this also means that when the data is changed in the Name field, the Calc
data will need to be changed either manually or via a script.

What I normally do in similar situations is create another field called
OldName which is a normal Text field with an auto-enter calculation to
copy the data from Name (again, only after it actually has some data).
e.g.
OldName Text
auto-enter by calculation:
If(IsEmpty(Name); ""; Name)

Then you can have a Calculation field that checks to see if both Name and
OldName contain the same data ... if not the field can display a warning
(either tect of graphically).
e.g.
ChangedName Calculation, Text Result, Unstored
= If(Name = OldName; ""; "Name has been changed!")

Put that field on the Layout next to Name, and it can be set as a button
to run a Script that updates the neccessary fields (OldName is given a new
copy of the Name data, and Calc is given the new first letter).
e.g.

Set Field [OldName; Name]
Set Field [Calc; Left(Name; 1)]

The warning will reset itself once the Name and OldName have the same data.


Helpful Harry )

On Sep 26, 4:51*pm, Martin Ôrautmann <t-use... (AT) gmx (DOT) net> wrote:
Many thanks to you both. For the record I am using Version 11.

Yes, it was a typo - the Filter field is global, and the Calc field is
a text field.

Thank you Martin for your example, which looks exactly like the
example I did! I still don't know why yours works and mine doesn't
but will look long and close. A long shot - perhaps because you
named the second occurrence of the table (Name, and Index)???

However, I noted what Harry had said:

rel_ChosenLetter
Match records in TableA with records in TableA
when Filter = TableA::Calc

and I investigated the Portal Setup options to Filter records. I
specified that I wanted to filter portal records testSelfJoin 2::Calc
= testSelfJoin::Filter, and that has worked perfectly. Martin's
example doesn't appear to have any filters, it just works.


It probably doesn't matter which type of calc I use to get the first
letter of the name field - that always worked fine in my small test
files, but I will fiddle around with them and see what is safest.

Thank you both again very much indeed. I appreciate the help and
continue my learning curve.

Katie.

BTW - my work was inspired by the post from Bridget Eley in 2002
(http://groups.google.com/group/comp....browse_thread/
thread/9fec20c29f7923af/cb333055186b03bb?hl=en&lnk=gst&q=bridget
+alphabetic#cb333055186b03bb)

In article <slrnj807og.4lt.t-usenet (AT) ID-685 (DOT) user.individual.de>,
traut (AT) gmx (DOT) de wrote:
You cannot have a Calculation field on the right-hand side of a
Relationship (at least in some versions of FileMaker, I'm not sure about
the newest). The reason (semi)officially given was because calculation
fields cannot be indexed ... I don't know whether that's actually true or
not.



True, except it doesn't always seem to work, so it's usually better to
force a real empty result.




That's may be possible, but it depends on the version of FileMaker being used.



It's neccessary in older version of FileMaker where auto-enter
calculations cannot re-calculate themselves.


Helpful Harry )

Thanks again for the generous information. I learned a whole heap of
new stuff, including the cmd-i option to view an index. (I am using
Windows, so it was a ctrl i)

The left letter calculation worked every time, there was no problem
with T or F.

On 26/09/11 10:13 AM, Katie Blake wrote:


now that you appear to have it working; another option is to do away
with the aec, and use a global + filtered rel, so you can morph it into
a clairvoyant search


have the same ABC buttons script the letter into a global g_search

create a calc c_search = g_search + "zzz"

redefine the self rel to be multi-parameter:

g_search ≤ name
c_search ≥ name

the portal result will initially be the same, but now you can refine the
result by typing in the global

With some more advanced scripting and script triggers, the result can be
made to refresh with each keystroke in the global