Foreign key gets strange value

[mispel3991]

Hi There,

I am a novice to filemaker and I am training myself by converting my CD-adminstration from an Access database to FM9. I encounter the following problem.

I have a cd table consisting of

Album_id, numeric (primary key)

Artist_id, numeric and (foreign key)

Album title

Other fields

There is also the Artist table with the fields

Artist_id, numeric (primary key)

Artist_name, alpha numeric

Artist - Album is a 1 n relationship.

The albumview shows all fields of Album table plus the Artist_name extracted from the Artist table.

Trouble begins when I want to add a new album. Starting in the Album view I use a script which opens the Artist table and where I can mark the artist involved. When return to the Album view there is a new record, but in the Artist_id field a question mark is shown. When I click on it I see the value of Artist_name in it.

While tracing the script I can see that the values are correct until the Album screen is reloaded. Then the Artis_name is shown in Artist::Artist_id. I tried to analyze the examples include, but somehow I miss the (simple?) solution.

Can someone please explain what I am doing wrong?

Kind regards,

Mispel

[aldipalo]

Just 2 quick thoughts, check for these possibilities.

You may be pointing the Album::artistid to artist::artistname OR if you are using a valuelist you've set it up so that it's set to show the artist name rather than the artistID.

[mispel3991]

Thanks for your reaction.

I've checked these two possibilities several times, and unfortunately it isn't that easy. While tracing I see the following just before I reopen the Album screen

Artist::Artist_name = Joan Armatrading

Artist::Artist_id = 5

Album::Album_id = 5

$$Artist_name = Joan Armatrading

$$Artist::Artist_id = 5

After the album screen is reopened the fields and variables are filled like this:

Artist::Artist_name = space

Artist::Artist_id = space

Album::Album_id = 5

$$Artist_name = Joan Armatrading

$$Artist::Artist_id = 5

Then when I click on Artist_id in the Albumscreen Joan's name appears. Somewhere along the road I lose the information, but why?

Kind regards,

Mispel

Edited June 18, 2008 by Guest

[mr_vodka]

It would help if you posted a copy of your script. You can attach it as a gif, pdf, etc.

[mispel3991]

Actually it are 3 scripts, they are Dutch and in the included Zip-file. I gave an indication of the, I hope, correct English terms where needfull. I hope it's clear enough.

The db-structure is in the PDF.

Table_relation.pdf

FM_Scripts.zip