Basic Rule

[ruthcarlton]

I have a system where records are created for a Hotel.fp5 from within Events.fp5.

I use the relationship hotel = ::hotel

The fields in Events.fp5 are:

Hotel, ::HotelAddress , ::HotelCity, etc.

This works great except when you want to edit the hotel name after a record has been created. When you change the hotel name in Events.fp5 it creates a new record in Hotel.fp5

I know there is a basic rule here that I am missing to keep the system easy-to-use, but more flexible.

[Geeksharka]

The basic rule is to not use names to establish relationships. Use unique ID's that will not change.

You should probably use a different key to attach the two files. Usually each Hotel record would have a serial number, or ID #. Make sure it's unique.

When you create a new Event, make sure that Hotel's ID is entered into the proper field to establish the relationship. There are many ways to do this.

If the relationship between Events and Hotels is based on ID's, then when you change a Hotel's name, its Events will still be linked.

HTH

[jasonwood]

Sharka's point is very good, you should use serial numbers for relationships, but you should also turn off "Allow creation of related records" (in the relationship definition) since you are not creating new hotels from the Events database.

[ruthcarlton]

Thanks for the response. The hotels do have unique IDs, but what is the relationship between the Hotel ID and the Event? Where do I store the Hotel ID in Events.fp5?

I want the user the be able to enter the hotel name and, if it is already in Hotel.fp5, all the relevent info (address, city, etc) to automatically fill in.

Also, I am creating new Hotel records from within Events.fp5

[jasonwood]

what is the relationship between the Hotel ID and the Event?

Using ID's is simply better because there is no risk of duplication, misspelling, etc.

If you really want them to be able to enter the Hotel name in the events database, you might considering using two relationships, so that the Hotel Number is a lookup based on the Hotel name relationship, and the rest of the fields are lookups based on the hotel number relationship.

If there aren't a lot of hotels, you could do the above with a value list to make it even easier.

Alternatively you could have users find the hotel first (in the hotel database) and then click a button to create a new event with that hotel.

Also, I am creating new Hotel records from within Events.fp5

This is the cause of the problem you mentioned above:

When you change the hotel name in Events.fp5 it creates a new record in Hotel.fp5

You can't expect FileMaker to "know" when you want a new record, and when you don't...

Alternatively, you could put a button on your events layout that creates a new hotel in the hotels database, where you enter the information, and then return to events. This process could be scripted so that the user may not even notice the transition (using a special layout in hotels, and then returning them to events with the new hotel entered in the record).