Click here to Skip to main content
Click here to Skip to main content

Corel Paradox Search Technique

By , 12 Sep 2012
Rate this:
Please Sign up or sign in to vote.

Introduction

I have worked with the Corel Paradox database system for 14 years. It has been a solid, dependable platform for creating screens, queries, and reports for all sorts of business office applications.

I recently encountered a problem with a Corel Paradox inventory report I made for a customer. They already had a working report for this application. However, they wanted it to be redesigned with a user friendly screen interface for accepting full or partial company part numbers. They didn't want to have to keep going into the design mode of the report and entering criteria in the report's "filter" section and then switching to run mode to execute it.

And Here Are The Details of the Problem

This report used a master part inventory data table that sequentially looped through about 27,000 records. With each successive loop it went into another data table that contained records denoting inventory additions and subtractions for individual company part numbers. This inventory transaction table had a very large number of records - about 504,000. Although the accumulated sums representing the on hand quantity for each company part number were accurate, the processing was very slow. The company part number field was not keyed or indexed, so I had to rely on slow sequential data comparison. This slow, but accurate method of tabulating the on hand quantity for each of the company part numbers in the report was essentially useless to the customer. This was because they had to run this report numerous times throughout the day and the amount of time it took was not practical in a fast paced business environment.

Further, I initially had used a simple query on the inventory transaction table that held half a million records. This was before I redesigned the report to work with objectPAL (object oriented Paradox Application Language). The query results were inaccurate versus what was actually in the data table, so this approach was also of no use to the customer.

Next, A Solution

I devised a way to solve this problem. Each time the master part inventory data table looped around, it would apply a new filter on the huge inventory transaction table. This "filter" used the company part number from the master part inventory data table. I used the objectPAL setGenFilter() to do this. For each loop around the master part inventory data table, the setGenFilter(dyn) used the criteria in the "dyn" dynarray structure to create an objectPAL TCursor on the inventory transaction data table for a specific part number supplied by the master part inventory data table. Here is a patch of code from the program that illustrates this approach:

		;// use 'dyn' dynarray and objectPAL setGenFilter() to apply a filter from the
		;// master part number table to the part number transactions table. This will 
		;// involve assigning the part number from the tc2 Tcursor for the part number
		;// field to the dynarray data structure.
		dyn["6characterpart#"] = tc2."6characterpart#"

		;// apply the setGenFilter() in conjunction with the ‘dyn’ dynarray
		;// structure to create a filtered tc3 Tcursor on the huge parts transaction data table.
		tc3.setGenFilter(dyn)

		;// initialize accumulated quantity variable 'Qtyx' to 0 prior to calculating
		;// on hand quantity for the specified part number for this loop iteration.
		Qtyx = 0

			;// move the parts transactions table’s TCursor record pointer to the beginning 
			;// of the now filtered parts transactions table.
			tc3.home()
			;// begin while loop for tc3 Tcursor records (parts transaction table).
			while NOT tc3.eot()

				;// accumulate the on hand quantity figure for the master part number field if
				;// it is equal to the transaction part number field in the filtered tc3 TCursor.
				;// actually we don't even need this 'if....endif' structure since all the 
				;// part numbers have already been filtered for the master part number in the 
				;// current position in the master part number table. Aha! we get even more
				;// processing speed!
				if tc3."6characterpart#" = tc2."6characterpart#" then
				Qtyx = Qtyx + tc3."Quantity"
				endif

			;// advance the transaction part number tc3 TCursor to the next record.
			tc3.nextRecord()

			;// end while loop for tc3 Tcursor records (parts transaction table).
			endWhile
 

This made the sequential on hand quantity calculation go much faster because the number of records it had to process for each part number was relatively small thanks to the filter that was applied to the inventory transactions table with each successive loop. It yielded accurate results and the new report processing completed after only a few minutes. Here is the Paradox search code I made.

Conclusion

The point of the story is just because software works to yield accurate results that does not mean it also works for the customer. The software solution must also be timely since business people have limited time to complete their tasks. If you would like to learn more about my services, then please visit my software developer website.

