Start a conversation

Creating a Invoice with Report PDF

In this tutorial we will learn how to create an Invoice application usingReport PDF.

For this example, we ‘ll use the information from two tables, "orders" and "order_details".

 

  • orders

 

CREATE TABLE `orders` (

`OrderID` int(11) NOT NULL auto_increment,

`CustomerID` char(5) default NULL,

`EmployeeID` int(11) default NULL,

`OrderDate` date default NULL,

`RequiredDate` date default NULL,

`ShippedDate` date default NULL,

`ShipVia` int(11) default NULL,

`Freight` decimal(19,4) default NULL,

`PriceOrder` decimal(16,2) default NULL,

`ShipName` varchar(40) default NULL,

`ShipAddress` varchar(60) default NULL,

`ShipCity` varchar(15) default NULL,

`ShipRegion` varchar(15) default NULL,

`ShipPostalCode` varchar(10) default NULL,

`ShipCountry` varchar(15) default NULL,

PRIMARY KEY (`OrderID`),

KEY `CustomerID` (`CustomerID`,`EmployeeID`,`OrderDate`,`ShippedDate`,`ShipVia`,`ShipPostalCode`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

  • order_details


CREATE TABLE `order_details` (

`OrderID` int(11) NOT NULL,

`ProductID` int(11) NOT NULL,

`UnitPrice` decimal(19,4) NOT NULL,

`Quantity` int(11) NOT NULL,

`Discount` decimal(7,0) NOT NULL,

`Total` decimal(16,2) default NULL,

PRIMARY KEY (`OrderID`,`ProductID`),

KEY `OrderID` (`OrderID`,`ProductID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

Our application of Report PDF will be based on "orders" table. After create the application, we will edit viewing settings.

Comment: We will use a jpg image of invoice for display in application as the backgorund. (Download in the end of Topic)

 

1. Upload the invoice and choose it. Define the width, height and image position invoice. See below.

 

 

 

2. Now, we will create two new fields: vl_total and order_details.

 

  •  vl_total of type Value. It will show the order total.

 

 

  • Order_details of the type Subselect. Subselect fields are exclusive of Report PDF, we can create a query to display fields that belong to a different table selected in the creation of the application. Thus we can work with relationship 1:N. Example (Orders and Order details, categories and products, ...).

 

 

 

 

 

3. In this example use the following information in the subselect field:

 

Increment: 7 (Distance between the rows of records).

Connection: sc_samples
Table: order_details

Selecting the table, the SQL query will be created automatically.

 

Add the following WHERE clause in SQL: WHERE OrderID = '{OrderID}'

 

4. Selecting the information that will be shown and its respective positions on the page, accessing the menu Layout PDF – Interface.

5. Removing unnescessary cells (cell_OrderID, cell_CompanyID, cell_EmployeeID, cell_OrderDate, cell_RequiredDate, cell_ShippedDate, cell_ShipVia, cell_ShipName, cell_ShipCountry, cell_order_details_OrderID, cell_ order_details _Discount). Click on edit icon of the cell (  <!—[if !vml]—><!--[endif]-->).

 

 

Informing the positioning of displayed cells .

CELL
POS X
POS Y
ALIGN
cell_CustomerId
30
75
Left
cell_Freight
135
196
Right
cell_PriceOrder
135
190
Right
cell_ShipAddress
33
81
Left
cell_ShipCity
37
87
Left
cell_ShipRegion
107
87
Left
cell_ShipPostalCode
142
87
Left
cell_vl_total
135
202
Right
cell_order_details_ProductID
40
121
Left
cell_order_details_UnitPrice
117
121
Right
cell_order_details_Quantity
18
121
Left
cell_order_details_Total
136
121
Right

 

6. Creating a event onRecord to calculate the total value of order freigth and assign to the field vl_total.

{vl_total} = {PriceOrder} {Freight};

 

 

Viewing the Application

 

Choose files or drag and drop files
Was this article helpful?
Yes
No
  1. Amanda Santos (Migrated deleted Agent)

  2. Posted
  3. Updated

Comments