Exports How To's

From Pengower
Jump to: navigation, search

Learn different exporting techniques and outputs.

How to create an export

To best illustrate how to create an Export for your application, let's look at the example below:

'Rewards Plus' requires the ability to export all products for a selected supplier.

No1.jpg

To produce this, the following is required:

1. The creation of a 'Dummy' type

A type of Dummy is created (if it doesn't already exist) with a Dummy section and with a single element of 'ATTACHMENT'.


Export 1.jpg

Export 2.jpg

Export 3.jpg

Export 4.jpg


The purpose of Dummy is to give the form the ability to be associated with an attachment (which will hold the generated export). This is achieved by creating a relationship between the Supplier and Dummy types.


2. A relationship between the Supplier type and a 'Dummy' type

A relationship, called Dummy to Supplier, is created between the Dummy and Supplier types.


Export 5.jpg


3. A form to provide the necessary user interface

A form, called Export Products, is created.


Export 6.jpg


Note how the form is created with four fields:

A. Header - Contains the heading text that is to be displayed at the top of the form


Export 9a.jpg

Export 10.jpg


<style type='text/css'>.heading {color: #801C7C; font-size: 1.2em; font-weight: bold; border-bottom: 1px silver solid;} .subheading {color: #9FB929; font-size: 0.9em; padding-bottom: 5px;}</style>

Export Products
You can use this form to export products for a specific supplier.


B. Supplier - Contains the relationship betwen the Supplier and Dummy types


Export 11.jpg


C. Click here to export all products for this supplier - Is the action button that will run action ta Supplier Export Products to generate the export.


Export 12.jpg

Export 13.jpg


D. Supplier - Contains an Attachment type to allow the user to download the generated export file.


Export 14.jpg

Export 15.jpg


An action to call the form


Export 16.jpg


A menu item to call the action that calls the form


Export 17.jpg

Export 18.jpg


An action to generate the actual export


Export 7.jpg

Export 8.jpg


//ta Supplier Export Products

Set frm = Current_Form


/////////////////////////////////////////////// // Get a list of products for this supplier ///////////////////////////////////////////////

Set supplierArr = frm["Supplier"]
Set supplier = supplierArr[1]

/////////////////////////////////////////////// // Sort products by ID ///////////////////////////////////////////////

Set ArrayToSort = supplier["Products_"]

If (0 = ArrayToSort.Count)
   Report = "No products were found for this supplier."
   Output = frm
   Return_Ok
EndIf

Set SortByElement   = "ID"
Set args    = {}
Add ArrayToSort To args
Add SortByElement To args
Set rtn     = Do Run Script Params Script Name = "fnLib Sort", Args = args
Set productArr  = rtn[3]


/////////////////////////////////////////////// // Initialisation Of Export File ///////////////////////////////////////////////

Set exportDate  = NOW  
    
Set Date        = exportDate
Set Format  = "yyyyMMdd_HHmm"
Set args        = {}
Add Date To args
Add Format To args
Set rtn     = Do Run Module Params Name = "Utils_DateToString", Args = args
Set dateStr = "" + rtn
    
Set filename    = supplier["Name"] + "_Products_" + dateStr + ".CSV"
    
Set file        = Do Create File Params Name = filename, Type = "TXT"
    
Set str = ""
Set str = str + DBLQTE + "Product ID" + DBLQTE + ","
Set str = str + DBLQTE + "Master Product ID" + DBLQTE + ","
Set str = str + DBLQTE + "Name" + DBLQTE + ","
Set str = str + DBLQTE + "Country" + DBLQTE + ","
Set str = str + DBLQTE + "Language" + DBLQTE + ","
Set str = str + DBLQTE + "Product Price" + DBLQTE + ","
Set str = str + DBLQTE + "Price Currency" + DBLQTE + ","
Set str = str + DBLQTE + "Delivery Charge" + DBLQTE + ","
Set str = str + DBLQTE + "Delivery Charge Currency" + DBLQTE + ","
Set str = str + DBLQTE + "Short Description" + DBLQTE + ","
Set str = str + DBLQTE + "Long Description" + DBLQTE + ","
Set str = str + DBLQTE + "Location" + DBLQTE + ","
Set str = str + DBLQTE + "Agenda" + DBLQTE + ","
Set str = str + DBLQTE + "Notes" + DBLQTE + ","
Set str = str + DBLQTE + "Large Image" + DBLQTE + ","
Set str = str + DBLQTE + "Medium Image" + DBLQTE + ","
Set str = str + DBLQTE + "Small Image" + DBLQTE + ","
Set str = str + DBLQTE + "Supplier 1" + DBLQTE + ","
Set str = str + DBLQTE + "Supplier 2" + DBLQTE + ","
Set str = str + DBLQTE + "Supplier 3" + DBLQTE
Do Write Line Params File = file, Content = str

/////////////////////////////////////////////// // Generate an export line for each Product ///////////////////////////////////////////////


For productNo = 1 To productArr.Count
   Set product = productArr[productNo]

   // Get the necessary relationships
   Set tmpArr = product["Country"]
   Set country = tmpArr[1]

   Set tmpArr = product["Language"]
   Set language = tmpArr[1]
    
   Set tmpArr = product["Supplier Price Currency"]
   Set supplierCurrency = tmpArr[1]

   Set tmpArr = product["Delivery Charge Currency"]
   Set deliveryCurrency = tmpArr[1]


   Set str = ""
   Set str = str + DBLQTE + product["ID"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Master Product ID"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Name"] + DBLQTE + ","
   Set str = str + DBLQTE + country["Description"] + DBLQTE + ","
   Set str = str + DBLQTE + language["Description"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Supplier Price"] + DBLQTE + ","
   Set str = str + DBLQTE + supplierCurrency["Description"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Delivery Charge"] + DBLQTE + ","
   Set str = str + DBLQTE + deliveryCurrency["Description"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Short Description"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Long Description"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Location"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Agenda"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Notes"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Large Image"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Medium Image"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Small Image"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Supplier 1"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Supplier 2"] + DBLQTE + ","
   Set str = str + DBLQTE + product["Supplier 3"] + DBLQTE

   Do Write Line Params File = file, Content = str
Next


Set frm[" "] = file

Report = "" + productArr.Count + " products have been exported and can now be downloaded"
Output = frm
Return_Ok

How to Export Data to CSV File

// get organisation from current form
Set form = Current_Form
Set orgs = form["Organisation"]

// make sure that organisation had been selected
If orgs.Count < 1
	Output = form
	Report = "Please select an organisation"
	Return_Ok
Else
	Set org = orgs[1]
EndIf

// run query - all people in organisation

Set orgName = org["Name"]

Set query = "Organisation Name = '" +orgName + "'"
Set people = Get "Person" Where query

If people.Count < 1

	Output = form
	Report = "Organisation has no personnel"
	Return_Ok
	
Else
	
	// create new csv file

	Set fileName = "Personnel.csv"
	Set filePersonnel = Do Create File params Name = fileName, Type = "TXT"

	// write people to file

	For i = 1 To people.Count
		Set person = people[i]
		Set thisLine = person["First Name"] + ", " + person["Surname"] + ", " + person["Organisation"]
		Do Write Line Params File = filePersonnel, Content = thisLine

	Next
	
	// save file
	Set report = Do New Object Params Type = "Report"
	
	Set report["Title"] = "Personnel report for " + org["Name"] + " " + NOW
	Do Add File Params Object = report, Element = "File", File = filePersonnel
	
	Do Store Item Params Item = report, Force = 1
	
	Output = report
	Return_Ok
	
EndIf

How to Export information to CSV

Scripted Exporting can be used to export client details to a CSV.

Example Client Export

Set now = NOW
Set date_txt = "_" + now.Day + "_" + now.Month + "_" + now.Year + "T" + now.Hour + "_" + now.Minute

Set mig = Current_Item

Report = "Collating objects"

Set file_name  =  "client_export" + date_txt + ".csv"
Set file = Do Create File Params Name = file_name, Type = "CSV"
	
Set clients = Get "Client" Where Status != "Signed Off"
For j = 1 To clients.Count
	Set client = clients[j]
	Set str = ""
	
	Set str = str + DBLQTE + client["Client First Name"] + DBLQTE +  ","
	Set str = str + client["Client Surname"] + ","
	
	Set assocs = client["Associate"]
	Set ass_names = ""
	Set ass_ids = ""
	For k = 1 To assocs.Count
		Set assoc = assocs[k]
		Set ass_names = ass_names + assoc["First Name"] + " " + assoc["Surname"] + ","
		Set ass_ids = ass_ids + assoc["Staff ID"] + "#"
	Next
	
	Set str = str + DBLQTE + ass_names + DBLQTE + ","
	Set str = str + DBLQTE + ass_ids + DBLQTE + ","
	Set staffs = client["Created By"]
	Set staff = staffs[1]
	Set str = str + staff["First Name"] + " " + staff["Surname"] + ","
	Set str = str + staff["Staff ID"] + ","

	Set str = str + client["Email"] + ","
	Set str = str + client["Home #"] + ","
	Set str = str + client["Mobile #"] + ","
	
	Set str = str + client["Status"] + ","
		
	Set prog = client["Programme Reference"] 
	Set str = str + client["Reference No."] + ","

	Do Write Line Params File = file, Content = str
	Report = "Exporting client " + j + " out of " + clients.Count 
	Progress = j / clients.Count * 100
Next

Do Add File Params Object = mig, Element = "Files", File = file
Do Store Item Params Item = mig, Force = 1

Report = "Data exported ok"
Output = mig
Return_Ok


What the script is doing

1) Gets all clients not signed off from an old system 2) Loops through all clients 3) Takes information from every client (e.g first name, surname) 4) These fields are separated by commas 5) 'Do write' line writes to the file 6) Stores on a type which is accessible when finished