Alpha Anywhere How to Write Formula in Report

In our last exercise we looked at using queries controlled by variables to fetch and display a range of records in a form as well as using the table method to step through the child records to populate a rich text object with memo fields from the child records. In this exercise I will demonstrate fetching forms and reports via queries versus the standard filter process.

The code displayed below is a standard xBasic way of opening a form which has no additional criteria.

DIM Shared varP_MyForm as P DIM layout_name as c  layout_name = "MyForm" DIM tempP as p tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@"))) if is_object(tempP) then  	if tempP.class() = "form" .or. tempP.class() = "browse" then  		tempP.activate() 	else 		varP_MyForm = :Form.view(layout_name) 	end if else  	varP_MyForm = :Form.view(layout_name) end if

( If you are not sure how to write this, use the Menu Code xBasic Script Genie on the code editor. )  This method sets a temp pointer to the form object name. Next it checks to see if it is an actual object and whether or  not it is in memory. If so, it simply gives it focus, otherwise it loads the form then gives focus.

If you want to load a form and fetch a specific record; below is the standard method.

query.filter = "quote_num = Var->vQt_Num" query.order = "recno()" query.filter = convert_expression(query.filter,"VE") DIM Shared varP_MyForm as P DIM layout_name as c  layout_name = "MyForm" dim tempP as p tempP=obj(":"+object_name_normalize(word(layout_name,1,"@"))) if is_object(tempP) then  	if tempP.class() = "form" .or. tempP.class() = "browse" then  		dim flagIsBaseFilter as l  		flagIsBaseQuery = .t. 		if flagIsBaseFilter then  			tempP.BaseQueryRun(query.filter,query.order) 		else 			tempP.QueryRun(query.filter,query.order)		 		end if  		tempP.activate() 	else 		varP_MyForm = :Form.viewqueried(layout_name,query.filter, query.order ) 	end if else  	varP_MyForm = :Form.viewqueried(layout_name,query.filter, query.order ) end if

Compare the two code snippets and you will see the difference is the setting of the query and order then the QueryRun function based on the form load option and finally if the form is not in memory it is loaded using the Form.viewqueried function rather then Form.View.

Now this works great when you have a small application but if the table the form is bound to is huge with many child records, the form will take a second or two to load. Doesn't sound like much but customers will complain about the delay. So, lets look at an alternative method to load a form.

if is_object("MyForm") then MyForm.show() MyForm.activate() else :Form.view("MyForm") end if Var->xSearchfor = "Quote_Num" xflt = vQT_Num MyForm:button1.push()

Here I simply load the form. Next I set a radio choice on the form ( xSearchFor ) to Quote_Num and pass the calling form variable vQt_Num to my query filter variable xFLT. Then I push a button ( button1 ) on the form to process the query needed. Since I am loading the form with no conditions the form loads immediately and when the button runs the query it happens locally rather than externally which is much faster.

Here is the code which runs on the button I push with xBasic

t = table.current() t.query_detach_all() xbasic_wait_for_idle() ui_freeze(.t.)  if xSearchFor = "Invoice_Num" then     xflt = "Invoice_No = '" + xflt + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) else if xSearchFor = "Quote_Num"     xflt = "Quote_Num = '" + xflt + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) else if xSearchFor = "WO_Num"     xflt = "WO_Number = '" + xflt + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) end if  cond1.refresh()  vQt_Num = WO_NUM0.text xCustID = customerid0.text  button15.Activate() ui_freeze(.f.)

One drawback to this method is since the table is queried you cannot use the next or previous toolbar buttons because the query drills down to just one record. The next example show how to overcome that when using the external query record form load method .

DIM Shared varP_MyForm as P DIM layout_name as c  layout_name = "MyForm" dim tempP as p tempP=obj(":"+object_name_normalize(word(layout_name,1,"@"))) if is_object(tempP) then  	if tempP.class() = "form" .or. tempP.class() = "browse" then  		dim flagIsBaseFilter as l  		flagIsBaseQuery = .t. 		if flagIsBaseFilter then  			tempP.BaseQueryRun(xfLT) 			tempP.Index_SetExplicit("Invoice_No") 		else 			tempP.BaseQueryRun(xfLT) 			tempP.Index_SetExplicit("Invoice_No")		 		end if  		tempP.activate() 	else 		varP_MyForm = :Form.viewqueried(xfLT) 		varP_MyForm.Index_SetExplicit("Invoice_No") 	end if else  	varP_MyForm = :Form.viewqueried(xfLT) 	varP_MyForm.Index_SetExplicit("Invoice_No") end if

This code queries the form then uses the function Index_SetExplicit to enable record fetching while still displaying the called record. This same function can be done with our local query example.

t = table.current() t.query_detach_all() xbasic_wait_for_idle() ui_freeze(.t.)  if xSearchFor = "Invoice_Num" then xflt = "Invoice_No = '" + xflt + "'" records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) else if xSearchFor = "Quote_Num" xflt = "Quote_Num = '" + xflt + "'" records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) else if xSearchFor = "WO_Num" xflt = "WO_Number = '" + xflt + "'" records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) end if  cond1.refresh()  vQt_Num = WO_NUM0.text xCustID = customerid0.text  button15.Activate() MyForm.Index_SetExplicit("Invoice_No") ui_freeze(.f.)

Here we simply add then index call to the end of the code.

Now lets look at using a variable query to run a report. Create a button on your form to print the selected report and set the report call as below.

                current_filter = current_filter_expn() 	current_order = current_order_expn() 	query.filter = current_filter 	query.order =  current_order	 	prompt_result = ui_get_print_or_preview("Print") 	If prompt_result = "Print" then 		:Report.Print("myreport",query.filter,query.order) 	Else if prompt_result = "Preview" then  		:Report.Preview("myreport",query.filter,query.order) 	End if

This lets Alpha Software handle the query for you. No additional filters or code needed. That's all for today. I hope you found this helpful and useful.

As always, if you are a programmer looking to farm out some work, a current business with an Alpha Software application which needs some TLC or someone new to programming in Alpha Software and need help just drop me a line. All work is guaranteed and prices are reasonable. Send a request to :

NLawson@cdc-TakeCharge.com

or call and leave a message at:

713 417-6831

Thank you for your time.

In this current age of application development it is not enough to create an application which looks good and does what it was intended to do, it must also be fast. In Alpha Software when you create a table or set you will most likely create one or more index's for controlling your data view. In fact even if you do not create an index, Alpha Software will do so automatically based on expected needs.

In one respect this is great because fetching and retrieving records becomes easy for both the developer (you) and the user. However as your application is scaled up with the growth of your business fetching and retrieving records will slow down. For example say you have an invoicing and inventory application where your invoice header table has grown to 80,000 plus records and the invoice items table holds and average of ten items per invoice that means the child table in the set will have at least 800,000 records. Now place that application on a central server with at least 20 workstations all accessing the application at the same time fetching updating adding and deleting records based on your predefined index's. Each index will be updated and refreshed with each change, addition or subtraction of the parent and or child record which gets slower and slower based on the size and access of the table and or set.  My solution to this problem is to allow Alpha Software to create the minimum needed index's and then use queries to fetch, sort and display data on the form.

To compare the two methods I will use a application I created on this blog several years ago; January 2013 to be exact; The King James Bible Reader. ( To review that lesson click Here.) In the original application I created three tables; verses, chapters and books. Verses contained a list of each verse in the bible, chapters each chapter and books each book and each record in the specific table had a memo field with the selected text. For example in the verses table Genesis 01-01 the memo field had only the single verse, in chapters it had all verses for Genesis chapter 01 and in books it had the entire book of Genesis in the memo field. I did this to insure the data would display  quickly based on using index's and high level append and posting operations. In this new setup I only need the verses table and a single rich text object to display the records by user choice and it actually works faster. Below is an image of our new form using four variables to edit and control our data queries.

This form is plain vanilla compared to our original Form

but this exercise in not about form rather function.

In the original the user would select the volume then either book chapter or verse then click Display Selection or Add Selection in order to see the selection in the  Reader. In the background the code would select the table then either append the requested memo field to the reader table using replace or post the selected memo field to add the memo field to current memo  in the reader. This worked fine as written but it required a lot of code to do a simple process and it was not very fast. Looking at our new form you can see we use four form variables with a few local variables to do the processing and it will now display the entire bible in the rich text object in the amount of time it used  to take to display a single life issue in the old application.

Lets start with the form design.

