Announcement

**Selwyn Rabins** · 01-17-2012, 02:23 PM

Re: How to create a calculated field based on items already in the current table?

the grid component allows you to automatically display totals for any numeric column in the grid.
it is just a checkbox. does that not meet your needs?

in the case of .dbf tables, to compute the sum of records in a table, you could use the tablesum() function. for example:

?tablesum("invoice_items","recno() < 5","quantity")

**nlights** · 01-17-2012, 02:32 PM

Re: How to create a calculated field based on items already in the current table?

Thank you for your response, Selwyn (and thanks for the various well produced videos!) In my case column totals don't do the trick. What I need is ideally a calculated/virtual field within the data table itself, that would -- again, ideally -- run a sum of snack quantities for any given snack on-the-fly. Maybe tablesum will do it, I'll check it out. I'll just have to figure out how to insert it into the DBF structure properly...

Speaking of videos, would you happen to have the "rs" CSS class that you mentioned in your scrolling dialog repeating section -video available for download?

**Selwyn Rabins** · 01-17-2012, 02:48 PM

Re: How to create a calculated field based on items already in the current table?

i don't know which video you are referring to, but perhaps this is the CSS that you are referring to:

Code:

-moz-border-radius: 4px;
-webkit-border-radius: 4px;
border-radius: 4px;
border-style: solid;
border-color:#c0c0c0;
border-width:1px;
padding: 2px;
-moz-border-radius: 5px;
border-radius: 5px;
-moz-box-shadow: 5px 5px 5px silver
-webkit-box-shadow: 5px 5px 5px silver;
box-shadow: 5px 5px 5px silver;

**nlights** · 01-17-2012, 02:54 PM

Re: How to create a calculated field based on items already in the current table?

So, if I create a calculated field into my table, I could use tablesum as follows:

xBasic -function (pseudo code):

CalcRequiredTotal(ID)

Return tablesum("eventitems","EI_ITEMID=ID","EI_RequiredQty")

That would probably do the trick, no? I suppose this could affect performance rather significantly. Does A5 use indexes to speed up queries like this automatically, a la Foxpro? If not, would you have any other performance tricks for this type of situation?

Finally, if I may, how do I go about writing an xBase function and then calling it from the table structure? A pointer to a video or wiki entry would suffice, if this is too lengthy of an explanation.

Thanks again!

**Selwyn Rabins** · 01-17-2012, 02:55 PM

Re: How to create a calculated field based on items already in the current table?

just so i am sure i understand. you have rows of data:

Code:

row1
row2
row3

for each row, you want to compute a summary value, so you have something like this in the grid:

Code:

row1   23
row2 34
row3 57

there are probably many different ways to approach this problem, but the one that I think is very flexible would be to

1. create a dummy field in your table ( you could just create a calc field that has as value set to a constant of '1')
2. add the dummy field to your grid.
3. set the control type of dummy field to 'custom'

this then allows you to execute any xbasic you want to compute the html for the 'custom' control as each row is rendered.

if you use the new video finder feature in the latest update (help/video finder), then search on 'custom control' you will see video 45 describes how to use a custom control.

custom controls give you a very powerful technique for adding custom calculations to the grid.

**nlights** · 01-17-2012, 02:56 PM

Re: How to create a calculated field based on items already in the current table?

The video I referred to is: "D13 Dialog Component - Repeating Sections - Scrollable Containers"

**nlights** · 01-17-2012, 04:45 PM

Re: How to create a calculated field based on items already in the current table?

Tried custom control, but didn't get far. Here's my custom control code (relevant snippet):
'itemsum = str(tablesum("eventitems.dbf","EI_ITEMID="+data.EI_ITEMID,"EI_PledgedQty"))
itemsum = str(Data.Ei_itemid)
Ei_dummy_render = <<%html%
<%a5?itemsum%>
%html%

I first tried running tablesum(), but it gave the same result for EVERY row. I then commented that one out and simply passed data.EI_ITEMID to see if it varies from record to record. But it doesn't! Each grid row gives me the last EI_ITEMID in the grid. Ahhhh.....

