expression to create a database field

[john linley]

I Am New Convert To Alpha5 From Alpha4. Having Used Alpha4 For About Fifteen Years (a4v4 Through A4v8)

In A4, A Parent Db Linked To A Child Db In A Set, Can Summarize A Num Field In Child Database Or A Numeric Summary Can Also Appear As A Logical Field. I Used Both Under Different Circumstances. The Example In Alpha Sports Seems To Be A Logical Field Only.

In Alpha5v8 I Have Structured A Payroll Set, Linking "employee" As Parent To A Child Table "payroll".
A Field In Parent (employee) Has A Num Field " Salary_total",
A Corresponding Field In Child Db Is Named "gross_salary".

I Need To Summarize "gross_salary" And Have The Parent Num Field "salary_total" Collect Or Capture The Data In That Particular Field. I Searched For Specific Help On This But Have Not Been Able To Find It.

Also In This "payroll" Table, I Need Once A Week To Update Two Fields..
The First Is A "pay_date" Date Field And The Second Is A character field "check_nbr" . Both Require User Input..
In Alpha4, I Created 2 Scripts That Used The "waitkey" Command Allowing User To Insert A Date In One Script And A Starting Check Number In The Second Script.
In Alpha5 I Have Played With Both Of These As Buttons On The Weekly Payroll Form.
The Date Works If I Use The Expression {date + 7}, But I Am Unhappy With This Clumsy Solution.

In Alpha5, Can These Be Combined In One Expression And Be Processed At The Same Time??

Would Greatly Appreciate Resolving These Matters.

John Linley

[Stan Mathews]

I Need To Summarize "gross_salary" And Have The Parent Num Field "salary_total" Collect Or Capture The Data In That Particular Field.
This can be done with a tablesum() expression.
Also In This "payroll" Table, I Need Once A Week To Update Two Fields..
The First Is A "pay_date" Date Field And The Second Is A character field "check_nbr" . Both Require User Input.. I would suggest having two variables defined for a form where the user did the input. Then an update operation would reference the values in the variables.

[john linley]

to: stan mathews. Thanks for the very fast response. I also am an early riser.
I will give them both a shot and hope I can succeed. Many years ago - 1950-1952 I was stationed at Fort Campbell. I guess it is still there.
Regards and thanks,

John Linley

[john linley]

to: Stan Mathews

Much appreciate your AM response. I also am an early riser.

Will give both a try later today. Years ago, 1951-1953, I was stationed at Fort Campbell, I guess it is still operational.

Thanks again, I will let you know how I make out.

John Linley

[john linley]

to: stan mathews

Dear Stan. Updating check numbers and dates working quite well now.

Many thanks for your help.

Unfortunately, I am still having trouble with TABLESUM() FUNCTION !

Alpha5 help gives the following expressions as an example.

