But the creation of a record in the related tables on either side has no effect. Both related tables are enabled to write to the join table.
Why would that automatically create a related record in the join table? How would FileMaker know which record on the other side of the relationship to link to the new record? It could be any/many/none.
"Allow creation of records via this relationship" means that if you place portal to Joining on the TimeSheets layout, you can select a value in Joining::WOOrderNumber to specify a record in WOrkOrderes and a record in Joining will be automatically created and a value in Joining::TimeUnique will automatically get the value of TImeSheets::TimeUnique for the current TimeSHeets record.
Thanks Phil. I want to create a record in joining as soon as I create a record in WorkOrders. That way I can link a record from Timesheets to that joining record via the portal below on my Timesheets layout as soon as I entire a valid WO# in the first field in the portal.
My basic problem is that entering a WorkOrder does not creat a record in the joining table. So far as I can see that until a record with the valid Work OrderNumber exists in the Joining table I am screwed.
My question maybe should simply be how do I get FMP to create a recored in Joining when I create a valid record in WorkOrder?
Again, from FMP Help "It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins."
Is my assumption that with the correct structure records are AUTOMATICALY created in the join table incorrect?
Hmm, my Key field TimeUnique is a calclated field made up from the entry date and the employee code (note FMP changes date date format). I now understand that calculated fields cannot be used as key fields if you want to write data to the related table via a portal. Back to the drawing board.
Use auto-entered serial numbers in TimeSheets and WorkOrders as your primary keys. Use plain, simple number fields in joinings for the corresponding foreign key fields.
Thanks Phil. The serial number will work with the work order table. Actually the key work or number IS a sequence number assigned by FMP with a prefix 13- to identify the year.
Problem I have with time sheets is that I want to limit each employee to one timesheet for each date. The concatenation of TimeCode (which is the employee's code) and TimeDate into the TimeUnique field accomplished that goal. Can I retain the unique status of Time Unique within the Timesheets table and add a serial number field as the key for relationships? If FMP assigns a serial number and then the record is not saved because it fails the unique test is that serial number "used up" or does it remain available for the next saved record?
Thanks for your time.
Actually the key work or number IS a sequence number assigned by FMP with a prefix 13- to identify the year.
Use just a simple serial number. Any time you add addiitonal "value content" to it, such as that prefix, you set yourself up for trouble. These are internal use only fields. "gaps" in them due to a record being created and then reverted or deleted don't matter.
You can have the other fields in your database to keep your users happy, just don't use them as your match fields in the relationships.
You can set up a validation that validates for uniqueness on your TimeCode/TimeDate field with an added text field that concatentates teh two in an auto-enter calculation. Then you set a unique values validation field option on it to catch errors where the same employee has specified the same date as a previous record assigned to them.