Calculated field

[Richard Froncek]

This was very simple in Alpha4; should be just as simple in A5v4 build266. It's not a walk in the sun, since I've been at it for what seems ages. This is a situation that pleads for example as in the other discussion group. Situation is simple; solution should be too.
Problem: I have a field for weekly average. However, I can't results to it. Have built two functions so far. A calc field rule seems too inadequate. Let's assume I'm tracking a weekly stock average. I want to keep a running total of the average on a daily basis. Some days, of course, the market is closed. I have a field in which to enter the market on a daily basis, and the calc field for the average - which I cannot get the result in to. After averaging, I round to the quarter point. A function does this. (Round to $x.xxxx). So far I have another function that also computes the divisor, i.e., number of days the market is open, on a running basis.
Should be as easy as falling off a log. Add the data for each day of the week; divide by the appropriate number of days, round, and place the result in the calculated field.
This may be the wrong approach; however my intent was to build another function to add the data, and include the divisor and round functions I've created.
I can't even get the third function built. This is my tale.
1. I select the appropriate table in "Tables/Sets."
2. I click on the "Code" tab.
3. Select "New".
4. Select Function.
5. It presents a screen with "Create Global Function".
Underneath is another window that is the Code Editor with my Round Function. (Why?)
6. I rename the Function in the "Create Global Screen"; fill in three names (Assume three different stocks). Click on the Disk icon to save. If in fact it saves, it must go to the electronic Valhalla, for I can not find it again.
I've repeated this action a number of times, yet it's nowhere to be found, and does not take me to a screen to enter code. The "OK" gets me to the underlying screen, which is my Round Function.
Since I've already created two functions, why can't I create another?
In other discussions, there is talk of examples. Certainly this begs for example. It also might help to answer the other questions asked on how|when|why to use X-Basic, and how to relate it to Field Rules -> Calc.
I've tried expressions in the calc field, but they too disappear.
Thanks to one and all who can get me out of the maze. I believe a well done example might benefit a number of lost souls.

[Tom Cone Jr]

RE: Calculated field

Tell us about your table structures.

If you are using a single table and entering daily prices for each stock I can envision an update operation which would calculate the weekly average and store it in a field in each daily entry, depending upon which week it falls in. This seems like a lot of work, when you probably just need to see the weekly average figures in a report.

Since the daily prices will probably include different collections of stocks, and since the market is closed some days creating occasional short weeks, I would definitely not describe the problem as a 'simple' one.

Please elaborate on your goal. Are you trying to produce a report which lists, by week, the average price for a series of stocks?

Are you trying to build a separate table which just contains the weekly average figures?

-- tom

[Richard Froncek]

RE: Calculated field

It is a single table. It contains a field for each day of the week for each of three commodities, along with a field for their weekly running running averages. A report may be printed as desired. The running average can be viewed in the table, a browse, or on a form as the data is entered. The form has the equivalent fields found in the table.
If needed, I can send the table in its current state (I have not yet developed an entry form or report in A5). Optionally, I can send the table, form, and report in its *.mbd format (Access, as I'm sure you know. I have not finished the entire project in Access, but want to switch to A5. I thought it would be much easier in A5 than Access. Of course I can send you the A5 table and the Access components.
I still say this is a situation for a good example that may well belong in the A5 text, whenever it is completed.
I'll boil down the Access *.mdb and post it here as soon as I can boil it down. Actually the A5 table is identical to the Access table. A5 is on another machine right now, and at the moment I'm having trouble communicating between the two.

[Richard Froncek]

RE: Calculated field

Thanks for your interest. Have it boiled down and trying to figure out how to attach it here. If it's not here, I'll have to try again.

[CALocklin]

RE: Calculated field

Creating a new function:

I know what a pain it was for me to learn to create Access procedures after building A5 scripts so here's how to create a function in A5. Follow the steps closely and you shouldn't have a problem.

- First, forget your "step 1" - this sounds like a holdover from your Access training - it doesn't hurt but it doesn't do anything either. It is not necessary in A5 to select a table before creating a script or function. In Access you usually attach VBA code to forms, queries, etc. but in A5 it is usually easier to just make them global scripts/functions which is the default UNLESS you put the script directly in the layout's events or buttons. This is rather like creating a public procedure in the Modules section of Access. (You can attach the A5 function to a table or set if you want to by using the "Library" window in the "Create Global Function" window - see below.)

- Second, in step 6 you said you "Click on the Disk icon to save." What disk icon?? If you click in the menu while the "Create Global Function" window is active, nothing will happen. You have to say OK in the "Create Global Function" window first. Clicking OK will save the new function with the first and last lines completed so you can fill in the rest - see 9 below.

1. Go to the Control Panel
2. Click on the "Code" tab.
3. Right click in a clear area of the control panel to get a list of options then click on "New" in the drop-down list.
-- OR --
If you have existing functions, right click on one of the existing functions and select "New Function" from the drop-down list.
4. You will now be in the "Create Global Function" window.
5. Give the function a new name.
6. Select the return type.
7. Add any argument names and types that will be used for input to the function.
8. Add the Library name to the "Library" box ONLY IF you feel a great need to store the function somewhere besides the database's global library. (I create a LOT of functions and scripts and never bother putting them in a special library. However, there could be a use for this if you want a function or script to be portable with the table instead of the database. This wouldn't make much sense unless you plan to use the same table in multiple databases - something I have never done.)
9. Now click the "OK" button in the "Create New Function" window. The function will be saved with only the first and last lines included and you will be taken directly to the editor window. Here's an example of what you should see:
function func1 as N(input1 as C)
' type your code here

