1 2 Previous Next 15 Replies Latest reply on Mar 5, 2010 4:33 PM by philmodjunk

    Assigning Fees to Multiple Types of Recipients at Different Times

    spectre6001

      Title

      Assigning Fees to Multiple Types of Recipients at Different Times

      Post

      Relationships are as follows (accounts table is the core):

       

      accounts -< fee terms -< fees

      accounts >- dealers

      accounts -< account brokers join table >- brokers -< reps

      accounts >- managers

      accounts >- sponsors

       

      I want to set up portals in each of these (sponsors, managers, brokers, dealers) tables to display the fees they receive. I think I probably have to make duplicate tables, but it's not working for some reason. I might be in a little over my head, but that's how you learn... Thanks in advance.

        • 1. Re: Assigning Fees to Multiple Types of Recipients at Different Times
          spectre6001

          I suppose it's noteworthy that the fee terms dictate who gets the fees, when, how much, and from where, and the fees table records each individual instance of the fees.

          • 2. Re: Assigning Fees to Multiple Types of Recipients at Different Times
            philmodjunk

            Can you explain more about "fee terms"?

             

            From here it looks possible that you could put dealers, managers, brokers, and sponsors in the same table.

             

            The key issue, as I see it, is how the fee terms work. Does one fee term specify one fee for one broker and a different fee for a manager, sponsor,... etc. Or is it one fee for one individual for each fee term record?

            • 3. Re: Assigning Fees to Multiple Types of Recipients at Different Times
              spectre6001

              I thought about putting them all in the same table, but they all require different sets of data and are treated different ways in other places.

               

              The fee terms state where the fees come from (money in, money out, assets, and distributions), how much (dollars per something, percent of something, etc.) when they happen (when the money comes in, when it goes out, monthly, or when assets are sold), and who receives them (dealers, managers, brokers, sponsors). Each account has its own unique set of fees.

               

              There are multiple fee terms for multiple accounts, multiple recipients, multiple sources, at multiple times (I think I got them all).

              • 4. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                philmodjunk
                spectre6001 wrote:

                I thought about putting them all in the same table, but they all require different sets of data and are treated different ways in other places.

                 

                Well that doesn't absolutely indicate a need for separate tables. It might or there might be ways to handle the diffrences while still keeping the records in the same table.

                 

                The fee terms state where the fees come from (money in, money out, assets, and distributions), how much (dollars per something, percent of something, etc.) when they happen (when the money comes in, when it goes out, monthly, or when assets are sold), and who receives them (dealers, managers, brokers, sponsors). Each account has its own unique set of fees.

                 

                There are multiple fee terms for multiple accounts, multiple recipients, multiple sources, at multiple times (I think I got them all).

                 

                I could figure most of that from your first post, but it doesn't fully answer my question. I see you have multiple fee term records for one account. If I were to look at just one fee term record, would I see terms for just one recipient or multiple recipients? In other words, if you have several brokers and a manager getting fees from a given account, does each have a different fee term record?

                 

                • 5. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                  spectre6001

                  I misunderstood the question. One fee term record would indeed have one recipient.

                  • 6. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                    philmodjunk

                    It looks like your various recipients should be linked to Fee Terms rather than directly to accounts.

                     

                    Fee Terms can function as a "star join" table that links to Accounts, Fees and Recipients.

                     

                    Apologies if you have already considered this, but if you can have one table in place of the separate tables for you recipients, your structure can be a lot simpler.

                     

                    Methods that might enable you to do this:

                     

                    Define a lot of fields in the combined table with only a subset of these fields actually in use for a given type of recipient. Then design dedicated layouts for each recipient type that contain the fields needed for each type of recipient.

                     

                    Use scripted finds to group your recipients by type and prevent viewing a recipient while on the wrong layout.

                     

                    Use filtered relationships that link a related table only to one type of recipient, or only one type of recipient at a time--this is especially useful in setting up portals of recipients that list just managers or just sponsors or...

                    • 7. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                      spectre6001

                      I combined all fee recipients (save brokers, as they are very different and their fees can be handled in another way) into a common table as suggested, and used fee terms as a "star join" table as best I can figure it means to do so.

                      Notation: "table1" -(relating field)< "table2" where -( )< is "one to many" and >( )- is "many to one"

                       

                      I tried:

                       

                      accounts -(account name)< fee terms -(fee recipient)< fees

                      and fee terms >(fee recipient)- fee recipients

                       

                      I also tried:

                       

                      accounts -(account name)< fee terms -(fee recipient)< fees >(fee recipient)- fee recipients

                       

                      There are portals in accounts to fee recipients and fee terms;

                      portals in fee recipients to accounts and fees

                       

                      I've tried a number of different arrangements of tables, at best I've been able to get the fee recipients to show up in the appropriate places in the accounts portal, and get the accounts to show up in their portal in the fee recipients portal, but I've yet to get it to create a fee record at all (the appropriate boxes are checked, I've looked a dozen times).

                      • 8. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                        philmodjunk

                        How are you trying to do that? From what layout, based on what table occurrence? Using a portal to what table occurrence? (or are you attempting a different method?)

                        • 9. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                          spectre6001

                          The portals in the accounts layout are:

                          "fee terms" (based on the fee terms table)

                          and "fee recipients" (based on the fee recipients table)

                           

                          The portals in the fee recipients layout are:

                          "accounts" (based on the accounts table)

                          and "fees" (currently based on the fee terms table)

                           

                          No records will create in the fees table to show up in the associated portal in the fee recipients layout.

                          • 10. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                            philmodjunk

                            I had to play with a sample file until I liked the results:  http://www.4shared.com/file/235041189/db5f39ec/MultiFeeRecipients.html

                             

                            As I see it, you need two pieces of information before you can record a fee in the fees table: Account Name and Recipient.

                             

                            I used the following relationships:

                             

                            Accounts::AccountName = FeeTerms::AcctName

                             

                            Recipients::RecID = FeeTerms::RecID AND

                            Recipients::SelAcct = FeeTerms::AcctName (enable allow create for fee terms)

                             

                            Fees::FeeTermID = FeeTerms::FeeTermID (enable allow create for fees )

                             

                            FeeTerms::FeeTermID and Recipients::RecipID are auto-entered serial numbers. SelAcct is a text field formatted with a list of account names from the account table.

                             

                            I placed a portal to fees on recipients with Two fields: Date, Amt (Amt is set to lookup a value from FeeTerms::Amt)

                            If I select an account name from SelAcct and enter a date in Fees:: Date, I see the appropriate amt from FeeTerms copied into the Fees::amt field.

                            This is just a proof of concept. A more sophisticated calculation can be put in place of the look up in order to compute a fee.

                             

                            Note: using an AccountID number instead of a name would make this a more robust solution.

                            • 11. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                              spectre6001

                              Recipients and accounts are a many to many relationship, so I can't have an account name field in the recipients table. Otherwise, that's how I have it currently.

                               

                              At this point, everything seems to be working like it should save the lack of record creation in the fees table from the fee terms table. All of the portals are working like they should save the lack of information from the fees table (because there aren't any records being created). I'm sure at this point it's something very simple that I just can't see for the trees.

                              • 12. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                                philmodjunk

                                Check out the sample file.

                                 

                                The field is only used to select a an account so that the fees can be entered successfully. Thus, it does not keep the relationship from being many to many as you can select any number of accounts. The drop down can be set up to only list those accounts that are assigned to the current recipient.

                                • 13. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                                  spectre6001

                                  I need to be able to set the fee terms from a portal in the account layout (each account has its own set of fees).

                                   

                                  Using the terms in the fee terms table (and triggered by things such as dates, purchases, etc. associated with the accounts table), records must be created in the fees table.

                                   

                                  A portal within the fee recipients table must display the fees associated with that recipient for calculation and reporting purposes.

                                   

                                  Right now, all of that is working, but for some reason I can't get anything to create in the fees table. Regarding just that relationship, I deleted the table and everything associated with it to start from scratch. Assume at this point we have only the fee terms table consisting only of a fee terms ID and a fees table consisting solely of a fees ID and a fee terms ID. They are joined via the fee terms ID fields, and the allow creation box is checked on the fees side. It may be that I've been staring at this for too long, but I think that if I create a record in the fee terms table resulting in a new fee terms ID, I should have a new fees record with a matching ID and a new fees ID. Is that not correct? If I could get that relationship to work correctly, all of my up front fees will be taken care of and I can figure out some other way to handle the recurring and back end fees. I've got to be doings something wrong, but I've reduced it to the absolute simplest form possible and it's still not working.

                                  • 14. Re: Assigning Fees to Multiple Types of Recipients at Different Times
                                    spectre6001

                                    Nope. I've been staring at it FAAAAAARRRR too long. I have scripts written all over the place to create new records given the entry of various fields, and for some reason I forgot all about it.... It's been a long week.

                                    1 2 Previous Next