I had to think back. I've helped one other person with the same exact question:
Your Tables and relationships might be set up like this:
Model::__pkModelID = OrderLineItems|ByOrder::_fkModelID
Order::__pkOrderID = OrderLineItems|ByOrder::_fkOrderID
Order::_fkFactoryID = Model|ByFactory::_fkFactoryID
Model|ByFactory::gOrderID = OrderLineItems|ByFactoryOrder::_fkOrderID AND
Model|ByFactory::__pkModelID = OrderLineItems|ByFactoryOrder::_fkModelID
Enable "Allow creation of records via this relationship for OrderLineItems|byFactoryOrder in the above relationship.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
There are several tricky details to set up:
gOrderID should be a field with global storage specified in field options. You'll need to set up the ONRecordLoad script trigger to automatically update it with the value of Order::__pkOrderID. You set up Portal A as a portal to Model|ByFactory with a Quantity field included from OrderLineItems|byFactoryOrder. When you specify a quantity in this field, a record in OrderLIneItems will automatically be created and will appear in Portal B--a portal to OrderLineItems|ByOrder.
The blue Tutorial: What are Table Occurrences? are occurrences of your OrderLineItems (OrderJoinFactory) table. The green are occurrences of Model.
Thank you Phil!
I tried your suggestions, however I am still having trouble with making the portals work together, because I do not think I am following your instructions properly.
For connecting Model|ByFactory and OrderLineItems|byFactoryOrder, I am unsure about how to create the one to one relationship. I just made the foreign keys unique in validation tab. Does that create problems? Was I suppose to create serial numbers for the foreign keys as well or was I suppose to do something else?
For the layout, I found the OnRecordLoad script trigger, but I am not sure how to make it automatically update gOrderID. I created a new script and called it OnRecordLoad and specified it in the script trigger. The script has
Go to Field [Model|ByFactory::gOrderID]
Set Field [ Order::_pkOrderID]
The value list to choose the factory does not seem to be working anymore, but I cannot tell because Portal A is not working.Any suggestions on how to fix the problems?I will attach a picture of the updated database.I really appreciate your help.
Foreign key fields should never be constrained as unique nor should they be auto-entered serial numbers. They get their values from the primary key of a matching record in a related table. OrderID in OrderJoinFactory cannot be constrained to unique values as you can have many records in OrderJoinFactory that must link to the same record in Order.
Your script should read:
Set Field [ Model::g_OrderID ; Order::_pkOrderID]
When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.
Most of the portal work now. Thank you so much!
I also changed the field for Factory Code (drop down box) from Factory:: _pkFactoryID to Order:: _fkFactoryID, because it stopped working with the ID from FactoryTable. I don't what happened, but I can select most of the factories just fine, but I am unable to select certain factories. For example:
If factory1 is currently in factory code, when I try to select factory 3, it still stays at factory1 but when I try to select factory11, I can select it with no problem and everything works fine.
I fixed the factory code problem. :) Thank you very much Phil! You are awesome!