Caclulated Fields in a Dialog

[meh8610]

Ok, this is what I'm trying to do. I have a textbox field that you enter an account number in. What I want to do is have the person enter the account number, press 'submit' and then then have three fields populated with the following information: one with a total budget amount from a table called Bduget (based off the account number entered previously); a second with the total amount of expenses from a table called Expenses (from the same account); and then a third that subtracts the expense amount from the budget amount. I have watched the video on how to populate the dialog from a button, but the data I'm using is a DBF file and there's more than one record of the account number. How would I go about accomplishing this? Thanks!

[GGandhi]

Re: Caclulated Fields in a Dialog

hello
you will need to add two look up and fill in actions for the onClick event for the button and the for the balance use calculated field expression
that should work.

[meh8610]

Re: Caclulated Fields in a Dialog

How do I calculate the totals for the budget and expense fields? The total budget and expense amounts are not already calculated. They would need to be calculated when the user submitted the request for the account information. Thanks!!

[GGandhi]

Re: Caclulated Fields in a Dialog

please post al the fields needed and indicate the ones that are from a table and the ones calculated using word document
that will help

[meh8610]

Re: Caclulated Fields in a Dialog

Let me know if this is what you need.

Thanks!

AccountEntryDocumentation.doc

[GGandhi]

Re: Caclulated Fields in a Dialog

this is already given at the post #1. what you need to provide is the individual tables and sample data. for example

budget table:

acct #	name	budget
101	gg	100.00
102	ss	200.00
103	ff	300.00

expense table
acct# desc	expense
101	meal	50.00
102	car	50.00
101	gas	25.00
103	smoke	55.00
102	clean	40.00
103	meal	100.00

something like that and meaningful to us to relate. so that it will be helpful.

[meh8610]

Re: Caclulated Fields in a Dialog

Ok, I've updated the word doc. Thanks for clarifying.

AccountEntryDocumentation.doc

[madtowng]

Re: Caclulated Fields in a Dialog

have you considered using the tablesum() function ?

EX:
lkupbudget = tablesum("budget-table","acct-id = " + e.datasubmitted.acct-id,"budgeted")
lkupexpense = tablesum("expense-table","acct-id = " + e.datasubmitted.acct-id,"expense")
clcactual = lkupbudget - lkupexpense
e._set.budgetfield.value = lkupbudget
e._set.expensefield.value = lkupexpense
e._set.clcfield = clcactual

You might need to do some convert_type's to make this work, but I think it should work.

[meh8610]

Re: Caclulated Fields in a Dialog

Ok, so I've tried that code as you suggested, but I'm receiving an error. I've attached a word doc with pictures of the error message and my current code. I'm not quite sure what I'm doing wrong. Thank you so much for your help!!

ErrorMessages1.doc

[meh8610]

Re: Caclulated Fields in a Dialog

Ok, nevermind. I figured out what I was doing wrong up above. But now my problem is that it isn't adding up anything. When I submit, it just returns a 0.

Here is my updated code:

budgetTotal = tablesum("budget.dbf","Mst_acct = " + e.dataSubmitted.accountNumber,"Amount")
expenseTotal = tablesum("expense.dbf","Mst_acct = " + e.dataSubmitted.accountNumber,"Commit_amt")
calculateTotal = budgetTotal - expenseTotal

e._set.budget.value = budgetTotal
e._set.expense.value = expenseTotal
e._set.difference.value = calculateTotal

Thank you again!

[madtowng]

Re: Caclulated Fields in a Dialog

Please show the code.

[meh8610]

Re: Caclulated Fields in a Dialog

Here is my updated code:

budgetTotal = tablesum("budget.dbf","Mst_acct = " + e.dataSubmitted.accountNumber,"Amount")
expenseTotal = tablesum("expense.dbf","Mst_acct = " + e.dataSubmitted.accountNumber,"Commit_amt")
calculateTotal = budgetTotal - expenseTotal

e._set.budget.value = budgetTotal
e._set.expense.value = expenseTotal
e._set.difference.value = calculateTotal

Here is what I have

[madtowng]

Re: Caclulated Fields in a Dialog

using the debug(1) option, what values show for the first 3 lines after they are executed?

[meh8610]

Re: Caclulated Fields in a Dialog

I think it shows all the values to be 0, but I'm not sure if I'm doing it correctly. Here is a screen shot:

ErrorMessages1.doc

[madtowng]

Re: Caclulated Fields in a Dialog

Click on the step button until you get to line 9.
Let us know the results.