auto-create a related record?

[skrying]

Suppose I have tables apples(ID) and oranges(ID, applesID) such that apples::ID = oranges::applesID.

The main layout shows records from apples with related information from oranges.

What I need to be able to do is automatically create an oranges record whenever a new apples record is created.

Is this possible? I need to be able to do this without scripts, and as transparently as possible for the end user.

[trev hatchett]

hello skrying

is is too simple to have just one table with both apple information and orange information, and just let your users see apple information on one layout and orange information on another ??

trev

[hairy1]

I too am interested in a solution to the situation identified by the original poster...

cheers

[Ender]

Apples and Oranges is a poor example to illustrate the one-to-many relationship involved here. Since both are fruits, they would likely reside in the same table. Let's try an example that makes a little more sense: Suppose you have a Student-Enrollment system, where a Student might have many Enrollment records (in various Classes.) I can see that it might be desirable to have that first Enrollment record created automatically when a Student is added.

Now presumably, there is some method for creating a Student record. Here's how I'd deal with each possible way a Student could be created:

1. The record is created by clicking a button. In this case, have the button execute a script that creates the Student, then creates an Enrollment, setting the StudentID (the foreign key) in the new Enrollment record.

2. The record is created via a relationship (like through a portal.) See if a field from the Enrollment table can be added to the portal. With "Allow creation of records via this relationship" checked on the relationship to the Enrollment table, the Enrollment record would be created when the Enrollment's field is populated.

3. The record is created through the New Record menu command. I would disable the menus so this wouldn't be an issue (instead using scripts as in 1. above,) but I suppose this could still be handled with an event script plug-in (when exiting the Student fields, check if an Enrollment record exists, if not add one.)

Although method 2. might be attractive since it requires no script, there's no way to guarantee the user will populate that Enrollment field. In my opinion, the most straight-forward way is to use a script to create the parent and child records (Student and Enrollment) at the same time, as in 1.

[skrying]

Apples and oranges aren't the real tables. And the real tables contain many more fields than I showed.

It's more like

Contact(memberId; ... )

Roles(Id; memberId; ... ) and Contact::memberID = Roles::memberId

Whenever a Contact record is created, an associated Roles record must be created as well. I've currently worked around the problem by doing it in PHP (data is autofilled from a website), but I'd still like to know if this can be done in FileMaker in an elegant, transparent way.

[Ender]

Contact -< Roles is the same structure as Student -< Enrollment (one-to-many.) So translate your table names into my previous response.

[comment]

Whenever a Contact record is created, an associated Roles record must be created as well.

IMHO, a record must be created only when it is needed, i.e. when there is actual data that needs to be recorded.

If the relationship Contacts <-> Roles allows creation of related Roles, you can create a record in Roles just by filling in a related field - when you have something to fill in, that is.

[tysone]

I have a similar problem in need of a solution, and it seems that a better workaround must exist.

In my case, I have a table for artwork and a table for artists.

When the user enters details of a new painting, they'll have the option of selecting from previously entered artists. However, if the painting is by a new artist then I would like FileMaker to automatically create a new record in the ARTISTS table.

Since there are no related fields that need to be filled out when artwork details are entered, I'm not sure how to force the creation of a record in ARTISTS when only one field is filled out in ARTWORK.

Thoughts?

[comment]

Take a look at the attached file. You can select the artist from a list of previously entered artists - or enter the name of a new artist into the RELATED field.

Please note that this is a demo of a principle. In an actual solution, I would script the entry of a new artist, making sure (as much as possible) that people don't re-enter an already existing artist. But that is a matter of user interface, not relationships.

Art.fp7.zip

[tysone]

Thanks for the suggestion and file, very helpful and one more thing to have in my toolbox.

However, it seems like it'll be a bit clunky from a end-user perspective. Ideally, the end user shouldn't care whether the artist is already entered.

Can you think of a way to implement this without having two fields on the artwork entry screen?

I'll keep tinkering...

Thanks!

[comment]

I can think of many ways to implement this - the problem is, this needs be tailored to the desired workflow. And I am not sure I would agree that "the end user shouldn't care whether the artist is already entered". I think the user should try and pick the artist from a list. Only if the right one cannot be found, the user should create a new record - and be aware of doing that.

I suppose you're thinking of something like iTunes. That can be done too, but even though I've been quite careful, I still find an ocassional "duplicate" artist (with different spellings). That's OK for my song collection, but I wouldn't pass it for a professional database.

[tysone]

Can you allude to some of the solutions that might work?

Perhaps my mind is stuck, but I can't think of a good solution that would let the user quickly choose from a list of artists already stored in the ARTISTS table or let them quickly add a new artist to that table.

If this type of problem has been discussed elsewhere, please point me in that direction as I havne't had much luck searching. Thanks!

[comment]

I would suggest you download Business Tracker, and see how a company is selected for an invoice, for one example.