With correct design, simly editing an existing record should not result in creating a new record.
How is your layout set up? Are you using a portal? If yes, how many rows of related records do you need to be able to display in the portal?
It's not a portal, it's just a field belonging to another table. The tables are related via two fields and every time data is entered into the field a new record is created in the related table. It's a very complicated setup but I guess it'd best be explained:
Amounts::IngredientID (child) <---------> (parent) Mixtures::IngredientID <---------> Ingredients::IngredientID
Amounts::MixtureID (child) <---------> (parent) Mixtures::MixtureID
So the mixtures layout has five fields for the ingredients, each linked to a whole bunch of TOs on the relationship graph in order to pull down related information like expiry dates. Every time a number is added to the amounts field a new record of that amount with associated IngredientID and MixtureID is created in the amounts table. Every Ingredient record in the Ingredients table has a portal of these amounts which it deducts from its total, showing how much is left.
If I add an amount in the Mixtures layout, click out of it, delete the number I just put in and then write another there'll be two entries in the amounts database, which will in turn yield misleading results in the associated Ingredients record.
That's definitely odd, and I think I'd have to see your layout and examine the design of your database to see why that's happening. If you place a field from a related table on a layout, the "allow creation of records via this relationship option" will make it possible to create a related record when you enter data into this field for the first time, but editing the field--even clearing the field completely and then returning to insert new data into the field at a later time shouldn't create a new additional record--it should just modify the record originally created when you first entered data into the empty field.
You got something else going on here, but I can't see what that is from here. Do you have any script triggers set to perform a script when the field is modified or exited? When the layout record is committed? If there's any chance of that and you have filemaker advanced, you might want to start the script debugger and then edit the field as you describe to see if the debugger captures any such scripts when they trigger.
Well now I feel like an idiot. Yes there is a lot going on but I just tried it again and, bingo, it worked like you said it should. I think I was moving between layouts a lot during the testing process and that may have confused it. That and I've made a bunch of changes since I last witnessed the problem so maybe I inadvertently fixed it. In any case it appears to work now. Thanks for your help!