;// begin pushbutton method and start declaration of variables and objects.
method pushButton(var eventInfo Event)     				
var
	dyn      	        DynArray[] AnyType	;// declare dynarray array variable.
	MasterPartMatch    	Smallint	  	;// declare small integer.
	Qtyx              	Longint	        	;// declare long integer.
	tblVar            	Table	        	;// declare table object.
	tc3,
	tc,
	tc2               	Tcursor	        	;// declare Tcursor objects.
	Varx,
	stPart1,           
	strRetVal,
	strReportName		String	               	;// declare string variables.
	rHandle	Report	                        	;// declare handle to report.
	rpiHandle		ReportPrintInfo	       	;// declare handle to print info structure.
;// end declaration of variables and objects.
endVar

;// preliminary information:
;// ORDERINGDEPT is a Paradox alias which is a made up name that refers

;// to a folder location like C:\TESTFOLDER, F:\ANYFOLDER, etc. aliases are
;// created on the Paradox toolbar under the alias manager. a Tcursor in Paradox
;// refers to a collection of data table records in much the same way as a MS Access recordset.

;// attempt to file lock the data table under the ORDERINGDEPT alias area that will fill up with
;// queried data from the programming iterations, ':ORDERINGDEPT:partreporttbl.db'.
tblVar.attach(":ORDERINGDEPT:partreporttbl.db")

;// if the ':ORDERINGDEPT:partreporttbl.db' table has been locked then proceed.
if tblVar.lock("Write") then

	;// prompt for full or partial part number from the user.
	stPart1 = "?"
	stPart1.view("Part Num (Full or Partial and ..): ")
  
	;// if something was entered for the part number then proceed.
	if stPart1 <> "?" then

	;// turn on the hour glass mouse cursor.
	setMouseShape(MouseWait,TRUE)

		;// open TCursors on ':ORDERINGDEPT:partreporttbl.db' the table that is populated with
		;// extracted data; ':ORDERINGDEPT:partlistmaster.db' is the master part number table;
		;//':ORDERINGDEPT:partlisttransactions.db' is the part number transactions table.
		;// all three Tcursors must successfully open to advance beyond this logic structure and
		;// move ahead with the processing.
		if tc.open(":ORDERINGDEPT:partreporttbl.db") And tc2.open(":ORDERINGDEPT:partlistmaster.db")
		And tc3.open(":ORDERINGDEPT:partlisttransactions.db") then

		;// empty out ':ORDERINGDEPT:partreporttbl.db' data table.
		tc.empty()


			;// if two periods (objectPAL wild card characters for any sequence of characters) were entered
			;// by the screen operator at the end of the part number data then remove them for the purpose
			;// of data comparison operations.
			if stPart1.substr(stPart1.size(), 1) = "." And stPart1.substr(stPart1.size()-1, 1) = "." then
			stPart1 = stPart1.substr(1, stPart1.size()-2)
			endif

;// move TCursor record pointer to the beginning of the master part number table. Initialize
;// 'Varx' variable to "ZZZZZZ" before commencing Tcursor data extraction operations. The
;// 'Varx" variable is used to make sure each iteration through the master part number table
;// is for a unique part number. this is because there can be several sequences of the same
;// master part number. the master part number table is keyed or indexed by the part number
;// and sequence number fields. the same part number can be listed each having different
;// sequence numbers. the part numbers in the part number transactions table are not keyed.
tc2.home()
Varx = "ZZZZZZ"

;// loop sequentially through the master part number table until TCursor has reached the end of file.
while NOT tc2.eot()

