3 Replies Latest reply on Mar 24, 2010 7:48 AM by stevelucky

    How would YOU structure this?



      How would YOU structure this?


      I'm pretty new to FileMaker Pro but have quite a technical background and pick things up pretty quickly. Let me lay out the scenario of what I'm trying to accomplish and I'd like to hear how you would structure it.

      I have several sales reps. Each of these sales reps has multiple accounts that they've signed. There are commissions associated with these accounts that they get paid.

      I'm trying to create a database where I can enter in personnel info for individual reps to be displayed at the top of the page. Beneath that there would be 2 or 3 tabs associated with their accounts. On one tab I would like to be able to add their accounts, one row for each account. I want to be able to add many accounts here as this will grow over time. On each of these rows I will have fields corresponding to their commissions that get paid out over a one month period. I will then have a 2nd tab that keeps track of commissions they get every month from these accounts.

      My question is, does this need to be done using two tables? 1 for the rep info and 1 for their accounts? Or should I just use one table?

      Please let me know if you need any more info.


      P.S. - Using FileMaker Pro 11 on Mac OSX 10.6

        • 1. Re: How would YOU structure this?

          Definitely, at least 2 tables.  In the Accounts table, you need a field to have a field to hold the Unique ID of the Sales Rep.  And you relationship will use the Unique ID (an auto-enter serial number) from the Sales Rep table and that foreign key in the Accounts table.

          • 2. Re: How would YOU structure this?

            Can any of the accounts have more than one sales rep covering it?


            Can a sales rep be assigned to an existing account when another sales rep quits?  Do you want to report the aged commissions against the original sales rep?

            • 3. Re: How would YOU structure this?

              There will only be 1 saleman per account, and if a sales person leaves, the account won't be reassigned but just become a "house account". Essentially it will be orphaned and assigned back to the company internally. I won't really need to track anything at that point. In fact, when a sales person leaves, they and their accounts will probably be removed from this system.