This form is bound to the verses table ( Does not need to be it is just what I chose to do. )

On the right of the form is a browse view of the verse link field for each verse in the Bible. The center of the form holds the Rich Text object which I dragged onto the form from the Form Toolbox. On the Left are the variables I use to control the data via queries. In the image above, you can see I placed the book variable on the form as a List Object computed automatically with values from a table or set.

The Chapter variable is a list object populated using xBasic ( shown above )

and our radio choice is a radio object populated manually. Now lets look at the code on these objects,

The button below our radio choice runs three separate routines based on the radio choice the user makes.

if vRdrView = "Book" then 	dim xchp as C 	dim cNbr as C 	xflt = Var->vBooks 	xflt = "Bookname = '" + xflt + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) 	'xcount = count(Verses->vLink,GRP->Grand) 	xchp = "" 	vChapter = "" 	KJB_Reader:Tables:verses.Batch_Begin() 	KJB_Reader:Tables:verses.Fetch_First() 	while .NOT. KJB_Reader:Tables:verses.Fetch_Eof() 		cNbr = KJB_Reader:Tables:verses.CHAPTER 		if xchp = cNbr then 			vChapter = vChapter 		else	 			IF KJB_Reader:Tables:verses.CHAPTER = "001" then 				vChapter = KJB_Reader:Tables:verses.CHAPTER +crlf() 			else 				vChapter = vChapter + KJB_Reader:Tables:verses.CHAPTER +crlf() 			end if 		end if 		xText = KJB_Reader:Tables:verses.KJB_BY_VERSE 		xReader = xReader + crlf() + crlf() + word(xtext,2,crlf(),1) 		xchp = KJB_Reader:Tables:verses.CHAPTER 		KJB_Reader:Tables:verses.Fetch_Next() 	end while 	KJB_Reader:Tables:verses.Batch_End() 	KJB_Reader:rtf1.rtf.plain_text = xReader 	DIM pObj AS P 	 	pObj = topparent:vChapter.this 	pObj.settings.dynamic_list = vChapter + <<%str% 	%str% 	topparent:Vchapter.Settings.Unique_keys  = .t. else if vRdrView = "Chapter" then 	xflt = "Bookname = '" + VBooks + "'.and. Chapter = '" + VChapter + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) 	 	KJB_Reader:Tables:verses.Batch_Begin() 	KJB_Reader:Tables:verses.Fetch_First() 	while .NOT. KJB_Reader:Tables:verses.Fetch_Eof() 		xText = KJB_Reader:Tables:verses.KJB_BY_VERSE 		xReader = xReader + crlf() + crlf() + word(xtext,2,crlf(),1) 		KJB_Reader:Tables:verses.Fetch_Next() 	end while 	KJB_Reader:Tables:verses.Batch_End() 	KJB_Reader:rtf1.rtf.plain_text = xReader else 	KJB_Reader:Rtf1.Rtf.Rtf_text  = " " 	KJB_Reader:rtf1.Refresh() 	t = table.current() 	xText = t.kjb_by_verse 	xText = word(xtext,2,crlf(),1) 	topparent:Rtf1.Rtf.Rtf_text  = "{\rtf1\ansi \deff0{\colortbl;\red153\green51\blue0;}{\fonttbl{\f0\fswiss Tahoma;}{\f1\fnil Bookman Old Style;}}{{\b \i \f1 \cf1 \fs28'"+  Var->xtext + "'\par }}}" end if

The first option is book. Looking at the code you can see we use another local variable xFlt  (xfilter)

which we first assign the variable of vBooks. Then we modify xFlt by adding the second call where we set xflt equal to xflt wrapped in a text string.

xflt = Var->vBooks xflt = "Bookname = '" + xflt + "'" records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)

in this example xflt becomes

Bookname = "Genesis"

now that is passed to our form object query and our records are fetched but we are not done yet.  Next I am using a very fast form tables method to fetch through the child records processing only the records controlled by the form index. Once a parent record is fetched in Alpha Software, the software automatically knows which child records satisfy the key index for the child records. Using the table method here I only fetch through the records tagged as Genesis in our table.

                xchp = "" 	vChapter = "" 	KJB_Reader:Tables:verses.Batch_Begin() 	KJB_Reader:Tables:verses.Fetch_First() 	while .NOT. KJB_Reader:Tables:verses.Fetch_Eof() 		cNbr = KJB_Reader:Tables:verses.CHAPTER 		if xchp = cNbr then 			vChapter = vChapter 		else	 			IF KJB_Reader:Tables:verses.CHAPTER = "001" then 				vChapter = KJB_Reader:Tables:verses.CHAPTER +crlf() 			else 				vChapter = vChapter + KJB_Reader:Tables:verses.CHAPTER +crlf() 			end if 		end if 		xText = KJB_Reader:Tables:verses.KJB_BY_VERSE 		xReader = xReader + crlf() + crlf() + word(xtext,2,crlf(),1) 		xchp = KJB_Reader:Tables:verses.CHAPTER 		KJB_Reader:Tables:verses.Fetch_Next() 	end while 	KJB_Reader:Tables:verses.Batch_End() 	KJB_Reader:rtf1.rtf.plain_text = xReader 	DIM pObj AS P 	 	pObj = topparent:vChapter.this 	pObj.settings.dynamic_list = vChapter + <<%str% 	%str% 	topparent:Vchapter.Settings.Unique_keys  = .t.

Using this method compiles our verses very fast and adds then to the plain text property of our rich text object. At the same time it automatically updates our vChapter dynamic list. and compiles our requested verses.

Next is the option for Chapters.

else if vRdrView = "Chapter" then 	xflt = "Bookname = '" + VBooks + "'.and. Chapter = '" + VChapter + "'" 	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.) 	 	KJB_Reader:Tables:verses.Batch_Begin() 	KJB_Reader:Tables:verses.Fetch_First() 	while .NOT. KJB_Reader:Tables:verses.Fetch_Eof() 		xText = KJB_Reader:Tables:verses.KJB_BY_VERSE 		xReader = xReader + crlf() + crlf() + word(xtext,2,crlf(),1) 		KJB_Reader:Tables:verses.Fetch_Next() 	end while 	KJB_Reader:Tables:verses.Batch_End() 	KJB_Reader:rtf1.rtf.plain_text = xReader

Since the chapter variable list is already created I only need to query the records by the user selection and compile the verses and add then to the rich text object. Again we use the table method to process the request in a nano second.

Our final option is the verse choice.

else 	KJB_Reader:Rtf1.Rtf.Rtf_text  = " " 	KJB_Reader:rtf1.Refresh() 	t = table.current() 	xText = t.kjb_by_verse 	xText = word(xtext,2,crlf(),1) 	topparent:Rtf1.Rtf.Rtf_text  = "{\rtf1\ansi \deff0{\colortbl;\red153\green51\blue0;}{\fonttbl{\f0\fswiss Tahoma;}{\f1\fnil Bookman Old Style;}}{{\b \i \f1 \cf1 \fs28'"+  Var->xtext + "'\par }}}" end if

Here I am not compiling multiple verses just sending the user selection to the rich text object and notice I did not use plain Text but rather RTF_Text. This is not necessary. I just did it to show you how it would need to be written if you wanted to go that route.

The user opens the form, selects the data view  they want, clicks the button and based on their selection the correct book or chapter or verse is displayed. Very simple and very powerful.

The code for each user choice is also on the onChange event of our list objects. Just the code for Books is on the onChange event for the vBook list, chapter code in the onChange event of  vChapter list object and verse on verse. By doing that the user can switch between books if books is selected, just by picking a new book in the list. The same goes for chapters and verses.

I hope you find this exercise useful. In our next session I will show how to use queries to find a specific record in an external table and load the form to that record as well as some other cool features.

As always, if you are a programmer looking to farm out some work, a current business with an Alpha Software application which needs some TLC or someone new to programming in Alpha Software and need help just drop me a line. All work is guaranteed and prices are reasonable. Send a request to :

NLawson@cdc-TakeCharge.com

or call and leave a message at:

713 417-6831

Thank you for your time.

ActiveX is a software framework created by Microsoft that adapts its earlier Component Object Model (COM) and Object Linking and Embedding (OLE) technologies for content downloaded from a network, particularly from the World Wide Web. In Alpha Software most Super Controls are in fact ActiveX objects. In this article we will look at adding a Super Control Web Component and use it for a file explorer type control. The image below is of the single pane view of our finished controller.

Single pane view