? TABLESUM(PURCHASE.DBF", ".T.", "COST") = 27123.10000

I re-wrote the expression as follows:

VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

I REPLACED THE ? WITH PARENT NAME AND FIELD
I REPLACED THE FILTER ".T." WITH THE SUPPLIER CODE FIELD

THE RESULT WHEN I PLACE THIS CALC FIELD EXPRESSION IN THE PROPERTIES BOX, I DO NOT GET A SUMMARY BY VENDOR, I GET THE NAME OF THE EXPRESSION ITSELF.
WHEN I LEFT THE FILTER ".T." IN AT FIRST, I GOT THE TOTAL FOR THE ENTIRE DATABASE, AND NOT FOR AN INDIVIDUAL VENDOR, WHICH I NEED.

I'M OBVIOUSLY DOING SOMETHING WRONG!! BUT WHAT ?

JOHN

[Stan Mathews]

I am not clear what you are attempting with

VENDORS->PURCH_TOT.DBF =

You cannot assign the result of an expression to "VENDORS->PURCH_TOT.DBF".

The example in the documentation uses a static logical value in the filter portion and it is quoted. If you use a dynamic value you must supply a filter to return a logical value dynamically. From your example

TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

it seems what you wanted was

TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE = "+quote(SUP_CODE),"AMOUNT") = ?????

"SUP_CODE = "+quote(SUP_CODE)

must resolve to a logical value. You can experiment with this in the interactive editor until you get it right.


If SUP_CODE is a character field. The first instance of SUP_CODE refers to the SUP_CODE in the PURCH-PAYMENTS table. The second instance of SUP_CODE refers to the current value of the SUP_CODE field in the PURCH_TOT table. Breaking out of the quoted filter portion and concatenating (+quote(SUP_CODE)) tells Alpha to use the current contents of the field.

Please also note that "PURCH-PAYMENTS" does not conform to recommended table naming conventions.

Table and Field Names

Alpha Five recommends that table names, field names, and paths to your files start with a letter (A-Z or a-z) and be composed of letters, numbers (0-9), and underscore (_) characters.

No mention of dashes there.

[john linley]

to: Stan Mathews;

Good evening Stan;

The aforementioned purch-paymnts was a typo error. I should have typed
purch_paymnts with the appropriate underscore. Sorry for the error and inconvenience. I will try and edit my messages more carefully.

Still having trouble with tablesum() function!! After repeated attempts and many variations I still, unfortunately, need help.

LET ME GO TO A DIFFERENT SET!

The set name is "INVOICING" with the parent table named "INVC_HDR" and a child table named "ITEMS_INVCD"

The linking field is "INV_NO" The child index is "LI_INV_NO"

A numeric field in the parent table is "INVC_TOT"

A CALCULATED numeric field {QTY*PRICE} in the child table is "ITEM_EXTENS"

I WROTE THE FOLLOWING EXPRESSION IN THE CALCULATED FIELDS BOX; IS THERE SOME OTHER PLACE TO WRITE THIS CODE? AS SOON AS I CLICK ON ok, AND RE-OPEN THE XY CALCULATED FIELDS BOX, THE EXPRESSION I JUST WROTE IS NOT THERE??? ANYWAY, THIS IS THE EXPRESSION...


TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = +(QUOTE(LI_INV_NO) ,
"INVC_TOT") = 100000.20


This expression, when I finally get it right, is supposed to summarize
multiple invoiced line items in the field "ITEM_EXTENS" and place that summary value in the parent field "INVC_TOT"

THE FIELD LENGTH OF THE NUMERIC FIELD "INVC_TOT" IS 9 WITH 2 DECIMALS. A SINGLE INVOICE CAN EXCEED 100,000.00

That is why I put such a large number after the = sign. I don"t know if this arbitrary number can be shorter than the field length.


Other tables in this set are a CUSTOMER.DBF and an INVENTORY.DBF table.
Customer is linked to the parent by a customer code field and the inventory table is linked to the ITEMS_INVCD table by a part number field. Both are one to one links.

Regards, and thanks for your help and patience.

john linley

[Stan Mathews]

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
"INVC_TOT") = 100000.20

Would yield a logical T/F result.... Is the result of the tablesum() = 100000.20?

The set name is "INVOICING" with the parent table named "INVC_HDR" and a child table named "ITEMS_INVCD"

The linking field is "INV_NO" The child index is "LI_INV_NO"

A numeric field in the parent table is "INVC_TOT"

A CALCULATED numeric field {QTY*PRICE} in the child table is "ITEM_EXTENS"

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
"INVC_TOT")

I think you want

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
"ITEM_EXTENS")

Sum the values in the ITEM_EXTENS field in the ITEMS_INVCD.DBF where the value in the LI_INV_NO field in the ITEMS_INVCD.DBF matches the value in the INV_NO in the INVC_HDR table.

I WROTE THE FOLLOWING EXPRESSION IN THE CALCULATED FIELDS BOX; IS THERE SOME OTHER PLACE TO WRITE THIS CODE? AS SOON AS I CLICK ON ok, AND RE-OPEN THE XY CALCULATED FIELDS BOX, THE EXPRESSION I JUST WROTE IS NOT THERE??? ANYWAY, THIS IS THE EXPRESSION...

Not sure what you mean. You need to define the INVC_TOT as calculated in the parent table field rules and give it the expression

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
"ITEM_EXTENS")

[john linley]

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(LI_INV_NO) ,
"INVC_TOT") = 100000.20

Would yield a logical T/F result.... Is the result of the tablesum() = 100000.20?



I think you want

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
"ITEM_EXTENS")

