Chapter 8 Define Data Fetcher

8.1  Overview

Data fetcher make up of SQL expression and for user to import his design logic into BC Excel Server. It refers to a

set of sequential query rules that is able to fetch qualified data or records from variously joined data tables. Also,

 it would return the results to the corresponded table cells according to default or customized filling criteria.

There are two kind of the data fetcher system in Excel Server: Data Fetcher and Data Writer. Data Fetcher is refer to draw

 the data from other report to current report. Data Write is to write back and update the data to the relevant report while

the current data from current report is updated. This Chapter is about to focus on the application of Data Fetcher and Data

 Writer with the case of a part of the sales system.

8.2  Data Fetcher

In chapter 6, the Customer Register From and Sales Order Form has been build and the customer’s information have been

acquired already. Therefore, it is possible to get the customer’s information from Customer Register From to fill in the customer

 information (customer code and contactor) of Sales Order Form.

The expression is:

While the customer’s name in Out of Sales Order Form is the same as the company name in Customer Register From, then get to

 information of customer code and contactor of Customer Register From to fill up the filed of customer reference and contactor of Sales Order Form

  Step One: Activate the data fetcher window by selecting the ‘define data fetcher’ on the template of Sales Order Form

                        Start to Define Data Fetcher in Sales Order Form

  Step Two: Select ‘New’ to add a new data fetcher expression.

                                  Data Fetcher Window

Step Three: Enter the data fetcher name ‘GetCustomerCodeNContactor’ and tick the fired occasion and click ‘Data Source’ to

import the source form.

                          Expression of Data Fetcher

Step Four: Select the source form CustomerRegistraterForm_Main and click ‘OK’

                                 Source Select

Step Five: Press the ‘Filter Conditions’ button to enter the activated condition of the expression.

Step Six:  Enter the expression

‘CustomerRegistrationForm_Main.Company = thisReport.Sheet1: Customer’

                            Expression of Condition

Step Seven: Select ‘Fill Methods’. The fill methods is about which target field will be filled and what will be filled in these field

Step Eight: Double click the Loading Data toward the target field ‘CustomerCode’

Step Night: Enter the expression ‘CustomerRegistrationForm_Main.Ref’

                     Fill Expression to the Target Field ‘Customer Code’

  Step Ten: Same method to fill the expression ‘CustomerRegistrationForm_Main.Contactor’

 to the target field ‘Contact’

                 Fill Expression to the Target Field ‘Contact’

  8.3  Test

Step One: Login ‘My Workbench’ and fill and new ‘Customer Register Form’

  Step Two: Enter ‘Cesoft’ to company name, ‘MS 01001’ to Ref., ‘Tao Wan’ to Contactor then save the report

                            Fill the new report

  Step Three: Back the ‘My Workbench’ and fill the new Sale Order Form

Step Four: Fill ‘Cesoft’ into the Customer field in sales order report and check what happened next

                                      Filling new sales order report

  Step Five: the code ‘MS 01001’ and Contact name ‘Tao Wan’ have been filled in automatically.

8.4  Data Writer

8.4.1   Case study

In Chapter Six, the ‘Customer Registration Form’ and ‘‘Sales Order Form’ has been built to deal with the customer order.

There is a simple requirement in Storage System: while the customer has ordered some products, the products have to

be shipped from storage. Hence, the number of the product in storage must be decrease. The case in this chapter is to

design the template to display the number of product in storage in real time. The ‘Out of Storage Form’ and ‘Product Register Form’

will be produced in this chapter to deal with the requirement.

Design:

The field ‘LastRenewDate’, ‘CurrentQuantity’, ‘CurrentAmount’ is the key filed in Product Register Form.

8.4.2   Implement

Preparation

Draw ‘Out of Storage Form’ and ‘Product Register Form’ (see Figure 7.1 and Figure 7.2) and define their data types (see Figure 7.3 and 7.4).

In this chapter, we will just focus on implementing the designing of the update of storage while the number of the product is changed. The

rest functions such as displaying the inventory history in Products Register Form will be ignored.

                                  Out of Storage Form

Data Type of Out of Storage_Main Form

Data Type of Out of Storage_Detail Form

                                  Product Register Form

Data Type of Product Register _Main Form

Data Type of Product Register _Detail Form

  Update while add new report of ‘Out of Storage Form’.

Step One: Open the template of ‘Out of Storage Form’ and build new Data Writer with ‘Update’

Step Two: Write down the Select ‘Save’ on the data write expression window.

  Step Three: Press the ‘Select Tables’ button

Step Four: Import the Product Register Form then click ‘ok’

Step Five: Select ‘Falter condition’

Step Six: Enter the expression ‘ProductRegister_Main.Code = thisReport.Sheet1:CodeDetail  And thisReport.Sheet1:Signature <>'’’ and click ‘ok’

Step Seven: Press ‘Set to Fields’ button

  Fill the ‘Method’ to the field of ‘LastRenewDate’, ‘CurrentQuantity’,’CurrentAmount’ of Product Register Form.

Step Eight: Double click the Loading data of ‘signature date’

  Step Nine: Enter the expression ‘thisReport.Sheet1: SignatureDate’ then click ‘ok’

Step Ten: Double click the Loading data of ‘CurrentQuantity’

  Step Eleven:

Enter the expression ‘ProductRegister_Main.CurrentQuantity - thisReport.Sheet1: Number’

Then click ‘ok’

  Step Twelve: Double click the Loading data of ‘CurrentAmount’

  Step Thirteen: Enter the expression

‘ProductRegister_Main.CurrentAmount - thisReport.Sheet1: TotalIncludeDiscount’

Then click ‘ok’

  The whole expressions are showed in the main data writer window.

  Normally, in data updating, there are adding and removing. So the data writer should come with a pair. Hence,

 after setting up the data write with the fired condition of ‘save’, we should also build the other one with the

condition of ‘delete or undo’ (see Figure 8.4.1 )

  In the case, the filter condition is the same as the ‘save’ one. However, we need to add the quantity and amount of

storage while the sales order has been removed.

                       Figure 8.4.1 Data writer with Deleted condition

                    Figure 8.4.2 Data writer—update the date

8.5  Test

Step One: Open a report of Product Register Form. In the example, you will fine the product ‘ 1’ in storage has been

 update to quantity in 100 and amount in 0 on 21/04/2009 . We will fill a new report of ‘Out of Storage Form’ to check

the changing of the product ‘ 1’ in storage.

  Step Two: Open a new report of ‘Out of Storage Form’

Step Three: Fill the report. Code->PRDT00007, Products->1, Number->80, Total(Include Discount)->20, then save the report.

  Step Four: Open the report of product ‘ 1’ of Product Register Form to check if the data have been changed

  The result is showed below: Quantity is changed to 20(100-80), Amount is changed to -20(0-20), Last Renew Date is changed to 05/05/2009

NEXT CHAPTER

BACK

CHAPTER 1---CHAPTER 2---CHAPTER 3---CHAPTER 4---CHAPTER 5---CHAPTER 6---CHAPTER 7---CHAPTER 8---CHAPTER