Alphabetic pick list using self join and portal

In article
, Katie
Blake wrote:

> 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.

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 )
 >> Stay informed about: Alphabetic pick list using self join and portal 

This message is not archived
 >> Stay informed about: Alphabetic pick list using self join and portal 

This message is not archived
 >> Stay informed about: Alphabetic pick list using self join and portal 

In article ,
traut.DeleteThis@gmx.de wrote:
> On Mon, 26 Sep 2011 18:33:44 +1200, Your Name wrote:
> > 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.
>
> Has there ever been any FMP version which did not permit to save and
> index its value?
>
> It's true for calculations which did include a related value, but for a
> local left(name,1) it has always been possible to save and index its
> value.

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.



> > 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.)
>
> not necessary - as long as name is empty, it won't be calculated. And
> even if it would, the result of left() on an empty field would return
> the value empty.

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




> > 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.
>
> .... or via the simpe checkbox option to recalculated the auto enter
> value on changing a field value.

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



> > 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!")
>
> Possible, but I do not see any profit from this here.

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


Helpful Harry )
 >> Stay informed about: Alphabetic pick list using self join and portal 

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.
 >> Stay informed about: Alphabetic pick list using self join and portal 

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
 >> Stay informed about: Alphabetic pick list using self join and portal