As you can see in the image above, the viewer looks very similar to the normal File Explorer  which ships with Windows. The exception is ours has a duel pane view.

With a duel pane view drag and drop is greatly simplified.

Some may ask; if it's basically the same as the shipped File Explorer why bother and the answer is by writing your own object you have the ability to track and record all user actions allowing for easy corrections or undo's.

Step One:

Create a form and drag the super Control onto the form.

Select control then draw the area on the form where you wish the controller to occupy.

Step Two

Select which Super Control you wish to use.

Step Three

Assign the Super Control values using the Super Control Designer Dialog.

Set Template Type to User Defined and the URL to a variable which controls our target folder path.

Step Four

Save and name your form and the browser is done.

On our form ( See first image above. ) We have several single click action buttons which

1. Controls the explorer view
2. Tracks the path of the folder
3. Sets the path of the folder
4. Interacts with the objects selected in the browser pane.

Lets look at the utility buttons copy and paste to. Copy

the onPush event of the Copy Button is

tcfilesys.activate()
sys_send_keys("{^c}")

Our Explorer object stores the selected object / objects in memory and I use the Send Keys function to copy the selected values to the clipboard. Once the target folder is selected I use Send Keys again to paste the objects to the target folder and the Microsoft framework does the heavy lifting.

The code for the Open button is

sys_open(topparent:tcfilesys.activex.Document.FocusedItem.Path)

Here I use the sys_open function and the path of our selected item stored in our ActiveX object.

I hope you find this useful and if you have a business with an Alpha Software application and you need help, Contact

NLawson@cdc-takecharge.com

No project is to large or small. Thank you for your time

April 18th, 2017

Hello everyone

I hope everyone enjoyed their weekend. I once had a customer tell me,

'The computer needs to work for me not me work for the computer.'

That makes perfect sense to me and I take care to ensure all forms I design address function first then form.

Today I want to look at form function and specifically object focus. An issue; well not really an issue rather annoyance; is when a button is clicked on a form and the selected field or form object loses focus. Many of you may think this is nit picky but if you are developing a form which is set for speed of data entry then it would become a real annoyance for the data entry person.   Alpha Software provides object classes one of which is

.class = button

The simple routine below can be put at the top of a button and will return focus to the previous object selected once the button is pushed. Here is the code.

If obj(topparent.active()).class() = "button" then      obj(topparent.active_prev()).activate() End If

If your button is performing a form related function such as;

• Setting the form mode to edit
• Sorting records in an embedded browse by the selected column
• Looking up a value from an external table
• Popping up a dialog
• etc

then this works great. Any code below the above routine will run then when focus is returned to the form the user is back on the original object. Cool!

As an example, maybe you are designing a form for creating deposits. On arrive of the total credit card field you could display a button which when clicked will open a dialog form allowing the addition of  credit card receipts and then post back the total to the field which has focus. It would also work well in a payroll package where you have to total employee punches. Now the reason I say to pop up a button which can be clicked is because there will be cases when the user just needs to enter the correct total and does not need the supporting detail. This design will allow them to do just that thus giving them the best of both worlds.

That's all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

April 13th, 2017

Hello everyone. Wow has it been a busy month. My last post was on March the 13th and a lot has happened since then. Many of you know I have always said to be successful as a custom application developer you must give the customer more that they expect, which is what I always try to do. Not only does it make you the developer look good, but it make them, (the customer), see the potential for their application. The last thirty days has been a living, breathing example of just that.

One of the upgrades I am doing is in inventory control. A fairly common business model is buy low and sell high which in it self is very straight forward. When you have slow moving inventory, knowing the actual cost of your inventory on hand becomes more complicated. As an example, say you have four parts (fan motors) in stock originally purchased for25.00 each two years ago. You just received an order needing 5 fan motors for a customer job. You contact your supplier and find the motors went up to 50.00 each and the minimum order is 10 for that price point. You place the order and receive the motors now having fourteen in stock. The value of your inventory is

     4 X 25 = 100.00

+10 X 50 = 500.00

Total          600.00

What is your customers cost per unit? Answer is

600 / 14 = 42.86 per motor.

Currently my customer computes the cost by hand and updates the inventory table manually. They want it to happen from the receiving form automatically. In their inventory table they have no field for tracing their cost for new purchases, only an inventory value field. So to give them what they want I am using the lookup function in combination with some variables and a high level posting operation. Since they may be ordering several parts from the manufacturer, I use the form tables method to fetch through the records comprising the specific order. The code below is attached to a button on the form which runs on the onPush event.

parentform.commit() Post_Incoming_Parts:Tables:Reqdetail.Batch_Begin() Post_Incoming_Parts:Tables:Reqdetail.Fetch_First() while .NOT. Post_Incoming_Parts:Tables:Reqdetail.Fetch_Eof()     Post_Incoming_Parts:Tables:Reqdetail.change_begin()            Post_Incoming_Parts:Tables:Reqdetail.ON_HAND = lookupn("F",Post_Incoming_Parts:Tables:Reqdetail.Part_Num,"On_Hand","inventry","Part_Num")     Post_Incoming_Parts:Tables:Reqdetail.change_end(.t.)     Post_Incoming_Parts:Tables:Reqdetail.Fetch_Next() end while Post_Incoming_Parts:Tables:Reqdetail.Batch_End() Post_Incoming_Parts:browse1.commit() xbasic_wait_for_idle() Post_Incoming_Parts:browse1.Activate() Post_Incoming_Parts:browse1.Resynch() parentform.Commit() parentform.Refresh_Layout()  END

Because the inventory is fluid and changes daily the check on hand button looks at each item ordered in inventory and uses the lookup function to to update the current on hand field. Because each record in the detail table has a unique part number I use an absolute reference for the A Key Value portion of the lookup function. This insures each record fetched supplies the correct Part Number thus returning the correct on hand value from inventory.

Next the user clicks Post Receivables.

Req_ord_nbr = Req_No.text a_tbl = table.open("inventry") post.t_db = "reqdetail" post.m_key = "Part_Num" post.t_key = "Part_Num" post.m_filter = "" post.t_filter = "Req_No=Var->Req_ord_nbr" post.m_count = 6 post.m_field1 = "ACT_COST" post.m_exp1 = "if(On_Hand=0,@Reqdetail->ACT_Cost,((@Reqdetail->ACT_Cost*@Reqdetail->Qty_Posted+@ReqDetail->On_Hand*ACT_Cost))/(@Reqdetail->Qty_Posted+@ReqDetail->On_Hand))" post.m_field2 = "Superceed" post.m_exp2 = "@REQDETAIL->Superceed" post.m_field3 = "Descript" post.m_exp3 = "@REQDETAIL->Descrip" post.m_field4 = "Wholesale" post.m_exp4 = "@REQDETAIL->Wholesale" post.m_field5 = "Date" post.m_exp5 = "@REQDETAIL->Date" post.m_field6 = "STOCK" post.m_exp6 = "@REQDETAIL->Stock" post.t_count = 0 a_tbl.post() a_tbl.close()

Now you can see that since the on hand is available in reqdetail a simple formula on the Act_Cost field expression averages the part cost on the fly just as the customer requested.

Let's say you have a customer which builds parts into a finished product. Part prices change as well as the labor to build the finished product. Table sum  function can be used as well.

dim SHARED xCnt as N dim SHARED xTrC as N xCnt = tablesum("inventry","Part_Num= '" + Var->xPNbr + "' ","On_Hand") xTrC = ((tablesum("inventry","Part_Num= '" + Var->xPNbr + "' ","ACT_Cost")*xCnt)+NewTC)/(xCnt+val(xOnHand)) a_tbl = table.open("inventry") post.t_db = "quotes" post.m_key = "Alltrim(Part_Num)" post.t_key = "Alltrim(Model_Num)" post.m_filter = "" post.t_filter = "Alltrim(Model_Num) = Var->xPNbr" post.m_count = 5 post.m_field1 = "ON_HAND" post.m_exp1 = "ON_HAND+val(Var->xOnHand)" post.m_field2 = "AVAILABLE" post.m_exp2 = "AVAILABLE+val(Var->xOnHand)" post.m_field3 = "ACT_COST" post.m_exp3 = "Var->xTrC" post.m_field4 = "WHOLESALE" post.m_exp4 = "if(Var->NewTC>Wholesale,Var->NewTC,WHOLESALE)" post.m_field5 = "LIST" post.m_exp5 = "if(Var->NewTC/.70>List,Var->NewTC/.70,LIST)" post.t_count = 0 a_tbl.post() a_tbl.close()

