Script Efficiency

[Martin Rice]

Hi, I just finished my first script of more than two lines It's loaded with "else if"s and I was wondering whether there is a way to make the script more efficient. Here it is:

#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 would be greatly appreciated. Thanks.

Martin

Signal Mountain, TN

[LaurenKuhlman]

I would agree with comment below on the summary field solution in general but your solution may actually require this kind of transaction processing. Anyway, a few things if you decide to go this route. I would use script variables as outlined below. Makes it more readable. Also, judicious use of and / or can shorten up your script. Make sure to use parentheses. And/or in FileMaker does not follow a typical order of precedence. Don't worry if you don't know what that means. Just always be sure to use parentheses when working with and / or.

These last 2 are more general FileMaker learning things that might help you:

I noticed a spelling error in the written script where Transactions was spelled Tansactions but only in some places. I just wanted to let you know how I output text from scripts. Use the print button on the Script Manager / Maker screen. Save to PDF (easier on a mac), open the pdf, highlight the text and paste it anywhere you want. Sometimes you get footer info in there if its multi-page but its still faster than typing it out.

I also noticed one of your table occurrences is named Budget 2. At this point in your learning, I would suggest finding some info on Anchor / Buoy. It will radically change (for the better) your development. Our own Roger Jacques developed this system of relationship graph management. I have contacted him to ask for a doc I can send your way on this subject. He is west coast so it may be a while before he is able to send it my way.

Set Variable [ $fromAccountType; $fromAccountType ]

Set Variable [ $toAccountType; $toAccountType ]

Set Variable [ $amount; Transactions::Amount ]





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

#

If [ ( $fromAccountType = "Spending" and ( $toAccountType = "Savings" or $toAccountType = "Expense" ) ) or ($fromAccountType = "Accrual" and $toAccountType = "Spending" )]

Set Field [budget::Current Balance; Budget::Current Balance - $amount)

Set Field (Budget 2::Current Balance; Budget 2::Current Balance + $amount)

#

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

#

Else If [$fromAccountType = "Spending" and $toAccountType = "Credit Card"]

Set Field [budget::Current Balance; Budget::Current Balance - $amount]

Set Field [budget 2::Current Balance; Budget 2::Current Balance - $amount]

#

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

#

Else If [( $fromAccountType = "Income" and $toAccountType = "Spending" ) or ( $fromAccountType = "Credit Card" and $toAccountType = "Expense" )]

Set Field [budget::Current Balance; Budget::Current Balance + $amount]

Set Field [budget 2::Current Balance; Budget 2::Current Balance + $amount]

End If

[Martin Rice]

Thanks for the response, Lauren. I know what variables are, however, I've never used any in FM scripting, and I'm not sure what you would use them for or where in this script.

Thanks again.

[comment]

I am not sure I follow this fully, but it looks like - with the correct relationships in place - the entire script could be reduced to:

Set Field [ Debit::Balance ; Debit::Balance - Transactions::Amount ]

Set Field [ Credit::Balance ; Credit::Balance + Transactions::Amount ]

However, I believe you should rethink your entire strategy, and change the balance fields into calculation/summary fields.

[Martin Rice]

Lauren, thanks so very much for taking the time to revise the script for me. I appreciate it greatly. Also, thanks for answering the question about exporting the script. I can't believe that I saw the Print button in the script window and didn't think about printing to a PDF.

Just as an aside, the way I did get the script to post here was really kludgey. I took a screen shot of the script's text; then I fired up my DEVONthink Pro Office and had it read the screen shot into a document and used DEVONthink's built-in OCR to convert it to text. The built-in OCR software really works well (especially if you're not swift enough to think of printing to PDF

In another version of the script, I did try to combine permutations by using and/or but didn't use parens so it didn't work. Thanks for pointing that out to me.

One thing I don't understand about the way you declared the variables in the script is that I see the assignment in

Set Variable [ $amount; Transactions::Amount ]

But in

Set Variable [ $fromAccountType; $fromAccountType ] and

Set Variable [ $toAccountType; $toAccountType ]

I don't see any actual assignment. Analogously to $amount; Transactions::Amount, I would have expected something like

$fromAccountType; Budget::From Account Type and

$toAccountType; Budget2::To Account Type

I must be missing something here, which wouldn't be surprising give my super-novice status.

Finally, thanks for spotting the typos and for getting me that paper you mentioned.

[Martin Rice]

Comment, thanks for the response. I don't know why I didn't think about actually structuring my database as a "real" double-entry accounting system, even though that's what I'm doing, in fact. Your response gave me a lot to think about and I appreciate it.

[LaurenKuhlman]

You caught me using Replace in my text editor.

Set Variable [ $fromAccountType; $fromAccountType ] and

Set Variable [ $toAccountType; $toAccountType ]

should be:

$fromAccountType; Budget::From Account Type and

$toAccountType; Budget2::To Account Type

Good catch

[LaurenKuhlman]

Roger's anchor buoy white paper attached.

Anchor_Buoy_White_Paper_v2_2.pdf

[Martin Rice]

Thanks so much, Lauren, I really appreciate it!