Sum the values in the ITEM_EXTENS field in the ITEMS_INVCD.DBF where the value in the LI_INV_NO field in the ITEMS_INVCD.DBF matches the value in the INV_NO in the INVC_HDR table.

Not sure what you mean. You need to define the INVC_TOT as calculated in the parent table field rules and give it the expression

TABLESUM ("ITEMS_INVCD.DBF" , "LI_INV_NO = "+QUOTE(INV_NO) ,
"ITEM_EXTENS")

[john linley]

To: Stan Mathews

Stan; I did reply via Email, but apparently it was not the right thing to do. I have to assume you never received it. To summarize, re: the lost taskbar,
Your suggestion to uninstall Alpha5 and re-install solved the problem. Alpha5 is functioning properly now. I also had to unistall alpha4 and re-install. It seems that when my workstation had a hard drive failure, it affected both alpha4 and alpha5. I was in A4 at the time of the crash.
Alpha4 is in a nine station network on drive "H" ; alpha5 is on drive "C" on my workstation only. My workstation hard drive failure affected both A4 and A5 and only on my work station. Alpha 4 continued to work smoothly in the other 8 workstations.

Sometime, in the near future, when I am ready to switch from alpha4 to alpha5, I will need to increase my license count by eight and network A5. I may need both A4 and A5 to run simultaneously for perhaps two or three months. I suspect A4 and A5 need to be in different networks. Is this correct??

My goal is to be ready by Jan 1, 2008.

Re: the tablesum() . I was trying, as you pointed out, to place that expression in the wrong place. The "set field rules" is the place, not the "form calculated fields". In A4, summarizing a child table is also a set field rule. It now seems foolish of me not to realize this sooner. Your thumbnail finally cleared that problem for me. Fortunately, there are many similarities between A4 and A5. Where this is the case, I make progress more quickly than when I get into strange waters, where procedures require a different approach.

Thanks again for your help. You are keeping my self-inflicted jan 1 deadline somewhat realistic.

On another and perhaps last issue. I would like to prevail upon you for some
assistance for the following issue.

In an 'accounts_receivable.set' ; a 'customer.dbf' is linked to a "sales.dbf" on a "cust_id" field.

We also join in a set named 'collections.set' a 'deposit.dbf'' to the same 'sales.dbf'. The linking field here is: "check_nbr". In both sets, 'sales.dbf' is the child.

In the 'collection.set' manual entry in the header->deposit.dbf might be as follows:

date field deposit_date 09/15/2007
char field 6 cust_id ABC
char field 7 chk_nbr 4567
num field 9 - 2 chk_amnt 100000.00

That check amount usually pays at least 100 invoices (records) in the 'sales.dbf' The invoice numbers are listed on the check stub in sequential order but has some skipped invoice numbers. As an example, if customer is paying invoices 3000 to 3120, there usually are several skipped numbers between 3000 and 3015. Also there usually are skipped numbers between 3100 and 3120. Between, there might be 80 or 90 sequential invoices paid without skipped numbers.

The 'sales.dbf' already has the cust_id value "ABC" and the invoice numbers which is also already filled perhaps 1000 to 4000. And each record also has
an 'invoice_amount' field which also contains values.

The 'sales.dbf' fields that need to be filled are:

date field payment date same as above
char field 7 check_nbr 4567
char field 1 paid_y_n "P"
num field 9 - 2 applied_payment (same value as
'invoice_amount' field)

In A4 - we globally update the 'sales.dbf-> check_nbr' field with the value 4567 into as many unskipped sequential fields as possible, then, manually update the remaining skipped fields. With this information in place , we can then - in one pass - globally update the remaining 3 fields.

I suppose, we can do the same in Alpha5, but, is there a better or easier way than this to accomplish the same task ???

Hope you have a better idea on this.

Regards and many, many thanks;

John Linley

[Tom Cone Jr]

John, over the years I've learned that the message board works best if each thread is limited to a single question or situation. Folks with similar questions can find and follow the threads much more easily later on. Taking a thread off topic with a new question also defeats the automatic search for similar threads that happens each time the thread is viewed, since that's based on the original topic subject heading. It's not my intention to be critical here, just asking (on behalf of others who will come along later) that you consider this next time.

[john linley]

To: Tom Cone, Jr.

