Auto enter based on surname problem

[macman75]

I've inherited a project created in FM 5.5. The main database has an established relationship with another file that contains a list of surnames, given names, middle initials and addresses.

In the main database the user enters the surname and the remainder of the information auto-enters. The problem I've found is in instances where there are persons with the same surname, e.g.Smith, Adam and Smith, Zach. A user wants the information on Zach Smith but upon entering Smith the information auto-entered from the relationship is that of Adam.

It's been about 6 years since I last worked with FM. I'm pretty sure that I've encountered this problem before but I can't remember the solution.

Any assistance would be greatly appreciated.

[Mike Williamson]

FileMaker always brings in the first related value. Your relationship is currently using only the last name. You'll want to change it to use both last and first name.

[comment]

One possible way to handle this is to have users enter the surname into a global field, and show them possible matches in a portal. Users select the correct contact by clicking in the portal.

[macman75]

Thanks to both of you for the suggestions.

I my first post I failed to state that the surname, given name and initial each has their own field. If I recall, this makes your suggestions a bit more difficult to work out.

I'm going to hit the books later tonight and try to implement your ideas.

[comment]

surname, given name and initial each has their own field. If I recall, this makes your suggestions a bit more difficult to work out.

I don't see why that should pose any difficulty regarding the portal. User enters "Smith" into a global field. The portal shows 'Smith, Adam' and 'Smith, Zach'. User selects 'Smith, Zach'.

[macman75]

I've tried both suggestions and I've settled on the Global-Portal alternative.

Working with a clone I have defined a new field in the main file as GlobalLastName.

I have created a new relationship, GlobalNameLookup, using GlobalLastName field and the field from the file it references, LastName. GlobalLastName::LastName.

The portal I created uses the data from the GlobalNameLookup relationship. I've placed the fields LastName, FirstName and MiddleInit inside the portal.

In the main file I've defined the fields for the First Name and Middle initial as auto enter using the data from the GlobalNameLookup relationship. First Name::FirstName and Middle::MiddleInit.

The portal works as expected, when I enter "Smith" into the global field the portal displays all of the Smiths. However as soon as I tab out of the surname field it populates with the information on the first Smith in the list.

I suspect that (at the very minimum) I've messed up in defining the auto enter fields. I'm also stumped on how to set up the portal to allow the user to pick a selection from its list.

I'm getting closer to solving this and your assistance is truly appreciated.

[comment]

You don't need any auto-enters or lookups. Place a button in the portal that sets the ContactID field in the main table to GlobalRelationship::ContactID.

[macman75]

OK. I've put the button inside the portal. The control script for the button is as follows:

Set Field ["Last Name = GlobalNameLookup::LastName"]

When I click the button I receive an error message that says the script cannot be found or has been deleted.

[comment]

You need to use unique IDs for your core relationships instead of names. Otherwise you are back to the same problem: you set the field LastName with Zach Smith's last name. That's "Smith" - and the relationship based on this field will fetch data from the first related record, which is Adam Smith. Your "real" relationship (i.e. NOT the one using the global) should be:

ContactID = Contacts::ContactID

and your script:

Set Field [ContactID , GlobalNameLookup::ContactID ]

Note that Set Field[] has two separate parts: which field to set, and what to set it to.

I receive an error message that says the script cannot be found or has been deleted.

I'm afraid I can't help you with that. Do some investigation, or just try it again.

Edited August 26, 2008 by Guest

[Raybaudi]

No need for a script, but only the single step:

Set Field [ MainTable::ContactID ; GlobalNameLookup::ContactID ]

( and what I wrote is what you have to see near the top of the "Button Setup" window )

Edit: Oh, comment is here.

Edited August 26, 2008 by Guest

[macman75]

Unfortunately, I'm using an old version of FM (5.5). There is not a button setup menu that I can find.

Your English is fine!

[Raybaudi]

I've inherited a project created in FM 5.5

...

It's been about 6 years since I last worked with FM.

Apologise. I missed the first part !

( maybe I was reading ONLY the second one and thinking in a new release )

[macman75]

I've revived this thread as I've managed to upgrade to FM 8.5. I'm still having trouble getting the button inside of the portal to select the correct name.

Could someone please look at the attached example and tell me where I've gone astray?

Case_Management.fp7.zip

[comment]

Try it this way?

Case_Management.fp7.zip

[macman75]

Yes, that's what I needed. I was going in the wrong direction with my TOs.

The button behavior was also confusing me. I thought the Specify target field / Calculated result choices were an either or proposition. So, when I specified the target field I got only the first side of the result. After I realized that I could also select the ID from the second TO things got better.

Thank you for your assistance without it I would still be stumbling around in the dark!

[macman75]

This project is starting to become a career. I'm back with a vexing (to me at least) problem. My surnames show up in the portal, the select button works but the fields will not populate until one exits the record then returns to it.

A shove in the correct direction will be appreciated.

CM.zip

[comment]

Why two files?

You are on a layout of Cases, but you're setting an AttyID field in Petitioners. I think you want to set the AttyID field in Cases (there probably shouldn't even be an AttyID field in Petitioners).

[macman75]

The Atty file is provided by another entity. It's updated periodically and sent as a tab separated file. I clone the FM file then do an import. I know, that's ugly but it works.

Each case may have multiple parties each of whom has an attorney. Your first suggestion worked however as I added additional attorneys to the mix I found that doing so resulted in changing the information entered earlier for the other party's attorney--the attorney I picked for record 2 also became the attorney in record 1.

I've got it working with multiple Atty TOs but I can't get the values to show up until I exit the record. My hope is to get this solution up and running temporarily then hit the books to build a better one.

[comment]

Each case may have multiple parties each of whom has an attorney.

OK, but then you should place your portal on a layout of Petitioners. And there should be a CaseID in Petitioners, not a PetID in Cases.