Relationship does not create related records

[mattpalmgren]

I have two DBs: RFQs and Parts.

The RFQ database has these fields: Part Number, QTY Needed, and Part Description.

The Parts database has these fields: Part Number, Part Description.

I have a relationship between these DBs based on the Part Number field, with allow creation of records in this table selected.

When I create a record in the RFQ DB, I enter a Part Number along with other information. I want the relationship to look at the Parts DB, and if the Part Number does not exist, I want a new record created in Parts with the Part Number.

However, a new record is not being created. What am I missing here? Thanks!

[aldipalo]

Question, which table is the parent, Parts or rfq? Where are the parts numbers stored?

[mattpalmgren]

Not sure if I know what you mean. These are two different databases, not tables.

Each record in Parts will be unique based on part number. There can be multiple records in RFQs that can contain the same part number, but there will be more information with it. However, I will be entering data in the RFQs database, not in the Parts database. I just want it to create a record in the Parts database while I am entering data in RFQs if it does not already exist.

[aldipalo]

So, from a relationship point of view the Parts Table(DB) is the parent and the RFQ is the child. One parent to many children. (A one to many relationship)

Another question, when you say two different databases are the housed separately. In other words Parts.fp7 and RFQ.fp7? Or are the within the same file like Orders.FP7

I hope I'm understanding you, so I'll take a stab. If the part numbers are created sequentially in the parts Table(DB) then that's where you want to get the part numbers from. So, if you are in RFQ you click on PartNumber and a value list shows all your part numbers and you chose the corresponding part number then all the corresponding data will automatically fill the fields in RFQ. I have not seen, in my limited experience, an application where you can create a record from either side of a one to many relationship. If the part number does not exist doesn't that mean the part doesn't exist either? If that's the case you need to go to the parts layout or a short form of it and create a new part number and part. This can be easily scripted.

[mattpalmgren]

They are two different FileMaker files. If what you say is true, that a one-to-many relationship cannot create records in the other file, then I will need to look into something else since this ia a one-to-many relationship. Thanks for your help!

[comment]

They are two different FileMaker files.

Makes no difference for the current purpose.

If what you say is true, that a one-to-many relationship cannot create records in the other file

It's not true - and it's not a matter of one-to-many or many-to-one. Simply, you cannot create a record in ANOTHER table by filling fields in THIS table only. If the relationship is set to allow records on the "other" side, you have to input some data that will go into the new record, for the record to created..

For example, suppose you are in the RFQ table, and you have a PartDescription field from Parts on your layout. When you enter an existing part number in the RFQ's PartNumber field, it will match the corresponding record in Parts and the relevant part's description will appear in the description field. When you enter a NEW part number, nothing will happen - yet. But if you start typing into the PartDescription field, a new record WILL be created in Parts.

[aldipalo]

Would that not then require you to have both PartID and fk_PartID on the same layout Or is it any field whatsoever? If the field was PartTable::PartName on the RFQ and there was no widget in the parts table all you need do is write in 'Widget' and it creates a new part record? Is this efficient? If a user types in any name, by mistake, you now have an erroneous part floating around. Is this common practice and how would you make sure these errors don't occur?

Not being argumentative just inquisitive.

[comment]

all you need do is write in 'Widget' and it creates a new part record?

No - you also need to fill the LOCAL PartNumber field - otherwise you'll get an error message to the tune of 'This field cannot be modified until "PartNumber" is given a valid value' (that is assuming the RELATED PartNumber has no auto-entered value, otherwise the answer will be yes).

Mind you, I didn't say this was a good user interface. I was only trying to explain the mechanism behind creating records through a relationship.