Problem with updating fields

[DanBrill]

Hello,

I've taken the plunge and started learning FileMaker last Friday. It was a bit weird at first compared to Access, and there are a few things that it doesn't do that I wish it would (like conditional formatting and event triggers), but I'm getting the hang of it. But I've also found there are some things it does much, much better than Access. Like most things in life, there are tradeoffs.

But one thing is vexing me, and I'm hoping someone can help me out. I'm having trouble getting values to update in a field. Here's what I've got.

I'm keeping track of people and their family relationships. I have a field for the first name called FIRST and one for the last name called LAST. I then have a calculation field called NAME that concatenates first and last:

NAME = LAST & ", " & FIRST

So for record number 1, Joe Smith would be

LAST = Smith

FIRST = Joe

NAME = Smith, Joe

And for record number 2, Mary Smith would be

LAST = Smith

FIRST = Mary

NAME = Smith, Mary

So far, so good.

I also have a field called SPOUSE. This field should be populated with the results of NAME so that users can select the individual's spouse, who is simply another record in the database. I created a value list called PEOPLE which is created from all the values in NAME. SPOUSE is set to be a pop-up list that is based on the value list PEOPLE. Now users can pull down a list and select a NAME to indicate the individual's husband or wife in the SPOUSE field.

Again, so far so good. For Joe Smith I can select "Smith, Mary" in the SPOUSE field, and vice-versa.

But here's the problem. Suppose there was an error in data entry and Mary Smith should actually be Marie Smith. I go to record 2 and change Mary to Marie. NAME recalculates so that she is now "Smith, Marie". However, the SPOUSE field doesn't update. On Joe Smith's record his SPOUSE is still listed as Smith, Mary. If I go to the drop down list I will see that the list itself has been updated to Smith, Marie. But the field isn't updating automatically.

What am I doing wrong, or is this just the nature of the beast? Is it because NAME is a calculated field? Is there a different approach I should be using?

Thanks for any insight,

Dan

[Vaughan]

The pop-up list just makes data entry easier: the field itself contains "Mary Smith" just as if you typed it by hand. The only way the field value will ever change is when you re-enter it yourself. This is normal. And good.

However, having said that, I know exactly what you want to do.

To do it, you need serial numbers on every record. Instead of entering the spouse's name, you enter their ID number. A self-join relationship links the record ID with the spouse ID. FMP can use the relationship to display the spouse's name, and this makes it self-updating.

I've just started learning Access, and on first blush (to the uninitiated) it would look far more powerful. Until you realise that it just has more things "hard-wired" in (with fancy names like "delete query" and "update query") which don't have comparable steps in FMP but are easily achievable anyway.

For instance I was initially impressed by the make table query until I realised I have already done something similar in FMP with just one single relational file and the "Go to Related Records" script step.

[DanBrill]

Thanks, Vaughn

And right you are. This was a pretty easy fix since I have the absolute rule that every record of every database I ever create will have an auto-entry id number attached to it. I can't count the number of times I'm glad I have this rule.

I've set it up so that users tab to the SPOUSE drop-down list and select the id number of the record for the person in question. (I've set it to also display the name next to the record number, so it is very easy for the user to know who that record number is related to.)

Then I create a relationship as you indicate and have a field that just displays the name alone. I set this field so that it cannot be selected by the user. I set up the tab order for the layout so that the field that displays the record number has a number in the tab order, but the field that displays the name is omitted from the tab order. Finally, I placed the field that contains only the name on top of the field that contains the record number. So, when the user tabs to this spot on the layout, the focus is on the obscured field, and they can select the record number. When they tab again and the field looses the focus, all they see is the field that displays the NAME alone.

To put this in Access-speak, it is equivalent to setting the bound column. The field is linked to the record number but is bound to (i.e., displays) the name information. In terms of setting up the process, Acess wins hands-down on this one. But FM can do the job, and I'm continuing to find things that FM does much more easily than Access does. Trade-offs again.

Thanks for your help

Dan