**Selwyn Rabins** · 01-17-2012, 05:05 PM

Re: How to create a calculated field based on items already in the current table?

i think you still have a big conceptual gap here.
it does not look to me like you have any experience yet with writing xbasic functions.

here is a more detailed explanation:

1. the custom control calls a function that returns html to display in the grid.
so, if your field is called field1, the function that gets called to render the control is:
Field1_render()

2. when you click the smartfield for the custom control definition, the builder shows you a function prototype.
so you will see:

Code:

function field1_render as c (.....)

end function

3. in xbasic, the way that a function returns a value is by setting the function name to a the value.
so, in the function prototype you see:

Code:

field1_render = <<%html%
some value
%html%

(contrast this with - say - javascript where a function return value is specified by saying: return some value)

so, knowing how xbasic functions work and how they return values is a necessary pre-requisite to using custom controls.

4. you do NOT use <%a5 code blocks here.

say you wanted your custom control to return today's date. your event handler for the custom control would be this:
(simplified by removing all error handling code, and 'with' statement)

Code:

function COMPANY_render as c (data as p, args as p, PageVars as p)
COMPANY_render = "Today's date is: " + date()
end function

but you want to return something more complex than today's date. so you might write code like this:

Code:

function COMPANY_render as c (data as p, args as p, PageVars as p)

dim summaryValue as n 
summaryValue = tablesum("[PathAlias.ADB_Path]\mytable.dbf",.........)
COMPANY_render = "Summary value is: " + summaryValue
end function

**nlights** · 01-17-2012, 05:53 PM

Re: How to create a calculated field based on items already in the current table?

Thank you for the clarification on how xBasic functions work. I've done a lot of Foxpro and C# development, but Basic is completely new to me, not to mention xBasic. I bought A5 thinking that it would do a lot of stuff for me without any coding, but looks like I am going pretty deep pretty quickly and simply setting property sheet values is not enough for my purposes. I appreciate your patience, though.

Is there a good manual for learning this, or do I have to plough through the not-so-friendly or easy-to-use Wiki? The videos are nice, but not exactly "random access" for getting to some specific question quickly.

However, I followed the custom control instructions, and this is how it looks in its entirety (see below.) It seems to tell me to return HTML, and that's what I did. Am I missing something major here?

Code:

function Ei_dummy_render as c (data as p, args as p, PageVars as p) 'do not delete this line
with PageVars	'do not delete this line
'Specify the Xbasic that returns the HTML for this control. 
'This function must return Ei_dummy_render, which should
'contain the HTML that will be displayed.
'The 'data' argument that is passed in contains the current 
'row value for each of the fields in the Grid. For example
'if the Grid displays the customerid, firstname and lastname
'fields, you can reference the values in these fields as
'data.customerid, data.firstname and data.lastname
'JAVASCRIPT: Advanced custom controls might also need to execute
'some javascript. To include javascript with your custom control, set
'the args.javascript property.
'TIP: In the case of Grids based on SQL tables, the current open connection 
'can be accessed as: args.rtc.cn  (when using this connection, be sure not to close it)
'

'-------------------------------------------------------------------
on error goto Ei_dummy_xbasicError 							'do not delete this line

'Write Xbasic code here that returns the HTML to be displayed by this control
'itemsum = str(tablesum("eventitems.dbf","EI_ITEMID="+data.EI_ITEMID,"EI_PledgedQty"))

'commented the previous line and added the line below for testing purposes: does this function get a "dynamic" ID or just the last ID repeatedly.  The latter seems to be the case

itemsum = str(Data.Ei_itemid)
Ei_dummy_render = <<%html%
<%a5?itemsum%>
%html%

end  												'do not delete this line
Ei_dummy_xbasicError: 										'do not delete this line
Ei_dummy_render = "Error in custom control xbasic: " + error_text_get() 'do not delete this line
end with 'do not delete this line
end function 'do not delete this line