Thank you for the advice. It does make sense, and I will certainly heed it in the future. I am not only a new convert from Alpha4 to Alpha 5, but I am also a new user in the internet as well as the Alpha Messgae Board, which, thanks to more advanced and competent users I have been helped a great deal.

I will try to open a new post (thread?) on this different topic. It seems as though I have too many personal notes entwined into my replies, such as this one. Can I remove them without destoying the more pertinent question and answer.?
Thanks again, John Linley

[john linley]

to: stan mathews

Dear Stan. Updating check numbers and dates working quite well now.

Many thanks for your help.

Unfortunately, I am still having trouble with TABLESUM() FUNCTION !

Alpha5 help gives the following expressions as an example.

? TABLESUM(PURCHASE.DBF", ".T.", "COST") = 27123.10000

I re-wrote the expression as follows:

VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

I REPLACED THE ? WITH PARENT NAME AND FIELD
I REPLACED THE FILTER ".T." WITH THE SUPPLIER CODE FIELD

THE RESULT WHEN I PLACE THIS CALC FIELD EXPRESSION IN THE PROPERTIES BOX, I DO NOT GET A SUMMARY BY VENDOR, I GET THE NAME OF THE EXPRESSION ITSELF.
WHEN I LEFT THE FILTER ".T." IN AT FIRST, I GOT THE TOTAL FOR THE ENTIRE DATABASE, AND NOT FOR AN INDIVIDUAL VENDOR, WHICH I NEED.

I'M OBVIOUSLY DOING SOMETHING WRONG!! BUT WHAT ?

JOHN

[Al Buchholz]

From the help file:

tablesum()

Searches the specified Lookup_Table for one or more records that satisfy the specified Filter, and returns the sum of the values contained in the Lookup_Expression.

The Filter must return a logical value, either True (.T.) or False (.F.). For example, to choose all of the records in a table, use the logical constant ".T." as a filter.

To choose only the records where the STATE field is equal to TN, use the filter "STATE = 'TN'". Note that the entire filter is always in quotations, and the character value, TN, is in single quotes.

[Stan Mathews]

VENDORS->PURCH_TOT.DBF = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE","AMOUNT") = 100000.20

Your expression seems to be trying to set VENDORS->PURCH_TOT.DBF to some value? That doesn't make any sense to me.

You don't specify where you are trying to create the calculated field expression so let's assume it is on a report or form.

You do this by giving the calculated field a name and equating it to an expression. Since you indicated that sup_code would define the vendor and I guess that sup_code is a character field, I think you want something like

v_total = TABLESUM( "PURCH-PAYMENTS.DBF", "SUP_CODE = "+quote(sup_code.value),"AMOUNT")

This assumes that the sup_code appears on the report or form and that it has the object name sup_code. One determines the object name by examining its properties (right click on it).

PS The dash/minus sign in PURCH-PAYMENTS.DBF does not follow recommended naming guidelines. You may be able to get by for a while but it has been known to be the source of hard to diagnose problems later.

[john linley]

ATTN: STAN MATHEWS... FROM: JOHN LINLEY
A question re: TABLESUM() FUNCTION!

IN A PARTICULAR SET, ON ENTERING NEW DATA, THE PARENT FIELD SUMMARIZING THE CHILD NUMERICAL DATA DOES NOT CHANGE AS NEW DATA IS ENTERED. HOWEVER, IF I GO TO THE CHILD TABLE AND RE-EVALUATE FIELD RULES AND THEN RETURN TO THE SET; THE SUMMARIZED PARENT DATA WILL NOW REFLECT THE NEW ENTRIES. IN ALPHA4, ALL PARENT SUMMARY FIELDS REFLECT NEW ENTRIES AS ENTERED. IS THERE A CURE FOR THIS ?? I AM ASSUMING I HAVE MISSED SOMETHING IN THE EXECUTION OF THE TABLESUM() FUNCTION.

REGARDS, JOHN LINLEY

[Tom Cone Jr]

John, it seems that you have created a calculated expression field rule for a field in your parent table. The expression uses tablesum() to generate a total from the linked records in the child table. The calc field expression will be re-evaluated each time you save a change to the parent table record, not each time you save a change (or enter new records) to the child table. Unlike Alpha Four, Alpha Five does not have field rules for the "set" per se. All field rules are saved to the table. A set cannot have field rules.

I'm familiar with two different ways to keep an "automatic" running total in a parent record, where the total sums all the linked child table records.

1) after establishing the initial summary total using a saved update operation, you can use a post field rule in the child table to add transaction table (child table) field values to the summary total field as each transaction record is changed or entered.

