7 Replies Latest reply on Jul 14, 2009 3:17 PM by comment_1

    How to distribute amounts

    Donovan

      Title

      How to distribute amounts

      Post

      I got a table (say table1) with a percentage field. All rows in that table add up to 100%. There is also an amount field set to 0 initially.

       

      In another table (table2) I create rows and amounts. I want those amounts to be distributed over table1:amount.

       

      Example:

       

       

      table1.perc    table1.amount

      10             0

      40             0

      50             0

       

      table2.amount

      500 (new)

       Now after adding amount 500 to table2, it should be distributed to table1:

       

      table1.perc    table1.amount

      10             50

      40             200

      50             250

       Now the reason why I can't use calculated fields: The percentages can change. But the amounts that have been distributed may not change!

       

      table1.perc    table1.amount

      20             50

      30             200

      50             250

       When another row is added to table2, that amount should be added to the existing amounts in table1, using the new percentages in table1:

       

      table2.amount

      500

      100 (new)

       

      table1.perc    table1.amount

      20             50+20=70

      30             200+30=230

      50             250+50=300

      Is this possible at all with FMP?

       

        • 1. Re: How to distribute amounts
          comment_1
             I believe you should use (at least) three tables here: let me name them Income (your table 2), Percentages (your table 1) and let's say Checks.

          When a new income is received, you need to create three new check records (assuming there are three records in Percentages). Obviously, this must be scripted.

          You shouldn't be adding to the amounts of existing records in the Checks table, because then you won't have a trail of what happened and why (esp. if the percentages have changed meanwhile).

          • 2. Re: How to distribute amounts
            Donovan
              

            When you say "Scripted" and because (I believe) there are no ways to do cursor loops in FMP scripting, does that mean the solution would be to create 3 tabs? 1 tab containing table2 with the amounts, tab2 containing table1 with the percentages and the 3rd tab containing table3 as you suggest? Then after a record would have been created in tab1/table2 - using scripting - I would need to find all records in tab2/table1, loop through every record and for each record I would need to jump to tab3/table3 and create a new record using the amount of tab1/table2 and the percentage of tab2/table1?

             

            What would be the best trigger to do this? OnRecordCommit?

             

            Are there really no cursor loops possible in scripting? 

             

            How would I prevent the user from entering those "dummy" tabs?

             

            How could I make this process invisible to the user? 

             

            I already have a similar problem to make sure the percentages in table1 add up to 100%. What the user does is entering amounts for that table too, and I should add the percentage to the records based on the amount for the row and the total amount of all records. Again I would need a cursor loop to do that and I believe the only way is to do a scripted "find all" in the layout and then walk through all records setting the percentage based on the total amount and the amount of the record I am in.

             

            The problem is also: on which trigger should this be done? On the onModeExit? OnRecordCommit looks too early to me, because what happens if the commit itself fails for whatever reason? I believe the OnRecordCommit trigger has already executed?

             

            It just feels so "un-pro" to me to do it that way. I was wondering if I am missing something.

             

            I feel that one can make nice apps with FMP, but I also have a vague feeling that my requirements above are almost beyond FMPs capabilities. As I am totally new to FMP, I'd like to verify that feeling.

            • 3. Re: How to distribute amounts
              comment_1
                

              I am not sure what you mean by "cursor loops". As for triggering, I'd suggest you put this aside for now and just place a "Distribute" button on a layout of Income for the user to click after entering/editing the amount.

              Once you have the correct structure (i.e. tables, fields and relationships) in place and the script working correctly (and that would include NOT distributing the same amount TWICE, no matter how many times they  click the button), you can start designing the user experience, including tabs (if you think you need them) and script triggers.

              I don't think this is beyond Filemaker's capabilities at all. The only thing here that goes beyond a standard meat and potatoes invoicing solution is that you need to control the users workflow a bit more tightly.

              • 4. Re: How to distribute amounts
                Donovan
                  

                Cursor loops are statements that return a set of records in memory (not in a form/layout). Then you can use a "cursor" to loop through the records. In Pseudo-Code: 

                 

                FOR myTableRec IN createRecordSet("table1") DO

                  myTableRec.fieldA = myTableRec.fieldA * 2

                  myTableRec.commitChanges

                NEXT myTableRec

                Is my understanding correct that in FMP, you would have to have a layout object that implements this "Cursor"? So for example a tab that you can activate in Scripting and navigate through the records in that layout?

                 

                Or is there a way to do that in memory, without having an associate Layout? (This is really important for me to understand)

                 

                Another problem I have is the Button method you mention. If the users forgets to push the button, I have inconsistent data in my DB. I'd like to make this fool-proof. If I were to use a button, how would you ensure the user actually pressed it?

                 

                Sorry if my questions are stupid. I am really 100% new to FMP. 

                 

                • 5. Re: How to distribute amounts
                  comment_1
                     You can definitely do this in Filemaker - roughly:

                  # TEST FOR EXISTING CHECKS If [ Checks:: IncomeID ] Show Custom Dialog [... ] ... Else Set Variable [ $incomeID ; Income:: IncomeID ] Set Variable [ $amount ; Income:: Amount ] Set Variable [ $percentages ; List ( Percentages:: Percentage ) ] Go to Layout [ Checks ] Set Variable [ $i ; 1 ] Loop Exit Loop If [ $i > ValueCount ( $percentages ) ] New Record Set Field [ Checks:: IncomeID ; $incomeID ) Set Field [ Checks:: Amount ; $amount * GetValue ( $percentages ; $i ) ] Set Variable [ $i ; &i + 1 ] End Loop ... End If

                   



                  Donovan wrote:
                  Another problem I have is the Button method you mention. If the users forgets to push the button, I have inconsistent data in my DB. I'd like to make this fool-proof. If I were to use a button, how would you ensure the user actually pressed it?

                  Yes, that is an issue that needs to be addressed - all I said is that I would address it at the end, rather than at the beginning.



                  • 6. Re: How to distribute amounts
                    Donovan
                      

                    Wow! Great answer! Thank you!

                     

                    This confirms what I believed that I need to work with layouts to update/insert/delete records.

                     

                    • 7. Re: How to distribute amounts
                      comment_1
                        

                      Donovan wrote:
                      This confirms what I believed that I need to work with layouts to update/insert/delete records.

                      Yes, that is correct: you must have a layout in order to establish the context.