Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

[AlanMalawi]

Hi guys,
I�m designing a form based on a set containing 3 tables:
Tbl_Employees linked to Tbl_Loan_Master (Emp_ID) which is linked to Tbl_Loan_Trans (Loan_ID)

On the main body of the form is the �master� info relating to Employee and two browses containing the other tables.

I want to put a calculated field at the top of the form (Loan_Totals) which should through using a script scan through the Tbl_Loans and get the figures from each record relating to Emp_ID

To explain it better a little �pseudocode�

In VBA I would have done a simple :

do while (Tbl_Loan_Master) Emp_ID = (Tbl_ Employees) Emp_ID
For i =1 to eof
vTot = vTot+LoanAmts
next I etc


I need another calculated field in the Loan Browse area totalling all transactions relating to a particular loan

In VBA I would have done a simple :

do while (Tbl_Loan_Trans) LoanID = (Tbl_ Loan_Master) Loan_ID
For i =1 to eof
vTot = vTot+AmountPaid
next I etc

I suspect inn Alpha Five the Tablesum() function is the way to go. Maybe I�m being thick but no matter what combination I try I get errors.

In first instance I think it should read something like:
tablesum("Tbl_Loan_Master","Emp_ID� ="tbl_Employees.Emp_ID�,� Loan_Amts")
In second instance I think it should read something like:
tablesum("Tbl_Loan_Trans","Loan_ID� ="Tbl_Loan_Master","Loan_ID� Amount_Paid")
Would some be so kind as to tell me what I should be doing??

Alan

[glenschild]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi guys,
I�m designing a form based on a set containing 3 tables:
Tbl_Employees linked to Tbl_Loan_Master (Emp_ID) which is linked to Tbl_Loan_Trans (Loan_ID)

On the main body of the form is the �master� info relating to Employee and two browses containing the other tables.

I want to put a calculated field at the top of the form (Loan_Totals) which should through using a script scan through the Tbl_Loans and get the figures from each record relating to Emp_ID

To explain it better a little �pseudocode�

In VBA I would have done a simple :

do while (Tbl_Loan_Master) Emp_ID = (Tbl_ Employees) Emp_ID
For i =1 to eof
vTot = vTot+LoanAmts
next I etc


I need another calculated field in the Loan Browse area totalling all transactions relating to a particular loan

In VBA I would have done a simple :

do while (Tbl_Loan_Trans) LoanID = (Tbl_ Loan_Master) Loan_ID
For i =1 to eof
vTot = vTot+AmountPaid
next I etc

I suspect inn Alpha Five the Tablesum() function is the way to go. Maybe I�m being thick but no matter what combination I try I get errors.

In first instance I think it should read something like:
tablesum("Tbl_Loan_Master","Emp_ID� ="tbl_Employees.Emp_ID�,� Loan_Amts")
In second instance I think it should read something like:
tablesum("Tbl_Loan_Trans","Loan_ID� ="Tbl_Loan_Master","Loan_ID� Amount_Paid")
Would some be so kind as to tell me what I should be doing??

Alan

Hi Alan

As you are on a form a calculated field should do the trick, drag the new calculated filed object from the picker to the form and follow the genie.

Tablesum will also work but it looks like your code has too many " remove the two I have indicated in red and see if that works. Basically the function is expecting a character response.

[AlanMalawi]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi Glen, Thanks for real quick response.
What my big problem is, is that I dont understand the prompts when entering the tablesum() calc
Example function reads: tablesum("1�, "2", "3")
("1"). Should this be the table containing the field to be added or the table containing the field that needs to be matched (In my first instance - Loan_Master or Employee respectively
("2").What is format here? Do I simply put in field name or must it be full statement? (In my first instance, "Emp_ID" or something like "tbl_Employee.Emp_ID=tbl_Loan_master.Emp_ID"
("3"). This I gather is just the field to be summed. Is it referred to (in first instance, as just "LoanAmount" or as "tbl_Loan_Master.LoanAmount". Also I dont understand why a numeric field should be enclosed in "" ???

Thanks again for your help
Alan

[glenschild]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi Alan

This is fundamental to grasping the power of Alpha. See the attached screenshot.



When alpha recognises the function you are creating you see a pop up help balloon which tells you what is expected.

In this instance the help starts with "N" which tells you the return value will be numeric. The three components to this function: "tablename", "filter" and "Field" are all prefaced with "C" which tells you alpha is expecting the contents of this function to be character based and therefore wrapped in ""

eg: tablesum("tablename","filtervalue = 25","field to return value from")

There is a lot of good info in the help files about this.

Regards

[Tom Cone Jr]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Tips for writing expressions.

And, the data type of each parameter that must be passed to a function is shown in the help system, and in the auto-complete sequence. These are helpful reminders, but should not, themselves, appear in the parameters you actually use.

[Stan Mathews]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

I think it is covered in the link Tom provided but to emphasize....

Filter expressions (or criteria) in Alpha are character strings, no matter the data types being ccompared. Character strings begin and end with quote marks. If quote marks are necessary inside the string they can either be replaced with single quotes, or escaped double quotes (\"). Character strings can be built by concatenating multiple pieces (+). Everything inside the outer quotes is evaluated as literal. If you want to substitute a value for something in the character string you have to stop the expression, concatenate the variable containing the value, concatenate the remainder of the string.

The filter string in your tablesum

tablesum("Tbl_Loan_Master","Emp_ID” ="tbl_Employees.Emp_ID”,” Loan_Amts")

"Emp_ID” ="tbl_Employees.Emp_ID”

could be verbalized with a sample value as
Emp_ID = "EMP05A"

add outer quotes
"Emp_ID = "EMP05A""

we now have quotes within quotes to handle
"Emp_ID = 'EMP05A' "

so we know what the filter string should look like with a static value
replace the static value with the dynamic
"Emp_ID = 'tbl_Employees.Emp_ID' "

since the dynamic is hidden within quotes we have to stop and restart the string so Alpha knows we want the value it contains

"Emp_ID = ' "+tbl_Employees.Emp_ID+" ' "

This is fine for character values. Other accomodations are made if the dynamic value is of a different data type to convert that type to be included in the character string.

[AlanMalawi]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi Stan,
Thanks for making so much effort to help me - I do appreciate it. However when I type in the following line:
tablesum("loan_tran","Loan_ID"="'"+Loan_Master.Loan_ID+"'","Tranamt") ......

I get an error message saying "Argument is incorrect data type"
Tranamt is Numeric, others are character fields.
Thanks, Alan

What am I doing wrong?

[AlanMalawi]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi Glen
Thanks for this.
I have used the help but am still confused
tablesum("tablename","filtervalue = 25","field to return value from")
Is tablename in this case the Parent table containing the field loan_Id
In filter value - I want to total records in Child file (Loan_Tran) where the Child.Loan Id = Parent. Loan ID
"the field to return value from" I guess would be the Amount field inj the Child file
So am I correct in assuming it to be: Tablesum("Parent","child.LoanID="'"+"parent.LoanID+"'", "child.TranAmt" ????
Thanks for your help
Alan

[Stan Mathews]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

What am I doing wrong?

As I stated.

This is fine for character values.

Since a numeric value is not quoted ........

tablesum("loan_tran","Loan_ID= "+str(Loan_Master.Loan_ID),"Tranamt")
or taking advantage of Alpha's automatic type conversion for numerics during concatenation
tablesum("loan_tran","Loan_ID= "+Loan_Master.Loan_ID,"Tranamt")

Thanks Mike, yes it appears an extra quote snuck into the expression. I neglected to delete it when removing the quotes around the numeric variable. Now fixed.

[Mike Wilson]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi,
I'll chime in.

tablesum("loan_tran","Loan_ID[COLOR="#FF0000"][SIZE=5][B]"[/B][/SIZE][B][/B][/COLOR]= "+Loan_Master.Loan_ID,"Tranamt")

The quote following the table name in the filter should not be there.

[SNusa]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

.....When alpha recognises the function you are creating you see a pop up help balloon which tells you what is expected......

Frequently it fails to recognize though.... When it does, you can use the X-basic Explorer. (XbE)

When you don't see "bubble help", try dragging and dropping what you need from: Objects-->Windows-->Form branch.
If this doesn't work (still no bubble help), look for the same "drag & drop" from: Objects-->System-->A5 branch.
(And after using the bubble help, you can make it look just like the first drag and drop by removing the a5 prefix etc.)

Here's another link that might help re: optional ways to do what you want: http://msgboard.alphasoftware.com/al...m()-vs-DBsum()

And another: http://www.learn alpha.com/QuerySynt...axGlossary.htm
And another: http://msgboard.alphasoftware.com/al...L_EXPRESSION() (aka Code indirection)

[AlanMalawi]

Re: Tablesum() - Multi tables - I dont understand criteria (Alpha Five v11)

Hi,

Thanks so much for all the input.

I'm probably being dumb but I'm finding I get inconsistent results when working with different forms.
I am mainly working around this by using the built in total facility that pops up when you drag a field accross onto the form.

Thanks to ALL of you for being so patient.

Alan