Fetch and Update Records

Some of the frequent questions we get on fetch and update of records:

1. How can I update records in a form, using scripting
2. How can I iterate a collection of records
3. How can I prepopulate one form with data that has been previously entered in another 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?
5. How can I fetch all the records in a form?
6. How can I fetch records within a given range?
7. How can I fetch records from another application?

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:

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 FAQ - Forms. 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 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 FAQ - Forms. 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.