Here I declare two variables xCnt and xTrC and use tablesum to build the values needed from inventory to complete the averaging equation. I then use the posting operation to add the finished product to inventory. Very simple.

Now I have said it before and it's worth saying again.

Complex problems do not always require complex solutions.

It's your job as the developer to know when and if a simple solution will work.

That's all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

March 13th 2017

Hello everyone. Hope you had a nice weekend. This morning I received a report design request which I never had before so I thought I would share the solution with you. A customer called and said they wanted a duplicate copy of their Parts quote report which did not show Part Numbers. In itself this is not such a strange request but I don't like to duplicate reports if the report layout can be controlled by code. IN addition the report has custom color bands in the detail. See below.

To honor their request I have three choices

1. Make a duplicate report and add a second print button to the quote form.
2. Modify the Print button on the form to prompt for the proper report
3. Write code which will modify the report layout on the fly.

I chose option three.

The color bands on the report are controlled by a calculated field on the report which sets a variable to odd or even. For those who are not sure how to do that here is the calculation.

flag_color = iif(mod(calc->run_cnt,2)=0,"even","odd")

The calculated field is then placed over the detail line of the report and a color equation is set. Here is the equation to color each alternate band.

case(calc->Flag_Color="even","White on white",.T.,"Dirty White on Dirty White")

Now if I change the calculated field color equation only I will get the wrong result because it does not address individual fields. If I set an equation for the individual field only I get the following which is incorrect.

Notice the color band is lost on the field we are affecting. The solution I came up with is to create a variable which I can pass to the report which adjust the Part field color equation taking in consideration both the variable selected by the user and the color band equation. Here is the code

case(Var->DocView="Hide".and.calc->Flag_Color="even","White on white",Var->DocView="Hide".and.calc->Flag_Color="odd","Dirty White on Dirty White",.T.,"black on white")

The onPush event of our print button sets the value of DocView via the use of a dialog box. Here is the dialog box and the code for the print button

'Date Created: 21-Nov-2016 11:45:41 AM 'Last Updated: 13-Mar-2017 07:54:31 AM 'Created By : NLaws 'Updated By : NLaws ' dim Shared prchoice as C DIM SHARED sng_multi as C DIM SHARED varC_result as C ok_button_label = "&Continue" cancel_button_label = "&Cancel" Delete XdialogStyle dim XDialogStyle as p XDialogStyle.AccentColor = "White" XDialogStyle.Color = "#127+127+127" prchoice = "" varC_result = ui_dlg_box("Parts Invoice Print Dialog",<<%dlg% {Windowstyle=Gradient Radial Bottom Right} ; {line=1,0}; ; {sp=5}{region}(sng_multi:Parts Quote); {sp=5}(sng_multi:Parts Quote wo Prt Nbr); {endregion}; ; {endregion}; {line=1,0}; {region} ; <*25=ok_button_label!CONTINUE> <25=cancel_button_label!CANCEL> {endregion}; %dlg%,<<%code% if a_dlg_button = "Continue" then  prchoice = sng_multi else End  end if   %code%) if prchoice = "" then  End end if  if prchoice = "Parts Quote wo Prt Nbr" then  DocView = "Hide" end if    dim record_number as N record_number = current_record_number() query.filter = "recno() = " + record_number query.order = ""  prompt_result = ui_get_print_or_preview("Print") If prompt_result = "Print" then  :Report.Print("Parts Quote",query.filter,query.order) Else if prompt_result = "Preview" then   :Report.Preview("Parts Quote",query.filter,query.order) End if if prchoice = "Parts Quote wo Prt Nbr" then  DocView = "" end if

Now my customer gets the desired result. See below.
and I did not need to create an unnecessary duplicate report.

That's all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

March 10th 2017

Hello everyone. In my last post I said I might try to create a method of setting up a ad hoc chess board so the user can practice specific chess scenarios. I decided to go ahead with this idea.

The first thing I needed to do is add a new menu option to my menu.

If you have been following along you know the menu is a dialog form and therefor easy to modify. I simply added another option to my radio object then added that choice to the condition statement.

Here is the code which runs when the Custom Game is selected.

else if xchoice = "Custom Game" then     button10.show()     xCGame = "Custom"     DIM Append as P          a_tbl = table.open("tblgrid")     append.t_db = "BLANKBOARD"     append.m_key = "Ranknbr"     append.t_key = "Ranknbr"     append.m_filter = ""     append.t_filter = ""     append.type = "Unique, replace existing"     append.m_count = 46     append.m_case1 = "EITHER"     append.m_field1 = "RANKNBR"     append.m_exp1 = "@BLANKBOARD->RANKNBR"     append.m_case2 = "EITHER"     append.m_field2 = "A"     append.m_exp2 = "@BLANKBOARD->A"     append.m_case3 = "EITHER"     append.m_field3 = "B"     append.m_exp3 = "@BLANKBOARD->B"     append.m_case4 = "EITHER"     append.m_field4 = "C"     append.m_exp4 = "@BLANKBOARD->C"     append.m_case5 = "EITHER"     append.m_field5 = "D"     append.m_exp5 = "@BLANKBOARD->D"     append.m_case6 = "EITHER"     append.m_field6 = "E"     append.m_exp6 = "@BLANKBOARD->E"     append.m_case7 = "EITHER"     append.m_field7 = "F"     append.m_exp7 = "@BLANKBOARD->F"     append.m_case8 = "EITHER"     append.m_field8 = "G"     append.m_exp8 = "@BLANKBOARD->G"     append.m_case9 = "EITHER"     append.m_field9 = "H"     append.m_exp9 = "@BLANKBOARD->H"     append.m_case10 = "EITHER"     append.m_field10 = "AIMG"     append.m_exp10 = "@BLANKBOARD->AIMG"     append.m_case11 = "EITHER"     append.m_field11 = "BIMG"     append.m_exp11 = "@BLANKBOARD->BIMG"     append.m_case12 = "EITHER"     append.m_field12 = "CIMG"     append.m_exp12 = "@BLANKBOARD->CIMG"     append.m_case13 = "EITHER"     append.m_field13 = "DIMG"     append.m_exp13 = "@BLANKBOARD->DIMG"     append.m_case14 = "EITHER"     append.m_field14 = "EIMG"     append.m_exp14 = "@BLANKBOARD->EIMG"     append.m_case15 = "EITHER"     append.m_field15 = "FIMG"     append.m_exp15 = "@BLANKBOARD->FIMG"     append.m_case16 = "EITHER"     append.m_field16 = "GIMG"     append.m_exp16 = "@BLANKBOARD->GIMG"     append.m_case17 = "EITHER"     append.m_field17 = "HIMG"     append.m_exp17 = "@BLANKBOARD->HIMG"     append.m_case18 = "EITHER"     append.m_field18 = "AN"     append.m_exp18 = "@BLANKBOARD->AN"     append.m_case19 = "EITHER"     append.m_field19 = "BN"     append.m_exp19 = "@BLANKBOARD->BN"     append.m_case20 = "EITHER"     append.m_field20 = "CN"     append.m_exp20 = "@BLANKBOARD->CN"     append.m_case21 = "EITHER"     append.m_field21 = "DN"     append.m_exp21 = "@BLANKBOARD->DN"     append.m_case22 = "EITHER"     append.m_field22 = "EN"     append.m_exp22 = "@BLANKBOARD->EN"     append.m_case23 = "EITHER"     append.m_field23 = "FN"     append.m_exp23 = "@BLANKBOARD->FN"     append.m_case24 = "EITHER"     append.m_field24 = "GN"     append.m_exp24 = "@BLANKBOARD->GN"     append.m_case25 = "EITHER"     append.m_field25 = "HN"     append.m_exp25 = "@BLANKBOARD->HN"     append.m_case26 = "EITHER"     append.m_field26 = "PIP"     append.m_exp26 = "@BLANKBOARD->PIP"     append.m_case27 = "EITHER"     append.m_field27 = "PIPB"     append.m_exp27 = "@BLANKBOARD->PIPB"     append.m_case28 = "EITHER"     append.m_field28 = "RANKV"     append.m_exp28 = "@BLANKBOARD->RANKV"     append.m_case29 = "EITHER"     append.m_field29 = "RANKVB"     append.m_exp29 = "@BLANKBOARD->RANKVB"     append.m_case30 = "EITHER"     append.m_field30 = "DBLD"     append.m_exp30 = "@BLANKBOARD->DBLD"     append.m_case31 = "EITHER"     append.m_field31 = "DBLDB"     append.m_exp31 = "@BLANKBOARD->DBLDB"     append.m_case32 = "EITHER"     append.m_field32 = "BLKD"     append.m_exp32 = "@BLANKBOARD->BLKD"     append.m_case33 = "EITHER"     append.m_field33 = "BLKDB"     append.m_exp33 = "@BLANKBOARD->BLKDB"     append.m_case34 = "EITHER"     append.m_field34 = "ISO"     append.m_exp34 = "@BLANKBOARD->ISO"     append.m_case35 = "EITHER"     append.m_field35 = "ISOB"     append.m_exp35 = "@BLANKBOARD->ISOB"     append.m_case36 = "EITHER"     append.m_field36 = "MOB"     append.m_exp36 = "@BLANKBOARD->MOB"     append.m_case37 = "EITHER"     append.m_field37 = "MOBB"     append.m_exp37 = "@BLANKBOARD->MOBB"     append.m_case38 = "EITHER"     append.m_field38 = "CAPTURE"     append.m_exp38 = "@BLANKBOARD->CAPTURE"     append.m_case39 = "EITHER"     append.m_field39 = "MOVEVALUE"     append.m_exp39 = "@BLANKBOARD->MOVEVALUE"     append.m_case40 = "EITHER"     append.m_field40 = "WHITEVAL"     append.m_exp40 = "@BLANKBOARD->WHITEVAL"     append.m_case41 = "EITHER"     append.m_field41 = "BLACKVAL"     append.m_exp41 = "@BLANKBOARD->BLACKVAL"     append.m_case42 = "EITHER"     append.m_field42 = "SCORE"     append.m_exp42 = "@BLANKBOARD->SCORE"     append.m_case43 = "EITHER"     append.m_field43 = "CHECK"     append.m_exp43 = "@BLANKBOARD->CHECK"     append.m_case44 = "EITHER"     append.m_field44 = "CHECKMATE"     append.m_exp44 = "@BLANKBOARD->CHECKMATE"     append.m_case45 = "EITHER"     append.m_field45 = "FIRSTMOVE"     append.m_exp45 = "@BLANKBOARD->FIRSTMOVE"     append.m_case46 = "EITHER"     append.m_field46 = "CASTLE"     append.m_exp46 = "@BLANKBOARD->CASTLE"     append.t_count = 0     a_tbl.append()     a_tbl.close()     script_play("CorralPieces") end if End

TblGrid is the table I use to build my Array of the chess board, so to start with a clean slate I need to blank the fields in the table. Next I run the script CorralPieces which fills in the capture corral for both white and black. Below is the result and the actual code.

CorralPieces Code:

'Date Created: 02-Mar-2017 11:23:59 AM 'Last Updated: 02-Mar-2017 11:23:59 AM 'Created By  : NLaws 'Updated By  : NLaws for i = 1 to 16 step 1     if i <= 8          eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Pawn White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     else if i = 9 .or. i = 10 then         eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Rook White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     else if i = 11 .or. i = 12 then         eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Knight White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     else if i = 13 .or. i = 14 then         eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Bishop White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     else if i = 15 then         eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Queen White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     else if i = 16 then         eval("BC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("King White Trans")+".png"         eval("topparent:BC"+Alltrim(str(i))+".Refresh()")     end if Next i  for i = 1 to 16 step 1     if i <= 8          eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Pawn Black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     else if i = 9 .or. i = 10 then         eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Rook Black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     else if i = 11 .or. i = 12 then         eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Knight Black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     else if i = 13 .or. i = 14 then         eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Bishop black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     else if i = 15 then         eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("Queen Black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     else if i = 16 then         eval("WC"+Alltrim(str(i))+".Default.Hbitmap.Bmpfile") = "[PathAlias.ADB_Path]\Engine\Images"+chr(92)+Alltrim("King Black Trans")+".png"         eval("topparent:WC"+Alltrim(str(i))+".Refresh()")     end if Next i parentform.Refresh_Layout()

This code is fairly simple, I set an array with 16 values and "i" represents the corral square location. When "i" equals the position value the image property for the square is assigned using the eval function.

This array runs twice once for White and once for Black.

If you look at the image you will notice a new button at the top of the board.

Begin

Once the player assigns pieces to the board, clicking begin tells the system to build the table grid and the analyze move table. We will examing that code in a minute. The image below shows a sample of a custom game.

The list box on the left shows what the program sees as available moves and looking at the layout of the board you can see it is correct. Now the question is how did I do it.

It starts with the placement of the piece on the board. The code is in a script oddly enough called 'PlacePiece' and is run in the onPush event of the board square selected. Here is the code.

'Date Created: 02-Mar-2017 01:33:16 PM 'Last Updated: 03-Mar-2017 11:30:04 AM 'Created By  : NLaws 'Updated By  : NLaws  cObjNm = Current_object() vMove = lower(Alltrim(eval(cObjNm+".Object.Name"))) rNbr = val(Right(Alltrim(vMove),1)) dim ni as N ni = val(Right(Alltrim(word(rtnPiece,3,":",1)),len(Alltrim(word(rtnPiece,3,":",1)))-2)) xPiece = expiece  t = table.open("tblgrid") t.fetch_goto(rNbr) MoveVal = val(Right(vMove,1)) t.change_begin()  if Left(word(rtnPiece,3,":",1),1) = "B" Then     if ni = 1 .or. ni = 2 .or. ni = 3 .or. ni = 4 .or. ni = 5 .or. ni = 6 .or. ni = 7 .or. ni = 8 then         eval("t."+Left(Alltrim(vMove),1)) = "W Pawn"     else if ni = 9 .or. ni = 10 then         eval("t."+Left(Alltrim(vMove),1)) = "W Rook"     else if ni = 11 .or. ni = 12 then         eval("t."+Left(Alltrim(vMove),1)) = "W Knight"     else if ni = 13 .or. ni = 14  then         eval("t."+Left(Alltrim(vMove),1)) = "W Bishop"     else if ni = 15  then         eval("t."+Left(Alltrim(vMove),1)) = "W Queen"     else if ni = 16  then         eval("t."+Left(Alltrim(vMove),1)) = "W King"     end if else     if ni = 1 .or. ni = 2 .or. ni = 3 .or. ni = 4 .or. ni = 5 .or. ni = 6 .or. ni = 7 .or. ni = 8 then         eval("t."+Left(Alltrim(vMove),1)) = "B Pawn"     else if ni = 9 .or. ni = 10 then         eval("t."+Left(Alltrim(vMove),1)) = "B Rook"     else if ni = 11 .or. ni = 12 then         eval("t."+Left(Alltrim(vMove),1)) = "B Knight"     else if ni = 13 .or. ni = 14  then         eval("t."+Left(Alltrim(vMove),1)) = "B Bishop"     else if ni = 15  then         eval("t."+Left(Alltrim(vMove),1)) = "B Queen"     else if ni = 16  then         eval("t."+Left(Alltrim(vMove),1)) = "B King"     end if end if  eval("t."+Left(Alltrim(vMove),1)+"Img") = xPiece t.change_end(.t.) t.close()     '____________________________________________ eval(rtnPiece+".default.hbitmap.bmpfile") = "[PathAlias.ADB_Path]\Engine\Images\Blank Trans.png"  dim list as C list = <<%a% A B C D E F G H %a% dim WPiece[8] as P WPiece.initialize_from_table("tblgrid")  for each xcol in List     dim i as N     FOR i = 1 to 8 step 1     if i = 5 .and. xcol = "A"         cObjNm = xcol+"_"+i     else         cObjNm = xcol+i     end if             if eval("WPiece["+i+"]."+xcol) = "Black King"         BKingPos = cObjNm     else if eval("WPiece["+i+"]."+xcol) = "White King"         WKingPos = cObjNm     end if             xPiece = eval("WPiece["+i+"]."+xcol+"Img")     eval(cObjNm+".default.hbitmap.bmpfile")  = "[PathAlias.ADB_Path]\Engine\Images\\"+xPiece+".png"     'eval(cObjNm+".Refresh()")     Next i Next        parentform.Refresh_Layout() xend:

There are two portions to this code first is to assign the selected piece to the selected square. When the player selects a piece in the corral the onPush code assigns image name and piece name to variables which are then passed to the script above. The variable rtnPiece is used to derive the integer value of our corral piece which is then assigned to our tblgrid. The second part of our script is the initializing of our board grid from our table then the form is refreshed showing the piece selected.

Below is the onPush code for our corral Piece.

'Date Created: 03-Sep-2014 12:33:49 PM
'Last Updated: 02-Mar-2017 01:40:57 PM
'Created By : cdc
'Updated By : NLaws
dim SHARED rtnPiece as C
dim SHARED expiece as C

if xCGame = "Custom" then  rtnPiece = current_object()  expiece = file.filename_parse(eval(rtnPiece+".default.hbitmap.bmpfile"),"n") else  rtnPiece = current_object()  if eval(rtnPiece+".default.hbitmap.bmpfile") = "[PathAlias.ADB_Path]\Engine\Images\Blank Trans.png"  goto xend  else   end if xend:

There is a second part to this onPush event as well which has to do with a pawn getting to the opponents home row. I took it out because I am in the middle of changing how it works and I did not want to confuse anyone.

The final piece we need to look at is the onPush event of our button 'Begin'

'Date Created: 02-Sep-2014 11:07:07 AM 'Last Updated: 07-Mar-2017 11:24:38 AM 'Created By  : cdc 'Updated By  : NLaws 'DIM Shared  PlayerTurn as c  DIM choice_list as c  dim SHARED CanMove as C choice_list = <<%txt% "White" "Black" %txt%  xMoveNbr = 1 MoveVal = 0 xDo = "Select" Gtype = "WHBH" xRate = "FBF" xPause = .t. RGM1 = "" RGM2 = "" RGM3 = "" RGM4 = ""  'ui_msg_box("","White Val: "+WhiteVal+crlf()+"black Val: "+BlackVal+crlf()+"____________"+crlf()+"Score   : "+xScore)  WCastleKS = .t. BCastleKS = .t. WCastleQS = .t. BCastleQS = .t. BCheckW = .f. WCheckB = .f. WKingPos = "E1" BKingPos = "E8" cPieceNm = "" xPiece = "" xMove = "" WCanMove = "" BCanMove = "" OrigPos = "" NewPOS = "" SelPiece = "" CurrPiece = "" Capture = "" OppCkLoc = "" tmove = "" vMove = "" AttackSqs = "" WAttackSqs = "" BAttackSqs = "" MoveList = "" WMoveList = "" BMoveList = "" WCanMove = "" BCanMove = "" CkList = "" PrevFrom = "" PrevMove = "" OPos = "" 'Zap piecemoves dim tablename as c  tablename = "piecemoves" dim prompt_for_confirmation  as l  prompt_for_confirmation = .f. 'check if the table exists dim table_filename as c  table_filename = table.filename_get(tablename) if file.exists(table_filename) = .f. then      ui_msg_box("Error","Table not found: '" + tablename + "'.",UI_STOP_SYMBOL) else     'check if table is in use     if table.in_use(table_filename) = .t. then          ui_msg_box("Error","Table '"+tablename+"' cannot be Zapped (i.e. emptied) because it is in use.",UI_STOP_SYMBOL)     else         dim tbl as p          dim flag_ok_to_zap as l          flag_ok_to_zap = .t.         if prompt_for_confirmation = .t. then              dim prompt_result as n              prompt_result = ui_msg_box("Warning","Are you sure that you want to Zap '"+tablename+"'?",ui_yes_no_cancel+ui_question_symbol)             if prompt_result <> ui_yes_selected then                 flag_ok_to_zap = .f.             end if          end if                   if flag_ok_to_zap = .t. then              tbl = table.open(tablename)             tbl.zap(.t.)             tbl.pack()             tbl.close()         end if      end if  end if  'Zap Capture Table tbl = table.open("capturetbl") tbl.zap(.t.) tbl.pack() tbl.close()  dim SHARED CanMove as C Playerturn = "White" script_play("ActiveCHPiece") xbasic_wait_for_idle() PlayerTurn = "Black" script_play("ActiveCHPiece") xbasic_wait_for_idle()   dim cnt as N dim xdata as C tam = table.open("an_move") tam.fetch_first() tam.batch_begin() while .not. tam.fetch_eof()     tam.change_begin()     tam.sqdata = ""     tam.wpiece = ""     tam.wfrom = ""     tam.wto = ""     tam.wprtk = ""     tam.wmoves = ""     tam.wattack = ""     tam.wstatus = "Open"     for each sqd in WCanMove         if Alltrim(tam.sq) = lower(Alltrim(word(sqd,2,":",1))) then             tam.sqdata = word(sqd,1,":",1)             tam.wStatus = "Control"             tam.wpiece = word(sqd,1,":",1)             tam.wfrom = word(sqd,2,":",1)             dim wm as C             wm = Right(Alltrim(word(sqd,3,":",1)),len(Alltrim(word(sqd,3,":",1)))-1)             tam.wmoves = Var->wm         end if         Cnt = Occurs(",",sqd)          for i = 1 to Cnt + 1             if tam.Sq = word(sqd,i+1,",",1)                 xdata = word(sqd,1,":",1)+":"+word(sqd,2,":",1)+"-"                 tam.sqdata = Alltrim(tam.sqdata)+Var->xdata+"&"             end if             Next i         Next     tam.change_end(.t.) tam.fetch_next()     end while tam.fetch_first() while .not. tam.fetch_eof()     tam.change_begin()     tam.bpiece = ""     tam.bfrom = ""     tam.bto = ""     tam.bprtk = ""     tam.bmoves = ""     tam.battack = ""     tam.bstatus = "Open"     for each sqd in BCanMove         if Alltrim(tam.sq) = lower(Alltrim(word(sqd,2,":",1))) then             tam.sqdata = word(sqd,1,":",1)             tam.bStatus = "Control"             tam.bpiece = word(sqd,1,":",1)             tam.bfrom = word(sqd,2,":",1)             dim bm as C             bm = Right(Alltrim(word(sqd,3,":",1)),len(Alltrim(word(sqd,3,":",1)))-1)             tam.bmoves = Var->bm         end if         Cnt = Occurs(",",sqd)          for i = 1 to Cnt + 1             if tam.Sq = word(sqd,i+1,",",1)                 xdata = word(sqd,1,":",1)+":"+word(sqd,2,":",1)+"-"                 tam.sqdata = Alltrim(tam.sqdata) + Var->xdata             end if             Next i         Next     tam.change_end(.t.) tam.fetch_next() end while tam.batch_end() tam.close()  dim defaultValue as c  defaultValue = "\"White\"" PlayerTurn = ui_get_list2("Select Player to Start",defaultValue,choice_list,1,.t.) xDo = "Select" BPip = tablesum("an_move",".t.","PiiecesB") WPIP = tablesum("an_move",".t.","Piecesw") BMob = tablesum("an_move",".t.","MobB") WMob = tablesum("an_move",".t.","MobW") BBrdRank = tablesum("an_move",".t.","RankB") WBrdRank = tablesum("an_move",".t.","RankW") WPVal = tablesum("an_move",".t.","PValW") BPVal = tablesum("an_move",".t.","PValB") BFork = tablesum("an_move",".t.","BFork") WFork = tablesum("an_move",".t.","WFork") WCheck = tablesum("an_move",".t.","WCheck") BCheck = tablesum("an_move",".t.","BCheck") WhiteVal = WPip+WMob+WBrdRank+WPVal+WFork+WCheck BlackVal = BPip+BMob+BBrdRank+BPVal+BFork+BCheck xScore = WhiteVal-BlackVal text1.text = "New Game, Thanks for Waiting, It's "+PlayerTurn+"'s Move" xCGame = "CustomPlay"

I am not going to review all the pieces of this script. All you need to know is it takes the initial piece move analysis and fills out the analysis table allowing the game to progress as if it reached the current state by playing from the beginning of a game. Working out this routine has been a revelation. My original code for starting a new game and maintaining the pieces on the board as the game progressed is huge; well over 5000 lines of code. Using the approach above I believe I can widdle the code down to less than a 1000 lines of code. Cool!

That's all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

February 27 2017

This past weekend I once again spent time working on my chess program. As you may recall, if you have been following along, I last discussed using collections as a means of determining the attack path and squares of white to determine the best possible defense against said attack. I discovered two things.

• The collection can be replaced with a compound case statement
• As the game wore on, processing time increased due to the growing number of choices in moves.

Lets look at the compound case statement.

I am calling the attack piece AP and the piece being attacked as PA

Also remember that it is blacks move so PA is black and AP is white. Now lets say AP is a Bishop on square A4 and it is attacking Blacks Queen on E8. If Black's Queen can take the Bishop without reprisal then the software does that with no issue. In this case however that is not the case so we need to know which squares are between A4 and E8

Here is my statement

If Left(AP,1)  < Left(PA,1) .and. Right(AP,1) < Right(PA,1) then

'We know AP is to the lower right of PA and attacks at a diagonal.

else if Left(AP,1)  > Left(PA,1) .and. Right(AP,1) > Right(PA,1) then

'We know AP is to the upper right of PA and attacks at a diagonal.

etc..

So all I actually need to solve for is the relationship as position of the attack piece to the piece under attack the the software fills in the blanks building my list of squares black needs to look at. Once that is known the code steps through the list looking for blocks, attacks or protection of the attacked piece.

Now lets look at the growing processing time based on the number of moves and squares to address. As I sat and twiddled my thumbs watching the computer clunk along, I though what if I created a button on the board which when clicked would allow the player to make black's move and learn it for future games. I know that for example computers are not good with blitz games or sacrifices, whereas human players can spot those opportunities fairly easily. So if I make the move for black, I want black to learn the move so the next time it is encountered, black will make the move quickly rather than looking at all possible options.

The image below is of a finished game using my new routine. I started making the moves for black at move 7 and the computer made the next three moves based on white's moves rather quickly. The rest of the moves I made and as you can see black won.

After completing the game, I replayed the game entering the same moves for white and black responded beautifully and quickly giving the same result with black averaging about 3 seconds per move. Needless to say I was very happy.

Here is the code to learn the move.

'Date Created: 21-Nov-2014 11:28:36 AM
'Last Updated: 27-Feb-2017 10:41:49 AM
'Created By  : cdc
'Updated By  : NLaws
parentform.commit()

t = table.open("ob_header")
t.enter_begin()
t.obnbr = 99
t.booktype = "Learning"
t.event = "Game"
t.site = "Desktop"
t.date = Date()
t.round = 1
t.white = "Human"
t.balck = "TakeCharge"
t.resullt = "0"
t.enter_end()
t.close()
rNbr = 99

pm = table.open("piecemoves")
pm.fetch_last()
r_Nbr = pm.recno()
pm.close()

a_tbl = table.open("openmoves")
append.t_db = "piecemoves"
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = "recno() = Var->r_Nbr"
append.type = "All"
append.m_count = 16
append.m_field1 = "MOVENBR"
append.m_exp1 = "@PIECEMOVES->MOVENBR"
append.m_field2 = "WHITEVAL"
append.m_exp2 = "@PIECEMOVES->WHITEVAL"
append.m_field3 = "BLACKVAL"
append.m_exp3 = "@PIECEMOVES->BLACKVAL"
append.m_field4 = "SCORE"
append.m_exp4 = "@PIECEMOVES->SCORE"
append.m_field5 = "WPIECE"
append.m_exp5 = "left(word(@Piecemoves->Whitemove,1,\":\",1),-4)"
append.m_field6 = "BPIECE"
append.m_exp6 = "left(word(@Piecemoves->Blackmove,1,\":\",1),-4)"
append.m_field7 = "WMVFRMA"
append.m_exp7 = "left(alltrim(word(@Piecemoves->Whitemove,2,\":\",1)),-2)"
append.m_field8 = "BMVFRMA"
append.m_exp8 = "left(alltrim(word(@Piecemoves->Blackmove,2,\":\",1)),-2)"
append.m_field9 = "WMVTOA"
append.m_exp9 = "lower(alltrim(word(@Piecemoves->Whitemove,3,\":\",1)))"
append.m_field10 = "BMVTOA"
append.m_exp10 = "lower(alltrim(word(@Piecemoves->Blackmove,3,\":\",1)))"
append.m_field11 = "Gamemvs1"
append.m_exp11 = "@PIECEMOVES->TC_CMV1"
append.m_field12 = "Gamemvs2"
append.m_exp12 = "@PIECEMOVES->TC_CMV2"
append.m_field13 = "Gamemvs3"
append.m_exp13 = "@PIECEMOVES->TC_CMV3"
append.m_field14 = "Gamemvs4"
append.m_exp14 = "@PIECEMOVES->TC_CMV4"
append.m_field15 = "OB_Nbr"
append.m_exp15 = "Var->RNbr"
append.m_field16 = "CurrentBoard"
append.m_exp16 = "@PIECEMOVES->CURRENTBOARD"
append.t_count = 0
a_tbl.append()
a_tbl.close()

The key here is not to learn each move in the game so far, only the last move based on the current board layout.The table PieceMove records each move in a game as a single record, so I open the table and fetch the last record assigning the record number to a variable r_Nbr. Next I run an append for just that record in the PieceMove table using r_Nbr as the filter. Now when a game is played, the software always checks for the current board piece layout before calculating new moves. When it see's a matching image, it makes that move rather than computing all possible moves. If you think about it, that is exactly what a human does. The more games they play the more piece layouts they recognize and make their moves accordingly. Mission accomplished!

The next thing I want to do is add a control which will allow the player to set up custom board piece positions. This way they can recreate screens they have seen on the web and practice solutions. Then they can teach the program only the best moves. Now that will be cool.

That's all for today. If you own a business and need help with your Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

February 24, 2017

Hello everyone. In my last post I demonstrated how to create an Alpha software app using xBasic code and how to attach an external library to your current app. Today I want to continue along those lines and look at the new process for building the code library and using the Project Viewer to display and organize the Project script library.

Let's start by looking at the code used to build our script table. Once I connect the code library to our Project Manager. I open the Project Tracker form. Next I select the project I just linked to our Project Manager then click update. (see image below)

Update will build a new temp table of all scripts in the project manager including the scripts from TC Games. It then appends the temp table to our actual table using unique replace existing updating any changes to scripts already stored as well as adding new ones. Here is the code.

'Date Created: 22-Feb-2017 07:47:25 AM 'Last Updated: 23-Feb-2017 09:14:05 AM 'Created By  : NLaws 'Updated By  : NLaws 'parentform.commit() tbl = table.open("tmpscriptandfncts") tbl.zap(.t.) tbl.close() dim xAppID as C xAppID = App_ID.text  dim list1 as C dim xf as C list1 = a5.script_Enum(4) t = table.open("tmpscriptandfncts") t.fetch_first() for each scr in list1     t.enter_begin()     xf = file.filename_parse(word(Var->scr,2,"@",1),"n")     t.sf_appname = Var->xf     t.sf_name = Right(word(scr,1,"@",1),len(word(scr,1,"@",1))-7)     if t.sf_appname = "tc_app_mgr" then         t.sf_id = "00001"     else         t.sf_id = xAppID         end if     t.sf_location = word(scr,2,"@",1)     t.sf_type = "SCRIPT"     t.sf_code = script_load(Right(word(scr,1,"@",1),len(word(scr,1,"@",1))-7))     t.enter_end() next    t.close()  DIM Append as P  a_tbl = table.open("scrptandfncts") append.t_db = "tmpscriptandfncts" append.m_key = "Sf_ID+SF_NAME" append.t_key = "Sf_ID+SF_NAME" append.m_filter = "" append.t_filter = "" append.type = "Unique, replace existing" append.m_count = 6 append.m_case1 = "EITHER" append.m_field1 = "SF_ID" append.m_exp1 = "@TMPSCRIPTANDFNCTS->SF_ID" append.m_case2 = "EITHER" append.m_field2 = "SF_NAME" append.m_exp2 = "@TMPSCRIPTANDFNCTS->SF_NAME" append.m_case3 = "EITHER" append.m_field3 = "SF_TYPE" append.m_exp3 = "@TMPSCRIPTANDFNCTS->SF_TYPE" append.m_case4 = "EITHER" append.m_field4 = "SF_CODE" append.m_exp4 = "@TMPSCRIPTANDFNCTS->SF_CODE" append.m_case5 = "EITHER" append.m_field5 = "SF_LOCATION" append.m_exp5 = "@TMPSCRIPTANDFNCTS->SF_LOCATION" append.m_case6 = "EITHER" append.m_field6 = "APP_NAME" append.m_exp6 = "@TMPSCRIPTANDFNCTS->SF_APPNAME" append.t_count = 0 a_tbl.append() a_tbl.close()

It takes just a minute to run and when done the user simply removes the Code Library from the Project Manager Application.

The original routine I wrote for this was close to a 1000 lines of code this is 64. Big difference. So now I have my scripts and what do I do with it. If you have been programming in Alpha Software for any length of time you may remember the Code Library Document Writer they sold (which did not work great). I now do the same using

• file.create()
• file.open
• file.seek
• file.write_line
• memo_write_to_file(filename, MEMO_APPEND)
• file.close

Before I show you how it's done lets look at the Project Viewer.

Now when the viewer loads, the Projects are listed in the right pane of the left panel. The user selects a project then clicks 'Code Library' in the left pane. This redisplays the right pane to include all scripts for the selected project just appended to the script table. Then the user selects a script and it is displayed in the right panel. Here is the protion of the code on the onChange event of the vsrlist object which displays the scripts for the selected project.

else if vSrList = "-Code Library" then     DocView  = DocView + crlf() +"_______________________"+ crlf() \     + "Code Library Scripts"+ crlf() + table.external_record_content_GET("scrptandfncts", "SF_ID +':' + SF_Name", "SF_Name","SF_ID = '"+word(Var->mySelect,1,":",1)+"'")

At the botton of the onchange event the watch variable for vStartDir is changed causing the pane to reload with the new values.

The onChange code for displaying the selected script is

else if vSrList = "-Code Library" then     if left(mySelect,1) = "0" then              else         CName = mySelect     end if     dim t as P     dim c_ID as C     dim cMemo as C     c_ID = ""     c_ID = word(vStartDir.value,1,":",1)     sf = table.open("scrptandfncts")     sf.fetch_first()     while .not. sf.fetch_eof()         if sf.sf_id+":"+sf.sf_Name = vStartDir then             CMemo = sf.sf_code             f = table.open("filemanager")             f.change_begin()             f.codememo = sf.sf_code             f.change_end()             f.close()         end if     sf.fetch_next()     end while     sf.close()

Now if you look at the image above, on the left side above the viewer pane are two buttons.

• Write Script to file
• Write Code Library to file

The first sends the memo field on our viewer to a file using the file function methods mentioned above and the second writes all scripts in our script table to a file. Below is the code for the OnPush event of both

Write Script to file

'Date Created: 24-Feb-2017 07:43:42 AM 'Last Updated: 24-Feb-2017 07:56:08 AM 'Created By  : NLaws 'Updated By  : NLaws dim tbl as P dim fptr as P tbl = table.current() 'Save current record if not in view mode. if (tbl.mode_get()<> 0) then     'parent.commit() end if tbl.fetch_first() filename = A_DB_CURRENT_PATH + "memos.txt" fptr = file.create(filename, FILE_RW_EXCLUSIVE) fptr.close() while .not. tbl.fetch_eof()     fptr = file.open(filename, FILE_RW_EXCLUSIVE)     'Next 2 lines position insertion point at end of file     size = fptr.bytes_get()     fptr.seek(size)     fptr.write_line("")     fptr.write_line("Program ID: " + word(mySelect,1,":",1)+ " - Script Name: " + word(mySelect,2,":",1))      fptr.write_line("___________________________________________")     fptr.write_line("")     fptr.close()     tbl.codememo.memo_write_to_file(filename, MEMO_APPEND)     tbl.fetch_next() end while sys_open(filename)

This code creates this text file.

Write Code Library to file

'Date Created: 24-Feb-2017 07:43:42 AM 'Last Updated: 24-Feb-2017 07:56:08 AM 'Created By  : NLaws 'Updated By  : NLaws dim tbl as P dim fptr as P tbl = table.current() 'Save current record if not in view mode. if (tbl.mode_get()<> 0) then     'parent.commit() end if tbl.fetch_first() filename = A_DB_CURRENT_PATH + "memos.txt" fptr = file.create(filename, FILE_RW_EXCLUSIVE) fptr.close() while .not. tbl.fetch_eof()     fptr = file.open(filename, FILE_RW_EXCLUSIVE)     'Next 2 lines position insertion point at end of file     size = fptr.bytes_get()     fptr.seek(size)     fptr.write_line("")     fptr.write_line("Program ID: " + word(mySelect,1,":",1)+ " - Script Name: " + word(mySelect,2,":",1))      fptr.write_line("___________________________________________")     fptr.write_line("")     fptr.close()     tbl.codememo.memo_write_to_file(filename, MEMO_APPEND)     tbl.fetch_next() end while sys_open(filename)

Which creates this file

Pretty cool. Now you can build a printed library of all code in your Projects which will come in very handy in 5 years when an old customer calls and says they had a failure in the app you created for then. If the code changed and you did not write the change you know they hired someone who did not know what they were doing.

Time to start enjoying the weekend.

That's all for today. If you are a business and need help with an Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

February 20, 2017

Hello everyone it's Monday the start of a new and exciting week. I hope everyone had a nice weekend.  Last week I had Jury Duty so not much got done around the office. Today I am going to continue working on the Project Manager Application. One of my original goals is to have the ability to track a new application development from beginning to end including the creation of the new app.

Below is an image of the A5 files for an app I created as a test in the Project Manager (NickApp).

There is no function in xBasic (at least that I can find.) which will let you create and app using xBasic. I did however, come up with a solution. Using file.create I create a file with the name I want for my app and save it with the extension of 'adb'. I then open the file using sys_shell which allows me to point to A5 v10 and pass the command line of my new app file. Alpha Software does the rest. Below is the Select Database dialog which shows the NickApp I just created,

and finally here is the empty control panel for the NickApp.

Below is the code

if vSrList = "A5 App's" then     DocView  = table.external_record_content_GET("applicationlist","File_Name","File_Name","ID=3") else if vSrList = "-New A5 App" then     filename = "c:\temp\NickApp.adb"     result = file.exists(filename)     if result = .F. then         file_pointer = file.create(filename, FILE_RW_EXCLUSIVE)     end if     dim useshell as C     useshell = "C:\Program Files\a5V10\alpha5.exe"         CLine = useshell + " " +  filename         sys_shell(CLine,1) end if

If you look at the code you can see I hard coded the path and folder name. The finished product will use a dialog box to prompt for the path and folder name and will write the result to our applicationlist table which will automatically update our menu.

Now this code is actually part of a larger script which is attached to the on change event of a list object on our new project viewer form.

On the left above is the form in design mode and to the right is the same form in view mode. When the user clicks a button on the custom toolbar the list called vsrlist is updated by the onPush code for the selected button. As an example, here is the code for our A5 App button.

ListState = "ADB" ui_freeze(.t.) dim SHARED SubjectList as C hBody.text = "Alpha Software App Viewer"+"<br>"+"No Application Selected" xbasic_wait_for_idle() DocView = <<%str% Select A5 Apps to See a List of current A5 Applications %str% parentform.commit() SubjectList = "" SubjectList = "A5 App's"+crlf()+"-New A5 App"+crlf()+"-Edit A5 App"+crlf()+"-Remove A5 App"+crlf()+"A5 App Controller"+crlf()+"-Tables-Sets"+crlf() if vwatch =1 then    vwatch = 0 else if vwatch = 0 then     vwatch = 1 end if ui_freeze(.f.)

I use the choices tab for my form object to populate the list variable (SubjectList) and update the object (vSRList) by changing the value of my watch variable.

Once the list is created, the user simply selects a choice on the left to

• Change the panel on the right
• Create a new App or Data Record
• Edit an App or Data Record
• Delete an App or Data Record

and if they select a choice on the right panel the HTML memo field displays data related to their selection. This should work well as a code viewer for our project manager.

If you think this form looks familiar you are correct. I created it in a lesson

Incorporating xBasic into Form & HTML Document Design: 12

which showed how to pass data values to a HTML memo field for display on a form. If you want a refresher or if you have not seen the previous lesson, then click the link above. I will not be reviewing all the code again for this process.

When I started this project manager app I created a fairly complex script for compiling and building a table of all scripts in the Project Manager Application list. After looking at what Alpha Software provides each developer as a means of building a code library I am changing my approach. Alpha Software allows you to add the code from any application to any other applciation. Look at the two images below.

The first image displays where to go to add scripts to an application and the second image shows the dialog box which allows you to do it. Once the code libraries are added I can run the portion of the previous code which writes the scripts to memo fields building a table of code records which can now be displayed in our HTML Viewer. We will look at that in the next session.

That's all for today. If you are a business and need help with an Alpha Software program, contact us. Are rates are reasonable and our work is guaranteed.

Phone:713 417-6831

EMail: NLawson@cdc-takecharge.com

Alpha Anywhere How to Write Formula in Report

Source: https://cdctakecharge.wordpress.com/

Share this post