I.T. Discussion Community!
-Collapse +Expand
Paradox
Search Paradox Group:

Advanced
-Collapse +Expand Paradox To/From
To/FromCODEGuides
-Collapse +Expand Paradox Store
PRESTWOODSTORE

Prestwood eMagazine

May Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDesktop Data...Paradox & Ob...ObjectPAL Co...OPAL: Reports   Print This     
Next Random Article Next Random Tidbit || Change Topic Random From All
  From the December 2014 Issue of Prestwood eMag
 
Paradox OPAL: Reports:
How I Generate a List of Top Customers for a Specific Sales Representative
 
Posted 6 years ago on 7/20/2013 and updated 9/16/2018
Take Away:

This is a useful report I made using the Paradox ObjectPAL coding to create a listing of top customers for a specific sales representative along with an order date range input. It’s a good insight into who your sales people are selling to and how much in a given time period.

KB102493



INTRODUCTION

There are many people in business who don’t have a very clear idea of what is really going on with their sales. You need to see what is actually occurring so you can make the necessary adjustments to keep things moving in a productive and profitable way. Application development is the best way to approach this problem.

I have created a very insightful report that shows what a sales representative is selling between an order date range. It will accumulate total sales amounts and total number of orders for each customer. Then it will sort those in descending order by amount sold (largest to smallest amount). Aside from its obvious use as a sales tracking and forecasting tool, it is also a great time saver versus doing the same thing manually.

THE INPUTS NEEDED TO FILTER THE REPORT

First, we need to prompt for the inputs that are needed to filter the Paradox data for the report. Below in Figure1 are the starting and ending order date prompts. Figure 2 shows the prompt for the sales representative’s initials.




Figure 1

 Figure 2

 

THE PROGRAMMING MECHANICS USED TO EXTRACT THE DATA RECORDS

After the prompts for receiving user input have been filled, the code will open Paradox ObjectPAL Tcursors to the orders data table, ":CUSTOMER:Orders_data.db" and the underlying data table for populating the report, ":CUSTOMER:ReportTable.db".

The ":CUSTOMER:ReportTable.db" data table is purged and then a “while loop” is begun. This will sequentially loop around the ":CUSTOMER:Orders_data.db" data table. If the sales representative is matched and the order date falls within the date range and the entire order has not been cancelled, then the current order record will be processed. The sales amounts and number of orders that meet the extraction criteria will be consolidated by customer.

After the data extraction is complete, the records contained in the ":CUSTOMER:ReportTable.db" data table will be sorted on the “Amount” field in descending order.

The narrated Paradox ObjectPAL code that does all this can be seen below.




method pushButton(var eventInfo Event)

var                                                                                          

                ;// declare all variables and objects.

                tblVar                                      Table

                daValidDate,

                stShpVar,

                stShpPrompt1,

                stShpPrompt2                        Date

                tc3,

                tc,

                tc2                                           TCursor

                stSales,

                stUserDate,

                stPartVar,

                strRetVal,

                strReportName                      String                      ;// handle to report name.

                rHandle                                  Report                    ;// handle to report being opened.

                rpiHandle                                               ReportPrintInfo      ;// handle to print info structure.

endVar

;// format erroneous date string to be used as a default date for the order date range

;// text boxes.

stUserDate = "11-11-39"

daValidDate = Date()

daValidDate = formatStringToDate(stUserDate, "DO(%M-%D-%Y)")

;// attach the underlying report data table, “:CUSTOMER:ReportTable.db”,

;// and attempt to do a table lock so no other users on the network can tamper

;// with it during the report processing.

tblVar.attach(":CUSTOMER:ReportTable.db")

if tblVar.lock("Write") then

                ;// prompt for the starting order date with the default date of 11/11/39 displayed

                ;// in the text box.

                stShpPrompt1 = daValidDate

                stShpPrompt1.view("Enter Start Date: ")

                ;// if the starting date is different from the default, then proceed to the next

                ;// prompt.

                if daValidDate <> stShpPrompt1 then

                                ;// prompt for the ending order date with the default date of 11/11/39

                                ;// displayed in the text box.

                                stShpPrompt2 = daValidDate

                                stShpPrompt2.view("Enter End Date: ")

                                ;// if the ending date is different from the default, then proceed to the

                                ;// next prompt.

                                if daValidDate <> stShpPrompt2 then

                                                ;// prompt for the sales rep’s initials, with “?” as a default in the

                                                ;// text box.

                                                stSales = "?"

                                                stSales.view("Enter Salesperson Initials: ")

                                                ;// if the sales rep’s initials are different than “?”, then proceed to

                                                ;// the processing.

                                                if stSales <> "?" then

                                                                ;// set the mouse cursor to an “hourglass” figure.

                                                                setMouseShape(MouseWait,TRUE)

;// open tcursors to the orders data tables as well as the underlying data

;// table that populates the  report.

if tc.open(":CUSTOMER:ReportTable.db") And tc2.open(":CUSTOMER:Orders_data.db") then

;// empty all records from the underlying data table for the report,

;// ":CUSTOMER:ReportTable.db". then set to edit mode.

tc.empty()                              

tc.edit()

                ;// sequentially loop around the orders data table until the end is reached.

                while NOT tc2.eot()

                                ;// for the current orders record, if the inputted sales rep is matched and

                                ;// the order date is trapped in between the inputted order date range and

                                ;// the order has not been cancelled, then proceed past this point. if any of

                                ;// these conditions are not true, then move to the next record in the orders

                                ;// data table.                          

