In this example, a PDF report is used to create an invoice.

Creating a PDF report

1. Create a new PDF report application using the following SQL statement:

SELECT CustomerID, OrderID, OrderDate, PriceOrder FROM orders

2. In the PDF Report menu – > Configuration, change the attribute PDF Format to Letter (216 x 279 mm) and in Create Search check yes.

3. Open the Font and Background tab, we use the “Image” property to define the background figure that will be used in the report. 

NOTE: The image used is shown below, click with the right mouse button to save the image, then upload the image to ScriptCase and select it.

4 – We will now create a special field called Subselect. It will be used to display the items in an order. Access the menu “Fields >> New field”.

5 – Choose the Subselect type and enter the name “order_details”.

6 – Change the Increment property to 11, choose the connection and then select the order_details table. In the Subselect property, make the changes as shown in the figure below.

SELECT	
productid,
unitprice,
quantity,
(unitprice*quantity) as total
FROM
order_details
WHERE
orderid = {orderid}

7 – Access the Fields folder. Select the order_details field and click order_details.ProductID. Change the data type, and then access the Lookup Configuration tab to inform the command in the image.

8 – Access the Layout PDF folder and click on the Interface item. In this interface it is possible to define the position of the cell, as well as font type, size, alignment, etc. (To make a more detailed edit of the cell, click on the Pencil icon). Position all cells according to the image below. 

Configuring the fields

9 – To configure the Filter, access the menu “Search>> Advanced Search >> Select fields”

10 – Select only the OrderID field.

11 – When editing the OrderID field, change the data type to “Select“.

12 – In the Filter Lookup, use the command according to the image below.

select orderid.sc_concat(orderind,'-',customerid)
from orders
order by orderid

13 – Run the application