end function

OK, now here's a quick function I created to average 5 fields in the current record. It took 50 times longer to write this explanation than it did to write the function. (Note: I modified the capitalization and spacing for readability.)

FUNCTION avg5flds as N( f1 as N, f2 as N, f3 as N, f4 as N, f5 as N )

	x = 0
	IF f1  0
		x = x + 1 
	END IF
	IF f2  0
		x = x + 1 
	END IF
	IF f3  0
		x = x + 1 
	END IF
	IF f4  0
		x = x + 1 
	END IF
	IF f5  0
		x = x + 1 
	END IF
	total = f1 + f2 + f3 + f4 + f5
	avg5flds = total / x

END FUNCTION

If the five field names were d1, d2, d3, d4, and d5, the calculation that you would then use is:
avg5flds( d1, d2, d3, d4, d5 )

This COULD be put in a calculated field in the table - but why??? Since the same function can be used on any form, report, etc., wouldn't it be just as easy to save hard drive space and just put the calculation in a field on the form? (Like an unbound control in Access.) Besides, this would mean that the record would save slightly faster (probably not noticeable on any recent computer) during data entry because there would be no need to run the calculation and save the result each time you enter/change data.

[CALocklin]

RE: Calculated field

Sorry about the double spacing in the function. I forgot that this message board does that when using the HTML ""pre>" command without marking the "Use HTML" box. And, marking this box requires the use of full HTML.

[CALocklin]

RE: Calculated field

I should mention that it is best to pull the data from the table even though the same values may be on the form. To find out why, go to the Tips section on Alpha's main website (click the Alpha logo on this page to go there) and review tip #10.

[Richard Froncek]

RE: Calculated field

I guess A5 isn't working correctly. The floppy disk icon, "Save" is at the top. I used the formulas you have. I had created two functions, and was trying to create a third. The "OK" click does not take me to a fuction code screen. It takes me to an underlying screen, which is my Roundquarter Function screen. Even though I had renamed the funfunction, and enter the arguments, th "OK" does NOT take me to the two line function screen. As I recall, it did with the first two functions I created. However, any number of attempts give me the procedure I've outlined. I can never find the new function I've named. Nor can I ever find any expressions in thefield rulues calculate. As I said, things seem to go to an electronic Valhalla, never to be found again. Guess I'll have to try reinstalling A5, again, the umpteenth time. Thanks for your time, response, and interest.
Dick

[Stan Mathews]

RE: Calculated field

I can't understand why you aren't getting the proper results following Cal's directions, but in any case, you could:

1)click the code tab
2)select one of your saved functions
3)right click and enter the design mode
4)immediately do a "Save as" and enter the new function's name
5)edit the line that begins with "function", substituting your new name for the name there (should be the same as the name of the original function chosen for editing), also edit the data type and argument name as necessary
6)you are now ready to modify the code, or you can erase it and begin afresh

[Richard Froncek]

RE: Calculated field

Will give it another go with this slight variation. Thank you.
Dick

[Richard Froncek]

RE: Calculated field

Thanks. Your method worked. Guess there's more than way to skin a cat in A5. It's tough enough learning one way, much less all the variants, albeit they're handy. Thanks again. Hopefully I can now make some progress.
Dick