Create record by relationship and auto-enter

[FossilFM]

I'm wondering if it is possible to create a related record just by a relationship and an auto-enter field?

What I would like is where I make a new record in the parent table and set the appropriate field values in parent table and once I commit the record a new record is created in the related table. This is without using a set field of the related keys in the related table. I know related records can be created using a set field through the relationship but is it possible to create it without the set field and just by auto-enter fields that are bsed on the parent table?

Thanks.

[Jacob22x]

There isn't a way to create a record without doing something to create that record - you need to have a script that creates it, or type something into a portal that creates it, or do it some other way so that the system knows you want to create a record. At least, as far as I'm aware.

I had an issue similar to what you might have - I had a table called issue that was parent to a table called discipline, but when I created a new record in issue I wanted a new record in discipline to automatically be created that was then related to that issue. I was able to do this eventually, but with a script and a plug-in. I defined a field on the issue table as an auto-enter calculation that then ran a script, and the script created the child record in discipline.

I was able to run a script from an auto-enter calculation because of a plug-in I got that ships with FileMaker Developer 7 - I think it's called MacExample or something like that. I found it on the internet, anyway.

[bruceR]

Note that one of the fun things you can do in 7 is create a relationship based on multiple fields. You can set this up to allow creation of related records. You can use a mix of globals and standard fields on the parent side of the relation. Then you enter a value in ONE field in the child side of the relation and all 5 or 10 or 25 fields in the child record get automatically populated.

[FossilFM]

BruceR,

Do you have any example in FileMaker 7 that performs this. I'd like to take a look at it since I'm having trouble implementing it.

I created a FileMaker 7 file with 2 tables and I'm having some problems on exactly what fields and relationship setup I need to do what you just said. The file has two tables that are related to each other by the parent ids.

Thanks.

[bruceR]

Actually, much to my astonishment, Jacob22 is wrong. You can create a parent record and a fixed set of related records by entering data ONLY in the parent record. I am boggled. It turns out to be quite easy. See attached.

CreateMultiple.zip

[spankalee]

Be very, very careful with this "feature". I'm doing something similar and it's causing me headaches. My way is slightly different, so you may not have the same problems, your method with the auto-enter may even be a solutions to my problem. FileMaker tech support wasn't even aware of the "feature" i discovered until I called in with a problem a few weeks ago.

My setup was an invoice that had two addresses (Billing and Shipping). Instead of defining the fields BillingStreet, ShippingStreet, BillingCity, ShippingCity, etc... in the Invoice table, I just defined BillingAddressID and ShippingAddressID and created a Addresses table with AddressID set to auto-enter serial. The relationship Invoice::BillingAddressID = Addresses::AddressID with "allow creation of new records" selected took care of everything.

FM7 took care of creating the Address records and setting the correct ID in the Invoice to establish the relationship. If the user manually typed data into an address field the Address record was created, and SetField (BillingAddress::City,"anywhere") etc would also work.

Everything worked fine, but when we started using the database over the network problems started cropping up.

The first issue was that sometimes the address from a newly entered invoice would somehow overwrite the address of a previously entered invoice. A quick look showed that we had two problems: we were getting collisions on the Address IDs (even though they were set to unique, no error was reported), and many times the AddressID was just set to 1, so many invoices were sharing the same Address record.

Another problem was that the Addresses table was littered with duplicate IDs. The symptom of this was that sometimes an address would go blank, and the user would enter it again and everything would be fine.

After upgrading to 7v2 things got a little better, but the problems still happen often enough that I had to add the AddressID field to the Invoice layout and train the users on how to double check and make sure that the IDs were constantly increasing, and how to fix any problems.

Obviously this isn't a good situation. FM tech support has been very nice, but also very slow at finding any reason or solution for this problem.

I personally think it has to do with network latencies and commits. When the client creates a new Invoice the "server" (I'm just using client to serve the DB) creates the invoice record and increments the InvoiceID field and returns the ID to the client. But it doesn't create the related Address records right away. Some order of events is causing FM to get mixed up when creating the AddressIDs. Possibly the user is clicking out of a field which commits the Invoice before entering data for the address. I have a script to copy the address data from a different table, so maybe a well timed commit and data entry trips up FM. I don't know yet.

It sure is a nice feature, but it may be an unintentional feature. There have been no problems at all when using this technique locally, but I suggest doing a lot of testing with multiple simultaneous users before deploying this over a network.

FM has had my files for a few weeks and haven't come to any conclusions yet. I'm hoping that v3 fixes this.