The question I posted in your original thread still holds:
What do you want for "past projects"? Records related to all existing projects or ... ?
Based on records of similar Client type in my database, I would like to have the past projects portal displaying the following:
Client, Date (estimate), Sub Category Cost Items and their individual Description and Cost
so that whichever cost item from this second portal the user finds suitable, he or she can paste it into the current project sub category.
Apologies for all the questions but these are basic design questions that drive the final solution. The general solution I am heading for is constructing a relationship that will display the records you want to see. These questions are intended to figure out precisely what you want to see in that portal.
"Based on records of similar Client type "
That appears to be the key detail. I don't see a field that can be used to classify your client records by "type". If this is what you want, I'd add a "type" field to both tables and format the client field with a value list of "types". THen I'd use a lookup or other method to copy this value into a "client type" field in you Sub Category Cost table.
Now you can create a relationship linking the two tables and place a portal on the layout that refers to the new Table Occurrence you'll create when setting up this additional relationship. You can specify a sort order on either the portal or the relationship to insure most recent records sort to the top if that's what you want.
Thanks PhilModJunk for your reply... truly appreciate your inputs indeed. No problem on your questions. I truly am glad that someone experience like yourself can help novice like myself and it takes alot of stress away from trying to develop an application when i'm not a developer by training.
With regards to your type field, I do concur with you. How would you write the script for the lookup or other method to copy the value?
Actually my real problem is how do i create the relationship for the tables, I do know the basics of creating relationship via a primary key, foreign key,... however in this case, it seems very difficult. With reference to my case as illustrated earlier, may I know how would you relate all my tables ?
With great thanks!
Ok, first to get a stored type value automatically copied from your PROJECT table to your Sub Category Costing table.
Define a ClientType field in both tables. You already have the relationship you need to do what you need.
In Manage | Database | Fields, find Sub Category Costing :: ClientType and double click it to bring up the field options dialog.
On the Auto-Enter tab, click "looked up value". In the dialog that pops up, specify PROJECT as the "Look up from related table" and select ClientType from the list of Project fields.
Click OK until all dialogs are dismissed.
You now have a field that will automatically receive a copy of ClientType from Projects. (Note: I am sticking to your defined list of tables. If you have a separate Client information table, and you should, the ClientType field should be part of that table and a look up should copy the value from the client table to Projects also.)
Now create a new relationship for you "similar client type" portal.
In Manage | Database | Relationships, click on the Sub Category Costing table occurrence and then click the button that looks like two green plus signs to make a copy of this TO. Name the TO "Sub Category Costing by Client Type".
Drag from TO to TO to create this relationship: Project :: ClientType = Sub Category Costing by Client Type :: ClientType