**nlights** · 01-17-2012, 06:03 PM

Re: How to create a calculated field based on items already in the current table?

OK, based on some debugging I just did, it appears that custom control does NOT get data from the database "dynamically." Case in point, my custom control code (in my previous message) always returns "2", which happens to be the last ID in the current browse window. HOWEVER, I created a calculated field that calls an xBasic function I just created, and things work great now.

Here's the calculated field: z=test(EI_ITEMID)

Here's the simple function:

Code:

FUNCTION Test AS N (ID AS N )
	'Test = tablesum("eventitems.dbf","EI_ITEMID="+ID,"EI_PledgedQty")
        'commented previous line and added the following line to test what ID is returned to each grid row.  ID's are correct, tablesum() now works as expected!
        Test = ID
END FUNCTION

So, this finally answers my question (but leaves me wondering about the custom controls.) Thank you for your help!

**WendyWelton** · 01-17-2012, 06:08 PM

Re: How to create a calculated field based on items already in the current table?

Originally posted by nlights View Post

Is there a good manual for learning this, or do I have to plough through the not-so-friendly or easy-to-use Wiki? The videos are nice, but not exactly "random access" for getting to some specific question quickly.

I struggled for a long time to get oriented with XBasic. I just finished Steve Working's new videos - http://www.imakenews.com/alphasoftwa...x000546115.cfm and I highly recommend them. They have excellent clear examples, but also set the ground work first for how & where to use XBasic vs Javascript, etc., and they come with working applications as examples. I wouldn't say I've "got it" yet, because I just finished them. But I finally feel like I'm on my way.

If you bought the A5 subscription, you would have gotten them at no cost. If not, imho, they're worth the price.

**nlights** · 01-17-2012, 07:46 PM

Re: How to create a calculated field based on items already in the current table?

Thanks for the tip, Wendy, maybe I'll check it out. Call me old fashioned, but I like to have an actual written reference chm help file that is easy to search, gives a lot of examples, and contains tutorials. .NET help system is a great example of what I'm talking about (although it's gone way beyond CHM.) I'm surprised that A5 doesn't have decent documentation whatsoever -- the Wiki is ok, but it's user interface is awful (IMHO.) The problem with videos is that they are next to impossible to search when all you need is the 10 second clip within the 4 minute video that explains the thing you are looking for (and good luck trying to bookmark it for future reference...)

**WendyWelton** · 01-17-2012, 08:17 PM

Re: How to create a calculated field based on items already in the current table?

Originally posted by nlights View Post

Thanks for the tip, Wendy, maybe I'll check it out. Call me old fashioned, but I like to have an actual written reference chm help file that is easy to search, gives a lot of examples, and contains tutorials. .NET help system is a great example of what I'm talking about (although it's gone way beyond CHM.) I'm surprised that A5 doesn't have decent documentation whatsoever -- the Wiki is ok, but it's user interface is awful (IMHO.) The problem with videos is that they are next to impossible to search when all you need is the 10 second clip within the 4 minute video that explains the thing you are looking for (and good luck trying to bookmark it for future reference...)

Steve's videos are short and well named.

Also check out the new video finder in the newest update (top right of the web control panel) and the new Wiki Search Assistant (under Help on the A5 menu). I know exactly what you mean about finding things in the wiki. These two new things are making a huge difference for me.

**WendyWelton** · 01-17-2012, 08:19 PM

Re: How to create a calculated field based on items already in the current table?

Originally posted by nlights View Post

when all you need is the 10 second clip within the 4 minute video that explains the thing you are looking for (and good luck trying to bookmark it for future reference...)

Another tip - Jing is a free download, and has a good screen snip tool that you can annotate. When I'm watching videos, when I see something I know I'll need, I grab a screen shot, toss some notes on it, and paste it into a Word doc - make my own little cheat sheet/reminder thingy.

Announcement

The Alpha Software Forum Participation Guidelines

How to create a calculated field based on items already in the current table?

How to create a calculated field based on items already in the current table?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment