Excel Fuction

[lkatz1]

How can we use this Excel fuction in Alpha Five

=MOD(10-(SUM(CEILING(MOD(MID(A1,{1,2,3,4},1)*{2,1,2,1},9.5),1))),10)

[MikeC]

Re: Excel Fuction

David,

You will have much better results if you do not use NON-Alpha functions....such as Sum and the vb equivalent of the Mid function (Alpha's has different arguments). I think the Mod function actually does have the same arguments so should be able to leave that the same.

[Tom Cone Jr]

Re: Excel Fuction

Mike, you're right. Can't use Excel functions in Alpha. But maybe he's asking (albeit somewhat obscurely) for help translating the excel function into xbasic ? My Excel skills are paltry so can't do it from here without an English translation of what the existing expression does. A good beginning point would be for David to tell us the data type and cell value (or text) found in cell A1, and then explain, step by step what the expression does. It could then be translated into xbasic.

[Steve Andrews]

Re: Excel Fuction

How can we use this Excel fuction in Alpha Five

=MOD(10-(SUM(CEILING(MOD(MID(A1,{1,2,3,4},1)*{2,1,2,1},9.5),1))),10)

David,

I agree with the points made by Mike & Tom. While I am not fully familiar with using arrays within expressions for Excel, it is true that Alpha is not design to do so. This leaves the first step of considering how you would arrange the array values within Alpha (e.g. as field values within a table).

But in an attempt to translate your expression, I have hardcoded two arrays into some code for the meantime. Using a function for the expression, I simply passed the pointers of the arrays into the function (as well as the value of Cell A1). Here is that code:

dim array1[4] as N
dim array2[4] as N
Lst1=<<%aa%
1
2
3
4
%aa%
Lst2=<<%bb%
2
1
2
1
%bb%
array1.initialize(Lst1)
array2.initialize(Lst2)

dim Cell_A1 as C
Cell_A1 = "2936"

Output = Excel_Formula(Cell_A1, array1, array2)

ui_msg_box("Formula Result",alltrim(str(Output)))

END


FUNCTION Excel_Formula as N(cA1 as C, nArr1 as P, nArr2 as P)
	dim nResult as N
	nResult = 0
	for j = 1 to 4
		nResult = nResult + max(mod(val(substr(cA1,nArr1[j],1))*nArr2[j],9.5),1)
	next j
	nResult = mod(10-nResult,10)
	
	Excel_Formula = nResult
END FUNCTION

I don't know if this will help but it's my 2 cents.

Steve

[lkatz1]

Re: Excel Fuction

So please explain all steps what we should do how to setup fields etc
thank you

[Steve Andrews]

Re: Excel Fuction

David,

To know how best to set it up, I would need to know what values are likely to change upon evaluating the function on different calls. While you have arrays of length 4 within the expression (e.g. "{2,1,2,1}"), might you need the expression for longer arrays on other calls? Likewise, will these two arrays always have the same values? Until I have a better feel on how you might use this expression, I would not know the optimal way to set it up.

Steve

[lkatz1]

Re: Excel Fuction

this is what we need a check digit which is figured out from 4 digits instead of 9 digit sample enclosed
file attached

[Steve Andrews]

Re: Excel Fuction

David,

Seeing your Word attachment, I located a similar Excel worksheet from which it was copied. One thing didn't look right, however. If the "sum of digits" was a multple of 10, the Check Digit which showed up was '10'. I would think that in such a case you would want a Check Digit of '0' instead. Thus, in the function below, I have the "original" return value consistent with the workbook I found but a "revised" return value based on what I think you want.

FUNCTION Check_Digit as N(cA1 as C)
	DIM j AS N
	DIM nDgt AS N
	DIM nOddEven AS N
	DIM nProd AS N
	DIM nSum AS N
	nSum = 0
	if .NOT. isnumber(cA1)
		ui_msg_box("Illegal Function Call","Check_Digit() can only accept numerical parameters.")
		Check_Digit = -1
		exit function
	end if
	
	for j = 1 to 4
		nOddEven = mod(j,2) + 1 	'= 2,1,2,1
		nDgt = val(substr(cA1,j,1))
		nProd = nDgt*nOddEven
		nSum = nSum + int(nProd/10) + mod(nProd,10)
	next j

	'Check_Digit = 10 - mod(nSum,10)		'<-- original
	Check_Digit = mod(10 - mod(nSum,10),10)	'<-- revised
END FUNCTION

I'm sure that you must know the basic approach to creating a UDF. You can find it in the help files. I hope that this helps.

Steve

[lkatz1]

Re: Excel Function

Than You for replying
We want 4 digits the checkdigit is the 5 th digit
try this link if it matches the checkdigit the we are ok but we have no idea what udf etc please give basic idea startup to configure copy etc

http://ribbs.usps.gov/files/acs/KEYLINE.EXE

[Steve Andrews]

Re: Excel Fuction

David,

I altered the function slightly to return a character rather than a number. To create your UDF, go to the Codes tab of your Control Panel. Then do the following:
1) Press "New".
2) Select "Function"
3) Enter: "Check_Digit" for Function Name
"Character" for Return Type
1 parameter with Name = "cA1" and Type = "Character"
4) Press OK.
5) To be safe, press Save right away.
6) Between the FUNCTION and END FUNCTION lines, paste the body of the function below.
7) Press Save again.

You will then be set. Hear is the slightly revised code.

FUNCTION Check_Digit as C(cA1 as C)
	DIM j AS N
	DIM nDgt AS N
	DIM nOddEven AS N
	DIM nProd AS N
	DIM nCD as N
	DIM nSum AS N
	nSum = 0
	if .NOT. isnumber(cA1)
		ui_msg_box("Illegal Function Call","Check_Digit() can only accept numerical parameters.")
		Check_Digit = -1
		exit function
	end if
	
	for j = 1 to 4
		nOddEven = mod(j,2) + 1 	'= 2,1,2,1
		nDgt = val(substr(cA1,j,1))
		nProd = nDgt*nOddEven
		nSum = nSum + int(nProd/10) + mod(nProd,10)
	next j

	'nCD = 10 - mod(nSum,10)		   '<-- original
	nCD = mod(10 - mod(nSum,10),10)	   '<-- revised
	Check_Digit = str(nCd,1)
END FUNCTION

Steve

[lkatz1]

Re: Excel Fuction

Thank You
Now how to we set the dbf fields we have a field with the 4 digits we need a new field forthe check digitso were does the function link

[Steve Andrews]

Re: Excel Fuction

It all depends on how you plan to use this check digit in the end. One approach would be to add one field to your DBF. Call it "CheckDgt" (character, 1 byte long, 0 decimals). After this is added, make it a calculated field.

To do that, go to the field rules screen for that table and field. Under the Field Types tab, choose "Calculated". Then in the expression box, put the expression: Check_Digit(Your_Field) where "Your_Field" is the name of the 4-digit field which you already have set up.

Note that the above approach will NOT update the new field for records already in the table. It is not until you save a record that the new field will be calculated. However, if you right-click the table in the Control Panel, you can go to "Utilities" and "Recalculate Field Rules" to force a recalculation.

Alternatively, you could make your new field 5 bytes and give it the expression Your_Field+Check_Digit(Your_Field). That is why I say that it depends on your end use as to what the best way would be to put this together.

Steve

[DaveM]

Re: Excel Fuction

David,

Just for explanations sake:

a udf is a user defined function. While Alpha has hundreds of functions you can call to do the normal things you might need, they also gave us a way to make our own. That is what Steve is giving you. Look at the code tab for where you would create a udf.

Your table needs to be set up with one field for the number you are going to input and any other fields you need to input. You can also set it up with however many fields for any calculated values.

I might suggest working a bit with Alpha Sports to see how the program flows. It will surely give you some ideas of what you need and how you want your app to flow.

Spreadsheets and databases are totally different animals as shown by MS having excel and access, not to mention foxpro and vb.

At least some in here have had enough spreadsheets to be able to help you with the transition.


.