;// set ' MasterPartMatch ' variable to 0. this is set to 1 if it is matched in the master part number table using
;// the criteria below.
MasterPartMatch = 0

	;// if the 6 character part number in the master part number table is matched to the inputted part number while
	;// comparing the two for the length in characters of the inputted part number and the master part number is not 
	;// marked obsolete, then proceed. this is the start of if-endif logic structure “a”.
	if ( tc2."6characterpart#".substr(1, stPart1.size()) = stPart1 ) then
		if ( tc2."Obsolete" = "N" ) then

		;// set ' MasterPartMatch ' variable to 1 to denote a match in the master part number table.
		MasterPartMatch = 1

		;// use 'dyn' dynarray and objectPAL setGenFilter() to apply a filter from the
		;// master part number table to the part number transactions table. This will 
		;// involve assigning the part number from the tc2 Tcursor for the part number
		;// field to the dynarray data structure.
		dyn["6characterpart#"] = tc2."6characterpart#"

		;// apply the setGenFilter() in conjunction with the ‘dyn’ dynarray
		;// structure to create a filtered tc3 Tcursor on the huge parts transaction data table.
		tc3.setGenFilter(dyn)

		;// initialize accumulated quantity variable 'Qtyx' to 0 prior to calculating
		;// on hand quantity for the specified part number for this loop iteration.
		Qtyx = 0

			;// move the parts transactions table’s TCursor record pointer to the beginning 
			;// of the now filtered parts transactions table.
			tc3.home()
			;// begin while loop for tc3 Tcursor records (parts transaction table).
			while NOT tc3.eot()

				;// accumulate the on hand quantity figure for the master part number field if
				;// it is equal to the transaction part number field in the filtered tc3 TCursor.
				;// actually we don't even need this 'if....endif' structure since all the 
				;// part numbers have already been filtered for the master part number in the 
				;// current position in the master part number table. Aha! we get even more
				;// processing speed!
				if tc3."6characterpart#" = tc2."6characterpart#" then
				Qtyx = Qtyx + tc3."Quantity"
				endif

			;// advance the transaction part number tc3 TCursor to the next record.
			tc3.nextRecord()

			;// end while loop for tc3 Tcursor records (parts transaction table).
			endWhile

		;// go to the end of the table that is populated with extracted data and append a new record. assign
		;// values from the master part number table for the current record position to fields in
		;// ':ORDERINGDEPT:partreporttbl.db' including the calculated on hand quantity for this master part
		;// number from the above operation. enable edit mode before the append operation and toggle 
		;// back to non-edit mode after data assignments are complete.
		tc.edit()
		tc.end()
		tc.insertAfterRecord()
		tc."6characterpart#" = tc2."6characterpart#"
		tc."Description" = tc2."Description"
		tc."Reorder pt" = tc2."Reorder pt"
		tc."Quantity" = Qtyx
		tc.endEdit()

		;// end of if-endif logic structure “a”.
		endif
	endif

		;// this next patch of code will skip through the master part number table while the part number is
		;// identical to the one that was just processed. when it reaches the next new one in the part number
		;// keyed sequence, it will assign it to the 'Varx' variable. the loop then swings around to repeat the 
		;// processing until we are at the end of table for the master part number table. note that the
		;// 'MasterPartMatch' variable must be 1 in addition to the part number being equal to the one just
		;// processed for this while loop to iterate.
		tc2.nextRecord()
		while NOT tc2.eot() And Varx = tc2."6characterpart#" And MasterPartMatch = 1
		tc2.nextRecord()
		endWhile
			if NOT tc2.eot() then
			Varx = tc2."6characterpart#"
			endif

;// bottom of sequential loop through the master part number table.
endWhile

		;// end of if-endif logic structure for attempting to open all three Tcursors.
		endif

		;// close all three TCursors.
		tc.close()
		tc2.close()
		tc3.close()

			;// assign the name of the report to a report print info handle. use the report print info handle
			;// to create a report handle. preview the report with data supplied by the underlying
			;// ':ORDERINGDEPT:partreporttbl.db' data table.
			rpiHandle.name = ":ORDERINGDEPT:Partonhandquantity.rsl"
			rHandle.open(rpiHandle.name)
			rHandle.setTitle("Print Preview")
			rHandle.attach("Print Preview")

	;// end of if-endif logic structure to test for part number input.
	endif

	;// turn on the arrow mouse cursor.
	setMouseShape(MouseArrow,TRUE)

;// remove the file lock that was placed on the ‘:ORDERINGDEPT:partreporttbl.db'
;// data table to begin with.
tblVar.unlock("Write")


;// switch file lock if-endif structure if the table, ':ORDERINGDEPT:partreporttbl.db'
;// can’t be locked.
else


;// display error message if the ':ORDERINGDEPT:partreporttbl.db' data table can't be file locked at this time.
msgStop("Error", "':ORDERINGDEPT:partreporttbl.db' database is busy....try later...")


;// end of file lock if-endif structure
endif

;// end of pushbutton method.
endMethod

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

doug433
Software Developer DOUGLAS B. MILLER, COMPUTER PROGRAM DESIGNER
United States United States
No Biography provided
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 12 Sep 2012
Article Copyright 2012 by doug433
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid