How can I get this to happen automatically?
You should give serious consideration to using a solution that keeps all tables in the same file--this can make for a much simpler solution to work with as a developer or as a database administrator.
But there are three ways to keep the additional password dialog boxes from popping up:
1) define identical account names and passwords in each file. Keep in mind that passwords are case sensitive so even capitalizing the same password differently will keep this from working. Here's why this works: when a reference in to a second file not currently open is encountered in the first file, FileMaker first attempts to open the file with the same account name and password used to open the first file. If such exists in the second file, no password dialog appears.
2) Use File Options to specify that the second file open automatically with an account name and password specified in File Options. This is handy if all users only need limited access for ordinary use of the database system and avoids the extra work of defining multiple identical accounts in both files. As the developer, you can still open such a file with full access privileges if you hold down the shift (windows) or Option (mac) key while opening the file in order to force the log in dialog to appear so that you can enter a full access password.
3) A script defined in File 2 can use the Relogin script step to re-log in to the file with a specified account and password. You'd have a very limited access password specified in file options in such a file with just enough access to permit this relogin script to re-log in to the file. File 1 can use Perform Script to perform this script in File 2 and use a script parameter to pass access information to that script to be used to determine which account name and password to use in order to log back into the script in order to set the correct access permissions. This is somewhat of a compromise between 1) and 2) as it avoids multiple matching accounts but allows for more than one level of access to the file as different parameters can cause the script in File 2 to use a different account/password to re-log in to the file.
1) is generally easiest to set up, but if you want users to be able to change their passwords regularly, it can be a lot of work to administer. This work can be greatly reduced, however by adding scripts to each file that create/delete accounts and passwords with a script in the main file that passes account name and password info to each similar script in the other files in a script parameter. The 2nd and 3rd options reduce the work load needed in order to maintain multiple matching accounts, but also reduces your ability to use account names to uniquely identify the user or set up record level access control where different users are restricted to different sub sets of records in a given table.
And most of this can be avoided if you put all your tables into a single file.
Note: I've been very careful to use the term Account name in this post and not User Name. User names are specified in Preferences. Account names are specified in Manage | Security. While an account name is often the same as a user name, they are often different and they are specified in different locations. And just to make things confusing, when FileMaker opens a log in dialog, it enters the user name as the default account name...
Thanks for the response. I forgot to mention one key piece of information - these files are on a FM Server that hooks up to our Active Directory. So 1, 2 and 3 look like they are out.
I guess I could re-implement these all in one file so I don't have to worry about opening external files and such - I think you make a good point about using a consolidated file and reimplementing now will be easier than later ... but I was hoping to avoid that until later.
Is there no easy way to have FM just open Solutions with user X (as verified by AD) and then use those credentials to open up Markets and then Sectors?
BTW - I did set Sectors to open as admin (File Options > Log In As) which works but bypasses all the security I am trying to set up. Same with Guest.
That is a very key piece of info and not a part of the system that I have had to deal with, but you should still be able to set this up so that you don't have to enter an account name an pssword. Using AD does not mean that you don't have accounts and passwords defined in the DB as I understand it, but rather you set up the AD to match the groups in AD to specific accounts in FileMaker--usually one account per privilege set as I understand it.
From what I read in the FileMaker Help entry titled: "Creating accounts that authenticate via an external server" it would appear that you can follow steps 1-9 at least once on each file to set up externally authorized accounts with identical account names in each file.
And I don't see anything that precludes options 2 and 3 from being used in combination with AD, though there is probably no need to do so.
Thanks - I decided to follow your first advice and merge all the files together. Looks like things are working correctly now. Your help was very much appreciated.