A newbie looking for help - sorting / relational

[juju158]

I'm a newbie to Filemaker, and our firm has a license to use Filemaker 5.0 Pro.

I currently use a "poor-man's" database - an Excel spreadsheet with multiple worksheets. On one worksheet - I have a list of every "firm" that ALL of my clients to business with as a whole (lets say 100 firms). On the other 40 individual worksheets, I have all the fixed info for EACH client (phone, address, etc) and then, about halfway down the worksheet, a have a list of "firms" THAT that client does business with (lets say 15 or so, of the 100). Each client has a different subset of "firms" - lets say any combination, from 5 firms up to 30 firms. No client talks to all 100 firms. The firm cells are referenced back to that first worksheet so I only have to change data regarding "firms" on the first worksheet. I realize this may be hard to visualize.

I'm interested in setting up a database to replicate this. I have the static part done. I have a CLIENT_MASTER database which has the info for each client. I have the FIRM_MASTER database which lists all 100 firms. Finally, I have the MASTER_MASTER which is the main database that where each record looks like a worksheet form Excel spreadsheet. The top is fixed and the data pulls from the CLIENT_MASTER when appropriate. Each record is nice and clean.

Problem: I can't figure out how to get the appropriate "firms" for each client's record. I figured I could do repeating fields, give each firm a code, and type them in that way. However, would I be able to insert a new firm? Would I be able to alphabatize? I thought about using a Portal but I can't seem to think of how I would set this up? I don't want to show all 100 firms for each client, because most clients do business with only 15 or so of the 100 firms.

Help!

Thanks!

Any suggestions? Thanks for reading this if you got this far!

J

[juju158]

Another note - for each "FIRM" - I have a Contact, Phone Number, Address, etc, which I would like to show on each record. These data is all accessible from teh FIRM_MASTER. I would also like to leave a blank "note" field next to these items so I can enter in misc. notes for each firm, specific to each client.

Yikes!