For a hobby here at home, I have a database with three tables: people, items, and items owned. People and Items Owned are both related through a serial number and Items and Items Owned are related through a second serial number.

In the layout for people I have a portal that shows items which that individual might purchase. Once a selection is made, I'm trying to write a script that will create a new record in the table of items owned with the serial number of the person and the serial number of the item purchased.

Is there a script step that will enable me to create a related record directly? Alternatively, is there a way to store both serial numbers temporarily, so I can paste them into the appropriate fields of a new record in "items owned"? I've tried "copy" and "paste" but this doesn't seem to work.

The only solution I've worked out so far is to have a preexisting record for each person in the table of items owned. Then I can "go to related record" in that table, duplicate it, and use the relationship between Items Owned and People to retreive the serial number of the item just purchased. But this is inelegant. Surely there's a better way?

Thanks,

Chuck Henebry

Yes you can do this in many ways. With FM 7 you can cascade between multiple relationships. If People is related to Items and Items is related to Items owned, with each relationship checked for 'Allow creation of records in this table via this relationship', you can have a portal to Items owned on the people layout. Create a button script for your list of new purchases, use a 'Go to Field' selecting the related field available from your portal, use a 'Go to Portal Row' selecting last, to make sure it adds a new row, then use 'Set Field' to add the data you want. You should use multiple set fields to set the proper Serial Number IDs as well as new purchase data.

Or

Create a script that created a new record in the related table, and use Set Fields from related tables.

Thanks! I see now that my problem was that I didn't have my relationships marked for "Allow creation of records via this relationship." I appreciate your time and help.

Chuck