if ( tc2."Salemn" = stSales And tc2."Orderdate" >= stShpPrompt1 And tc2."Orderdate" <= stShpPrompt2 And tc2."TotalOrderCanceleddate".IsBlank() = TRUE ) then

                                                ;// if any of the 3 detail lines in the order have not been

                                                ;// individually cancelled, then add the quantity multiplied by the

                                                ;// unit price to an accumulator variable, “Extx”.

                                                Extx = 0

                                                if tc2."FirstLineCancelDate".IsBlank() = TRUE then

                                                Extx = Extx + ( tc2."FirstLineQuantity" * tc2."FirstLineUnitPrice" )

                                                endif

                                                if tc2."SecondLineCancelDate".IsBlank() = TRUE then                          

                                                Extx = Extx + ( tc2."SecondLineQuantity" * tc2."SecondLineUnitPrice" )

                                                endif

                                                if tc2."ThirdLineCancelDate".IsBlank() = TRUE then

                                                Extx = Extx + ( tc2."ThirdLineQuantity" * tc2."ThirdLineUnitPrice" )

                                                endif

                                                                ;// if the customer code in the order record is not blank,

                                                                ;// then proceed.

                                                                if tc2."Custcode".IsBlank() = FALSE then

                                                                ;// if the customer code in the order record is matched in

                                                                ;// the underlying data table used to populate the report,

                                                                ;// then add the “Extx” accumulator sum to the “Amount” field

                                                                ;// matched record and increment the “NumOfOrders” field in

                                                                ;// the order record by 1.

                                                                if tc.locate("Custcode", tc2."Custcode") then

                                                                tc." Amount " = tc." Amount " + Extx

                                                                tc." NumOfOrders " = tc." NumOfOrders " + 1

                                                                else

                                                                ;// if the customer code in the order record is not matched in

                                                                ;// the underlying data table used to populate the report,

                                                                ;// then append a new record to the underlying report data table.

                                                                tc.end()

                                                                tc.insertAfterRecord()

                                                                tc."Custcode" = tc2."Custcode"

                                                                tc."Ship Date1" = stShpPrompt1

                                                                tc."Ship Date2" = stShpPrompt2

                                                                tc."Company" = tc2."Company"

                                                                tc."Salemn" = tc2."Salemn"

                                                                tc." Amount " = Extx                              

                                                                tc." NumOfOrders " = 1

                                                                endif

                                                                endif

                                endif

                ;// advance to the next sequential order record in the orders data table.

                tc2.nextRecord()

                endWhile

endif

;// turn off edit mode for the underlying data table for the report,

;// ":CUSTOMER:ReportTable.db".

tc.endEdit()

tc.close()

tc2.close()

                ;// now sort the :CUSTOMER:ReportTable.db data table in descending order by the

                ;// sales amount field.

                sort tblVar

                  on "Amount" D

                  to ":CUSTOMER:ReportTable_sort.db"

                endSort

                                ;// this loop will transfer sorted records from the

                                ;// ":CUSTOMER:ReportTable_sort.db" data table to the

                                ;// ":CUSTOMER:ReportTable.db" data table.

                                if tc.open(":CUSTOMER:ReportTable.db") And tc2.open(":CUSTOMER:ReportTable_sort.db") then

                                tc.empty()

                                tc.edit()

                                                while NOT tc2.eot()

                                                tc.end()

                                                tc.insertAfterRecord(tc2)

                                                tc2.nextRecord()

                                                endWhile

                                tc.close()

                                tc2.close()

                                endif

;// set up the report’s print preview window.

rpiHandle.name = ":PDOXPROJ:TopCustomersPerSalesman.rsl"

rHandle.open(rpiHandle.name)

rHandle.setTitle("Print Preview")

rHandle.attach("Print Preview")

                                                endif

                                endif

                endif

                                                                ;// set the mouse cursor back to an “arrow” figure.

                                                                setMouseShape(MouseArrow,TRUE)

;// unlock the underlying report data table, “:CUSTOMER:ReportTable.db”.

tblVar.unlock("Write")

else

;// display error message for not being able to lock the report’s underlying data table.

msgStop("Error", "‘:CUSTOMER:ReportTable‘ data table is busy....try later...")

endif

endMethod




Figure 3 below shows an example of the report’s print preview.

Figure 3

CONCLUSION

The report I have discussed is a good example of programming code in the Corel Paradox ObjectPAL platform that helps business people see what is really happening. There are an endless number of variations of software such as this that can help management see which sales representatives and customers are doing better than average in sales productivity.

Aside from my custom software services, I also offer computer repair services in my local area of Cleveland, Ohio USA as well as "fix my computer" advice for many of today’s difficulties.


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1235A1
Enter key:
Article Contributed By Douglas.M:

Please visit my software developer website for more information about my services. I offer application development as well as Android app coding services. My developer skills are best suited to dealing with custom software projects. I can perform programming for Corel Paradox as well as C# Sharp and PHP.

In my local area of northeast Ohio, I can cater to computer repair and "fix my computer" issues.

Use my contact web page today to reach me about any software design ideas you have.

Visit Profile

 KB Article #102493 Counter
7704
Since 7/20/2013
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech