Separate Files or Separate tables in the same file? (The options differ depending on your answer to this question.)
What version of FileMaker are you using?
With a relationship set up with "Allow Creation of Records via this relationship" you may not have to copy the investor ID from one table to the other at all.
Example: if you define a relationship from table 1 to table 2 based in the Investor ID fields and enable that option, you can place a portal to Table 2 on your Table 1 layout and when you enter data into the bottom blank row of this portal, FileMaker will copy the Investor ID from the current record in Table 1 to the matching field in Table 2 of this new record automatically.
There are also ways to use globla fields, variables or a script parameter to pass the Investor ID from the current record of one layout to the layout and/or file for the other table where either a record is updated with this value or a new record is created and the number entered into it's Investor ID field.
Thanks Phil, i'm using fp11 and separate files for separate tables. Would this be different?
If you use a portal to the table in the other file, no. If you need to do this with a script, yes.
You can call a script in the 2nd file to do what you want with the ID number with a script in the first file that passes the ID number as a script parametr.
Perform Script [//select script in external file ; YourTable::InvestorID]
#do what you need first here, find a record, create a new blank record or whatever...
Set Field [YourTable::InvestorID ; Get ( ScriptParameter ) ]
thanks very much, this is now working....
another question, I wish to include a field under portal representing sub-total of the line item expense of that portal. Could I use sum function?
Sum ( RelatedTable::NumberField ) defined in the layout's table (not the portal's table), will return the total of all records in your portal if the portal is not filtered. This is the best option, IMO, if your portals do not have a portal filter expression, as it will update more smoothly in reponse to any editing you might do of records in the portal.