2) you can use events in the data entry form layout to run a new total and assign the new total to the suummary total field.

In many applications keeping a running total in the parent table is unnecessary. The total can be computed when needed. The total can be displayed on screen in the form layout or included in a report without having the computed value actually stored in the parent table.

As a newcomer to the message board permit me a personal observation. The use of ALL CAP LETTERS is usually reserved to signal a loud voice or shouting. For most all cap text is a bit harder to read. There are exceptions (the visually handicapped come to mind), but I thought I'd mention this in case you weren't aware of it.

[john linley]

to: Tom Cone Jr ... from john linley:

I much appreciate your quick response. I like both of your suggestions for a tablesum()
replacement. I think I will try an "event action" on some forms and a posting field rule from child to parent on others. It is just a habit of mind to use all caps, but I will curb this habit in the future.

Thanks again.

[john linley]

To Stan Mathews

Sorry to bring this up again, but, if I may..

I wrote the following calc field rule in a set acc/receivable. I am attempting to summarize a child field "ord_tot" N-8-2 (for all records per customer) to a field in the parent table "YTd SALES" N-9-2.

tablesum( "sales_bl" , "cust_id= " +quote(cl_cust_id) , "ord_tot" )

cust_id is the linking index between parent and child and also a child index name.
cl_cust_id is a parent index name.

Switching indexes in the expression also did not work. Would much appreciate your help.

john linley

[Stan Mathews]

Indexes have nothing to do with a tablesum expression.


tablesum( "sales_bl" , "childfieldname = " +quote(parentfieldname) , "ord_tot" )


where childfieldname is the name of the customer id field in the child table and parentfieldname is the name of the customer id field in the parent table. This would be the expression to sum the ord_tot field in the sales_bl table for records where the value in the customer id field in the child table match the value in the customer id field in the current record in the parent table.

[john linley]

Good evening Stan;

When I read your version of the expression with accompanied, most clear, explanation. I thought, at long last I am beginning to understand this (tablesum) thing.

So, I went to the set, "acc Receivable" which links the two tables "customer list" and "sales_bl" and I entered at the field name, "YTD SALES" in the "customer list" table
the following calculated field rule expression:

tablesum("sales_bl","cust_id = " +quote(Cust_id),"ord_tot")

The "Cust_id" field is identical in name, type and length (char-7) in both tables and is the linking field in the set.

However, it didn"t work. The error message was "not a valid expression".

A posting field rule also requires matching fields, so this makes the utmost sense.
Could I be placing this calculated field expression in the wrong place?

Going nuts again, John

[Stan Mathews]

Well, I'm confused here. You mention a posting field rule and a calculated field. These are mutually exclusive. You cannot post to a calculated field.

I am attaching a small sample database in which the ord_tot field in the sales_bl table is posted to the ytd_sales field in the customer list table. This has nothing to do with a tablesum() expression. The amount in the ord_tot field for each record is added to the ytd_sales field in the customer list table. This is what posting is design to do.

If you define the ytd_sales field to be calculated with the tablesum() expression`you have to force the recalculation of the field so that the amounts in any new records to show up in the ytd_sales. A calculation such as this is really more appropriate to a form or report calculated field where you want to show the latest total.

This begets the question, why do you want to store the ytd_sales total when you can easily calculate it at any time?

[john linley]

Stan:

Last night, I thought I sent another message. The content of the message was,
After reading your explanation of the tablesum function, I tried it elsewhere, and it worked correctly the first time I wrote the expression. I do believe, that now, I can use this function as and when needed without any further bother.
I also added that your patience is admirable, your assistance is invaluable, and I am much much appreciative. You and others have been most helpful.

I wonder where that othr reply went? I must have attached it in the wrong place.

Well, thanks again, John

[Stan Mathews]

I wonder where that othr reply went?

Lost in cyberspace, I suppose. Glad you got to where you needed to be.