Some of the frequent questions we get on fetch and update of records:
1. How can I update records in a form using
Deluge Script?
You can fetch and update records from a form using Deluge script.
Let us illustrate this with the help of an example. The application Library
Manager has two forms:
Books - To enter the details
about each book with fields Name, Status and Author. The following books
are owned by the library. The column 'Status' with values 'Available'
and 'Issued', indicates whether the book is available in the library or
is issued to any user.
Issue Book - To enter the details
about the books issued. This form has a lookup field "Book Name" which
imports the Books from the above form. Only the Books whose status
is "Available" is listed in the lookup field. This is achieved by specifying
the criteria Status=="Achieved", while creating the lookup field. When
a Book is issued, the status of the Book is updated from "Available" to
"Issued" in the Books form. For example, the following books
are issued.
In the Books form, the Status of these books will be set to "Issued".
This is achieved using the "Issue _Book -> on add -> on success"
script, given below:
Code Explanation
MyBook = Books [Name == input.Book_Name]; -
Fetch records from Books form with the given criteria and store
it in collection variable named "MyBook"
MyBook.Status = "Issued"; - Access the
Mybook variable and update the Status field with value
"Issued".
The books issued is now updated with Status as "Issued" in the Books
form, as shown in the screen-shot below:
2. How can I iterate a collection of records?
The for each syntax in Deluge scripting, enables you to conditionally
fetch a collection of records and iterate over them. Let us illustrate
this with the help of a simple example. The CEO of a company wants to
address all the new employees who have joined after certain date say,
'10-jun-2007' . We have to mail all these new employees. Lets see how
we can achieve this.
1. Form 'Employee' has the following fields: Name, Qualification, EmailID,
TeamName, JoinDate
2. Write on add -> On success script, as given below:
|
for each x in Employee [JoinDate > '10-Jul-2007']
{
sendmail
(
To : x.EmailID
From : "yourmail@yourdomain.com"
Subject : "Meeting at 6:pm tomorrow"
Message : "As our CEO wants to address the new employees, you are
requested to attend the meeting.<br>Thanks"
)
}
|
Code Explanation
for each x in Employee [JoinDate > '10-Jul-2007']
- Fetch the records from the Employee form with the given
criteria and iterate over each record, to send mail to the EmailID of
each record. Here 'x' is the instance variable that will represent a
single record in each iteration.
sendmail - The deluge function
to send mail
x.EmailID - Refers to the
emailid of each record
3. How can I prepopulate one form with data that
has been previously entered in another form, in same application?
To fetch data from a form and use it in another form, a relationship
has to be established between the two forms, using Lookup fields.
To create relationships between forms using Lookup fields, refer the topic Creating relationship. Let us illustrate this with the help of an example. The sample
application has two forms:
- Customer form holds the customer details as shown in the Customer
view given below:
- New Request form is used to enter a new service request from
a customer. This will have the Customer_Number as a lookup field
from Customer form and other fields to display the customer details and
request details., as shown in the New Request form given below. When a
Customer Number is selected from the list, the customer details like,
Name, Address etc will be displayed from the Customers Form.
Deluge Script : To achieve the above requirement, we have to add
on user input script to the Customer_No field in the New
Request form, as shown below:
Customer_No
(
displayname = "Customer Number"
type = picklist
values = Customers.Customer_Number
sortorder = ascending
tooltip = "Please Enter Customer number then click Customer Name"
on user input
{
temp = Customers [Customer_Number == input.Customer_No];
input.Customer_Name = temp.Name;
input.Plot_Number = temp.Customer_Plot_no;
input.Address = temp.Cust_Address;
input.E_mail_I_D = temp.Customer_Email;
input.Phone_number = temp.Customer_Phone;
input.Post_Code = temp.cust_postcode;
}
)
|
Code Explanation:
temp = Customers [Customer_Number == input.Customer_No];
Fetch records from Customers form with the given criteria and store it
in collection variable named "temp"
input.Customer_Name = temp.Name; Update
the Customer_Name in the current form from the variable "temp". Here,
input.Customer_Name refers to the customer name field in the current
form and temp.Name is the customer name field fetched from the
"Customers" form.
4. How can I fetch a value (for example,
the salary assigned to an employee) from a form and use it in calculation
in another form?
To fetch data from a form and use it in another form, a relationship
has to be established between the two forms, using Lookup fields.
To create relationships between forms using Lookup fields, refer the topic Creating relationship. Let us illustrate this with the help of a sample application.
The application named Collection
Variable, has two forms:
- Form A stores the Name and Salary of employees.
- Form B has a Lookup (Employee) field based on the Name
field in Form A and Commission field to enter the commission to
be paid for each employee. The Total field will be calculated by
adding the Salary and the Commission. The Salary
will be fetched from From A.
Deluge Script: To calculate Total value, On user input
script is added to the Employee and the Commission field
as given below. The script will be executed when an employee is selected
or when the commission amount is entered.
Employee
(
type = picklist
values = FormA.Name
on user input
{
if (input.Employee != "-Select-")
{
if (input.Commission != null)
{
dat = FormA [Name == input.Employee];
input.Total = (dat.Salary + input.Commission);
}
}
}
)
Commission
(
type = USD
width = 20
on user input
{
if (input.Commission != null)
{
dat = FormA [Name == input.Employee];
input.Total = (dat.Salary + input.Commission);
}
}
)
|
5. How can I fetch all the records in a form?
To fetch all the records in a form, specify the criteria as
[ID != 0], where ID is a auto-generated field that
contains a unique value for each record in the form.
6. How can I fetch records within a given
range?
To fetch records from a Form within a given range, specify the start
index and end index range in the Fetch records task,
as shown in the sample code below. Here, the first five records that are
sorted by Added_Time field will be fetched from the Contact
form.
on add
{
Submit
(
type = submit
displayname = "Submit"
on success
{
rec = Contact [ID != 0] sort by Added_Time range from 1 to 5;
}
)
|
In the Script Builder UI, click on Edit in the Fetch records
dialog, to specify the Sort column and Range, as shown in the screen-shot
given below:
The Edit dialog to specify the sort field, the sort order and
range will be displayed. Specify the values and click Done.
7. How can I fetch records from another application(in
different application)?
The Fetch Records statement in Deluge Scripting currently supports
fetching data from a form and updating it in another form, within the
same application. Fetch and update of data from different applications
can be achieved by using Functions. Refer the topic, Defining
Global Variables for more information on defining functions and calling
the function from another application, to fetch and update data.
