Value List: Auto-complete with calculation field?

[Steve E.]

I need to aggregate the information in several fields and use it in a value list. But if the field with the value list is a calculation field, it is not possible to set it to "auto-complete." For example:

3 fields: last_name, first_name, complete_name

If the complete_name field is a calculation field (calculation = first_name & last name)

then a value list of complete_name cannot be set to auto-complete.

Is this something I'm doing or is this generally true with value lists, that they cannot be set to auto-complete if they are a calculation field?

If the value list field is not a calculation field and is populated with data by a script performing the exact same calculation, this problem goes away and the value list can be set to auto-complete. Obviously, I can have a script perform the calculation unobtrusively instead of it being performed by a calculation field, but it is another step and not as elegant.

Comments?

[Steve E.]

Well, if instead of a calculation field, a text field is used with the calculation auto entered, it is possible to set the value list to auto-complete. This seems to work fine for performing finds. See the attached file.

Can anyone think of a good reason why a text field with an auto-entered calculated value that is used to produce a value list can be set to auto-complete the value list, but a calculation field cannot? Is this just a feature that was missed in the Filemaker programming or is there a good logical reason why this wasn't done?

Value_List_Auto-Complete.fp7.zip

[Steve E.]

Aha. I was wrong. A Value List created by a calculation field can be set to auto-complete. My mistake was to make it a global field. The disadvantage of using a text field with an auto-calculation is that if there are any alterations to that field, the data in the field will not recalculate automatically. A calculation field will always simply recalculate based on any changes in the fields that the calculation is based on.

The example file shows a system for performing finds based on a value list created in a calculation field and displayed un the same field set to auto-complete. The field for determining the find value is within a portal with conditional visibility. Two script triggers are used. The portal is outlined (bordered) so people can see that a portal is present.

Find_VL_Aut-Comp_Calc_ngField_Portal_Visible.fp7.zip

[comment]

Couldn't this be simpler?

The disadvantage of using a text field with an auto-calculation is that if there are any alterations to that field, the data in the field will not recalculate automatically.

Not exactly. In this example, an auto-entered calculation replacing existing value WILL re-evaluate when one of the referenced fields is modified - since the referenced fields are in the same table.

FindVL.zip

[Steve E.]

Thanks. Much simpler. I'm particularly impressed by the name calculation field working even though it doesn't "exist" on a layout anywhere.

Here's this method set up with a portal for conditional visibility (which I need in the "real" database I'm developing). I had to add a script, mainly for navigational purposes from the portal condition field. Otherwise it seemed to get "stuck" in the portal condition field.

FindVL-2_in_Portal.fp7.zip

[comment]

Calculation fields work on data level, not on presentation (layout) level.

I am still not sure what is the purpose of the on/off portal here.

[Steve E.]

This is for a membership & fundraising database for a (small) nonprofit environmental org I'm on the Board of. Basically, navigation and common operations will be performed mostly through three fields with conditional value lists. For example:

*People - Enter - NewMember will create a new record for the initial member entry.

*Finance - View - Payments goes to a layout showing all donations for the members on the active record.

*People - Show - Issues & Contacts will show the issues of concern to this member and our interaction with them about those issues (i.e. meetings they've attended, response to action alerts, etc.).

There are about 15 of these combinations. Some are simple navigations or operations (i.e. print labels), and some are searches,including member and duplicate search. Many memberships are households and have two names in the membership record.

So People - Find - FirstPerson makes a field (in a portal) visible that has a value list comprised of both names for each record. Selecting one of these name combinations then finds that record. This allows the person using the database to find and search for members or determine if someone is a member. By using a value list in this way it avoids performing a search that finds 4 "John Smiths" and then having to browse or search again to find the "John Smith" who lives with "Sarah Schmidt." Instead, the person using the database can select "Smith John - Schmidt Sarah" for the initial search. Doing it this way also means that only one window and layout is necessary.

I'm sure there are slicker ways of doing this, but its what I've come up with.