Creating a parent record through a child record?

[oilcan]

Hello all,

I am quite the beginner with Filemaker. I made a post here once long ago, and was able to work out a solution for our current database problem. Since then, I haven't done any filemaker work. Nowadays, I've been tasked with designing a database for handling new production our lab is undertaking. Its a monstrous task, as the database will be quite complex, and I have only tinkered under the hood on an existing filemaker database, never built one from the ground up.

I foresee many difficult upcoming problems, but here is one of the first ones. I shall focus on two related tables, Shuttle Vectors and Peptides.

Each record in Shuttle Vectors will refer to only one Peptides record. Each record in Peptides may refer to 0 or more of the records in Shuttle Vectors. So I've decided that structurally, Peptides represents the parent, and Shuttle Vectors represents the child. Please correct me if my thinking on this is inaccurate.

So here's the issue. The Shuttle Vectors table from the perspective of those that will use the database is of more interest than the peptides table. I currently have in the Shuttle Vector table a model working where you can select from a dropdown to choose which record in the Peptides table will be the foreign key. However, as I currently have it set up, to create a new Peptides record requires that you go to the Peptides layout and create one. What would be preferable here is that a user would be able to, in the Shuttle Vectors layout, either choose one existing record from Peptides from a dropdown, or add a new record to peptides by typing in their own entry. I had considered just using a custom value list for peptides instead of making its own table, but each peptide has multiple attributes, and for the sake of avoiding future pitfalls I want to be thorough in archiving all this information.

so if my logic about which is the parent and which is the child is correct, it seems that what I want to be able to do is create a parent from a child. Is this even possible? or should I just stick with a convention where if they are working with a new peptide they should go to the Peptides layout and manually enter it?

My apologies if I'm not making much sense here. My only experience with filemaker has been tinkering under the hood on our current FM5.5 database, and it is by no means constructed by proper database design, so foreign keys and primary keys and the like are very new to me.

[jamesducker]

I think I *nearly* understand. It'd be really helpful if you could reply attaching a sample file showing what you've got so far.

Yours

James

[Matthew F]

The quick answer to your questions is that you can create a related record from either the parent or the child.

You were correct in choosing a "dropdown" list as the field style. If the users want to create a new related record that is not currently listed then they just need to type a new peptide name. You will need to make sure that you have selected the option to create related records in the relationship graph. (Double click the little connector box between 'peptides' and 'shuttle vectors' in your graph and select 'allow creation of records in this table via this relationship'.

I can imagine a situation where you might want to use peptites with more than one shuttle vector and vice versa. In this case you would want to set up a many to many relationship rather than your proposed one to many relationship. To do so you simply link the two tables together through an intermediary table. The intermediary has only two fields, 'shuttle ID' and 'peptide ID'.

If you would like to see an example of FilemakerPro application for plasmids you can check out the the 'MPD Templates' link at the following website: http://www.fhcrc.org/science/labs/fero/MMUG/LabDatabases.html Open the 'Directory' file and choose 'Plamids' from the reagent database.

[oilcan]

thanks for your reply, mfero. it was very helpful. I'll take a look at your database link and see how others have approached it.

fyi, I'm doing my best to design this database around the work flow, and as our data is currently being modeled, there is not a case where one shuttle vector record will be associated with more than one peptide record. There is a step down the line in protein production where a tethered peptide may be thrombin cleaved and an exchange peptide inserted, but that step shall be several tables removed from our shuttle vectors table.

thanks again.