1 2 Previous Next 15 Replies Latest reply on Dec 11, 2009 12:43 PM by liquidcooled

    Script Efficency

    martinpaulrice

      Title

      Script Efficency

      Post

      Hi,

       

      Just finished writing my first script of more than two lines in FM Pro 10 on an iMac running OSX 10.6.2. Amazingly enough, the script works. However, I was wondering whether there's a more efficient way of writing this script other than using all the "else if"s?

       

      Here's the script:

       

       

      #Subtract from "From Account/Add to 'To Account"#If [Transactions::From Account Type = "Spending" and Transactions::To Account Type = "Expense"]Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)##Variation for "From Account" being Spending and "To Account" being Savings#If [Transactions::From Account Type = "Spending" and Transactions::To Account Type = "Savings")Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount)Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)##Variation for "From Account" being Accrual and 'To Account" being Spending#Else If [Tansactions::From Account Type = "Accrual" and Transactions::To Account Type = "Spending"]Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)##Subtract from "From Account"/Subtract from "To Account"#Else If [Transactions::From Account Type = "Spending" and Transactions::To Account Type = "Credit Card"]Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]Set Field [Budget 2::Current Balance; Budget 2::Current Balance - Transactions::Amount]##Add to "From Account"/Add to "To Account"#Else If [Transactions::From Account Type = "Credit Card" and Transactions::To Account Type = "Expense"]Set Field [Budget::Current Balance; Budget::Current Balance + Transactions::Amount]Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount]##Add to From Account/Add to To Account#Else If [Tansactions::From Account Type = "Income" and Transactions::To Account Type = "Spending"]Set Field [Budget::Current Balance; Budget::Current Balance + Transactions::Amount]Set Field [Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount]End If

       Any suggestions for making this more efficient would be greatly appreciated.

       

      Thanks! 

       

        • 1. Re: Script Efficency
          philmodjunk
             I don't think you'll find many people willing to try to unravel your script unless you repost it with each script step on a separate line. It's just too hard to read the way you've posted it. :smileysad:
          • 2. Re: Script Efficency
            martinpaulrice
               Phil, thanks for the response. I don't know what happened. When I typed it in, it was all very nicely formatted, but then after I sent it, it turned out to be the unreadable mess it now is. I'll try to post it again. Also, thanks for the response to my question about getting the script out of FM. I don't have the Advanced version, but printing to pdf will work just fine.
            • 3. Re: Script Efficency
              philmodjunk
                 I've had some of my posts show unexpected formatting also. I've learned that I can use the Edit option in the Options menu located in the right corner, just above the kudos control to edit a post that didn't post the way I wanted it to.
              • 4. Re: Script Efficency
                martinpaulrice
                   Thanks, I'll try that!
                • 5. Re: Script Efficency
                  ninja
                    

                  martinpaulrice wrote:

                  Here's the script:

                   

                  #Subtract from "From Account/Add to 'To Account"#

                  If [Transactions::From Account Type="Spending"and Transactions::To AccountType ="Expense"]

                       Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]

                       Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)

                  ##Variation for "From Account" being Spending and "To Account" being Savings#

                  If [Transactions::From Account Type = "Spending" and Transactions::To Account Type = "Savings")

                       Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount)

                       Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)

                  ##Variation for "From Account" being Accrual and 'To Account" being Spending#

                  Else If [Tansactions::From Account Type = "Accrual" and Transactions::To Account Type = "Spending"]

                       Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]

                       Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount)

                  ##Subtract from "From Account"/Subtract from "To Account"#

                  Else If [Transactions::From Account Type = "Spending" and Transactions::To Account Type = "Credit Card"]

                       Set Field [Budget::Current Balance; Budget::Current Balance - Transactions::Amount]

                       Set Field [Budget 2::Current Balance; Budget 2::Current Balance - Transactions::Amount]

                  ##Add to "From Account"/Add to "To Account"#

                  Else If [Transactions::From Account Type = "Credit Card" and Transactions::To Account Type = "Expense"]

                       Set Field [Budget::Current Balance; Budget::Current Balance + Transactions::Amount]

                       Set Field (Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount]

                  ##Add to From Account/Add to To Account#

                  Else If [Tansactions::From Account Type = "Income" and Transactions::To Account Type = "Spending"]

                       Set Field [Budget::Current Balance; Budget::Current Balance + Transactions::Amount]

                       Set Field [Budget 2::Current Balance; Budget 2::Current Balance + Transactions::Amount]

                  End If

                   

                  +++++++++++++++++++++++++++

                  Had a few minutes...but not enough to dig in...looks like a planning budget.

                  Have a great night!


                  • 6. Re: Script Efficency
                    martinpaulrice
                       Ninja, thanks so much for doing this, I really appreciate it!
                    • 7. Re: Script Efficency
                      philmodjunk
                        

                      Thanks Ninja,

                       

                      MartinPaulrice,

                       

                      I'm not convinced this should have been scripted in the first place. Normally, you can use calculations and relationships to do basic math like adding and subtracting. That way, simply entering data will trigger the totals and such to update without needing to run a script each time. That also avoids problems that occur when you run such a script and then discover that you mistyped a number. (Then you have to choose between running a second script to "unpost" the transaction so that you can your "post" script again or you have to make an error correction entry and post it. Both options are messy and generally unecessary.)

                       

                      Here's how I set up a basic account ledger table:

                      TransID : Auto-Entered Serial Number

                      TransDate: Date

                      AccountID: Text or Number

                      Description: Text

                      Debit: Number

                      Credit: Number

                      cBalance: Debit - Credit

                      AccountTotal: Summary, Total of cBalance, Running--Restart Summary for each sorted group (AccountID)

                       

                      You can enter all transactions for all your accounts on one layout, and see the balance for each such account.

                       

                       

                      • 8. Re: Script Efficency
                        martinpaulrice
                          

                        Phil:

                         

                        I just didn't read what you wrote about using a system of debits and credits carefully enough. I see that your system deals easily with the problems of changes and deletions, about which I just wrote you in the other thread. (I'm sorry I created two threads for these questions. It would have been better to combine them into one.) I see that the "chart of accounts" is taken care of with the AccountID field -- for which I could set up a look up list. This system should really be able to work for me and it is infinitely more simple than what I was trying to do.

                         

                        The only thing I'm not quite sure about is where to set up the budget part against which I'd compare the Account Totals. I suppose I could do another table that would have the account names (AccountID) and the budgeted amounts and have the two tables related for reporting that shows what the cBalance is compared to the budgeted amount. I'd also need to accommodate beginning monthly balances for the spending accounts so I could keep track of checking, saving, accrual and credit card balances.

                         

                        In the meantime, I'll set up a table like the one you described and enter practice transactions to make sure that I'm doing it right.

                         

                         

                        Thanks yet again! 

                        • 9. Re: Script Efficency
                          philmodjunk
                            

                          Sounds like you're on the right track here. I have a little DB I use just to track my personal spending and I have a budget table related to my "ledger" table much like you describe. In my budget and ledger tables I have "month" fields that I use to link to specific sets of transactions in the ledger.

                           

                          My cTransMonth key looks like this:

                           

                          Date ( Month(TransDate); 1 ; Year(TransDate) )

                           

                          I have the same type of "month" field in my budget field and set up a relationship like this:

                           

                          Budget::cMonth = Ledger::cMonth AND

                          Budget::AccountID = Ledger::AccountID

                          • 10. Re: Script Efficency
                            martinpaulrice
                              

                            Yep, that's pretty much the way it's working out as I build this thing, thanks.

                             

                            One thing I haven't been able to figure out looking at the basic ledger table you posted above is why you have only one account and both a debit and a credit in the record? Do you make two records for each transaction, that is, one account of the transaction is a debit record and then you enter another account for the credit side of the transaction? Or is this not a double-entry system?

                             

                            Thanks for any clarification. 

                            • 11. Re: Script Efficency
                              philmodjunk
                                

                              Having both fields in the same record makes it easy to report all debit and credit actions in a single report. The table's structure permits you to make an account transfer a single entry, but there's no reason you can't use double entry accounting. You can even use two layouts, one for debits and one for credits if you wish, just remove the debit field from the credit layout and vice-versa.

                               

                              DoubleEntry example:

                              11/24/09 cash      Trans to Cash, debit: $10.00

                              11/24/09 checking Trans from checking, credit $10.00

                               

                              would move $10.00 from Checking to Cash

                               

                              If you need to record the second account in each transfer, you can add a "fromAccount" field to your table and layouts.

                              • 12. Re: Script Efficency
                                martinpaulrice
                                  

                                That's perfect, Phil, just what I needed. With all the information you've given me, I know I can get the app just the way I wanted it, and without the ungainly scripts.

                                 

                                Thanks so much! 

                                • 13. Re: Script Efficency
                                  liquidcooled
                                    

                                  Hello I've been following well actually as many of PhilModJunk's posts as I can on double entry system. I'm new to filemaker and I'm trying to make a multi location inventory. I'm doing a log as described but am having trouble figuring out how to create a double entry as described in this thread in one form. I need to essentially move an item from one account to another and need it all to be on one form. It seems that there has to be a simple way to create an out entry and an in entry in what seems like one step to the user but I'm having a heck of a time wrapping my new to filemaker head around it.

                                  Thanks

                                  Tim 

                                  • 14. Re: Script Efficency
                                    philmodjunk
                                      

                                    Hmm, I'm far from expert on accounting practices (one semester of accounting does not produce a bookkeeper!)

                                     

                                    I'd consider a pop up custom dialog or window (new window script step) To do this. The user would fill out a form and then click a button to submit the transfer. The script would then take the single amount, date and two account ID's to generate both entries automatically. (That's how my bank does it when I use their web site to transfer funds to a different account BTW)

                                    1 2 Previous Next