Creating related record: really?

[Philippe]

FMP 4.0v1 W98

I have 2 files, and a defined relationship between the two using one key field.

The relationship works as expected between the 2 files. Great!

The problem: I selected in the "Edit" of the "Define relationships" for my master file "Allow creation of related records".

But this does not work...

In this forum, someone posted a very similar question a while ago (02/25/01), and a bad guy answered:

"Hi, bevin

Nothing will happen in your other 4 databases until you either create a related file via a portal or script. Relationships provide a means of making data available in one file available in another, but do not by themselves create records. Rather a relationship offers you the potential, but you as the developer have to then use scripting, portals, calculations, and other means at your disposal to effect the record creation. You probably need to read the FM manual and/or help files a little more throroughly to see how it is done.

Best of luck.

--------------------

Moon Mullins

Full Moon Consultants "

Well, I am not gonna consult Full Moon, for sure: in the FMP manual, they say:

"Creating related records

You can add records to the related file as you enter data in a record in

the master file.

To add related records from the master file, the Allow creation of related

records option must be selected for the relationship. (See "Defining

relationships for a relational database" on page 10-21.) When this option

is selected, typing in a related field that has no corresponding related

record creates a related record based on the match field in the master file."

This is, as far as I can tell, what I am doing.

So what do I do wrong?

PS: Full Moon, don't feel obliged to answer!

[kennedy]

So, for example, create a portal in File1 that shows the related records in File2. Put a couple layout fields in the portal that display fields of File2. Go into browse mode. At the bottom of the portal will be an empty row. Type a value into one of the fields in that bottom row. FMP will automatically create a new related record in File2.

Is that what you're looking for?

[Philippe]

If I understand your suggestion, this is not what I am looking for.

Let me try to clarify: in File 1, I have records, each representing an item. In each record, the name of the corresponding customer is indicated.

In file 2, I have the list of these customers (1 customer/record).

The reltionship between File 1 and 2 is based on the name of the customer.

I want that when a new item is sold to a new customer (whose name is not yet present in the records of File 2), a record for that customer is automatically created in file 2 as soon as this new customer name is enter in the File 1 record. Of course, if the customer is already in File 2, no record should be created in File 2.

Thus the "simple" related field approach, that I cannot get to work...

[kennedy]

[color:"red"](RED FLAGS GOING UP)

So, if while entering the new item, the data entry person mistypes the customer name a couple of times, are you okay with having three new customers created? (The real one and a couple of bogus ones.)

Or when entering an additional item for a known customer, if the data entry person spells the customer name a little differently than it was originally put in, are you okay with a new bogus customer getting created rather than attaching the item to the existing customer?

If there are two common misspellings for a customer name, are you okay if the dozen items for that customer end up getting split something like 5 on the proper spelling, 4 on the bogus spelling #1, and 3 on the bogus spelling #2?

Generally, for robust relationships, you want to use IDs in such situations. And generally, for avoiding bogus records, you want to verify that the customer really *intends* to be adding a new customer rather than finding an old customer... making things too automatic is usually very bad in the building of a good database (or a database with good data).

[Ugo DI LUCA]

[color:"orange"] Orange Flags going up

The storm just passed through....

Hopefully, there's no way to have this new entry automatically created unless you use a script.

300% agreed with Brian that you should forget about names.

Having an ID would be helpful, having the ID in a value list with the Name as a second would be better.

But...

Besides mispellings, I personnally have 26 "Rodrigues" in my customer file, and 17 "Rodriguez". Which one do I choose. 7 of them have also "Jose" as their firstname

Using portals rather than Value List would save a lot of errors, rather than a single Value list. You coud even use the Clairvoyance tool. Check the Forum Sample for these kinds of Type-Ahead portals.

If not, I went with a test for a CheckSpeller available here

[Philippe]

Good point.

The problem is: make it easy for the user, with potential risks for the data, or limit the risks, and make it less user friendly.

PS: concerning my original post, I just deleted all fields and relationships involved, made new ones (without any difference as far as I can tell...) and... everything works as expected.

Hm...

[McCormick]

In the trade-off between those two conflicting goals, often you can achieve a good compromise by making the sacrifice elsewhere: more work for you and bigger files. If you're willing to add scripts and fields you can often put firm but friendly verification pauses in their way. Perhaps they can type a name into a temporary global field (although they won't know this), and you can rig up a complicated script to search for similar names to offer them. Perhaps just add an "Are you sure?" custom dialogue every time and give them the chance to edit their typos. Perhaps a script that searches for similar names to flags them for later review. If you did take this route, you could even add a repeating field to store common mispellings that would fill up gradually and catch future mistakes (eg, searching in both Name and WrongNames). Of course, you should use unique IDs for each customer, but the issue is about the interface between those and what the user will see/type. As I said, all of these ideas are more work for you, and if you do them well, your users will barely notice.