This tutorial shows how to develop an application to manage the company's budget entries, leads, lead status, task management, and task details.
──────────────────────────────
Preparing and Designing
In this example, we will create 5 different forms to cover functionality;
Budget Entry The form which logs the entry of budget values
Leads The form in which the information of leads is entered
Lead Status The form which the status of leads
Task Management: The form in which task management is performed
Task Details: The form which task details are shown
──────────────────────────────
Please keep in mind, Xpoda Studio will create fields at the database right after you click the‘’ Save ’’ button from the toolbar. To be sure, use the‘’ Save ’’ button frequently or you may lose data.
Developing Applications
Now that we designed our application data and UI, it is time to develop an application using Xpoda Studio. Login to Xpoda Studio and add a new form.
──────────────────────────────
First of all, in this form, we want to record the company's budget entries.
Type: Received-expense selection of the budget
Amount: To determine the amount
Month: To choose which month
Note: To create a note
Document: To add documents
Current Budget: To show the current budget
1- Add a new form and name it ‘’USA_BUDGET_ENTRY’’.
2- From the toolbar, add a new Drop Down Box and name it as ‘’ Type ’’. After you make it you need to find the Field Style on the right panel and see Constant Value and leave as it is. Scroll down and see a blank field and fill it typing;
Received
Expense
3- Add a new Numeric Box and name it ‘’ Amount ’’ in both fields.
4- Add a new Drop Down Box and name it ‘’ Month ’’ in both fields. See the blank field and fill it typing;
January
February
March
April
May
June
July
August
September
October
November
December
5- Add a new Text Box and name it as ‘’ Note ’’ in both fields. Activate Multiline.
6- Add a new Add File and name it as ‘’ Document ‘’.
7- Add a new Numeric Box and name it as ‘’ Current Budget ‘’. Activate Form Only.
8- Add a new List to view budget entries. From the right panel scroll down and active Auto Extension.
9- Let's add actions to this form. From the right panel, add the first action to update the value when the form is opened. Type of Action should be "When the form is opened" to handle button click. The operation will be " Update Value ‘’. Value Area will be ‘’ GridList_1 ’’ and we heading to the database using Query Wizard (on the right panel at the bottom of the blank SQL field).
10- We need to find (USA_BUDGET_ENTRY) table. Now, we’re dragging
UserTableID
FromTypeID
Month
Type
Amount
Notes
And dropping off to the Query Builder in order to create the SQL Query. See, it’s that much easy to write an SQL query with Xpoda, just by dragging and dropping.
11- Add the second action to make received and expense calculations. Type of Action should be "When the form is opened" to handle button click. The operation will be " Update Value ‘’. Value Area will be ‘’ CurrentBudget ’’ and we heading to the database using Query Wizard. We need to use this query;
SELECT
ISNULL((SELECT SUM(Amount)
FROM
dbo.USA_BUDGET_ENTRY WITH (NOLOCK)
WHERE USA_BUDGET_ENTRY.Type = 'Received'),0) -
ISNULL((SELECT SUM(Amount)
FROM
dbo.USA_BUDGET_ENTRY WITH (NOLOCK)
WHERE USA_BUDGET_ENTRY.Type = 'Expense'),0)
12- Add a new authority so that the Current Budget field can not change. Type should be ‘’Authority or Field ‘’. Type of Authority will be ‘’ It can not change fields ‘’. Fields will be ‘’ CurrentBudget ’’. Users Type will be ‘’ All Users ‘’.
13- Let's run our app to see changes/newly added forms.
──────────────────────────────
In this form, we want to record the leads form.
Name: To show the Leads name
Type: To show the Leads type
Budget: For budget entry register
Explanations: To add explanation
Phone: Numbers a person has to call with a telephone.
Address: Detailed address
1- Add a new form and name it ‘’USA_Leads ’’.
2- Add a new Text Box and name it as ‘’ Name ’’ in both fields.
3- Add a new Text Box and name it as ‘’ Type ’’ in both fields.
4- Add a new Numeric Box and name it ‘’ Budget ’’ in both fields.
5- Add a new Text Box and name it as ‘’ Explanations ’’ in both fields.
6- Add a new Text Box and name it as ‘’ Phone ’’ in both fields. Select Mask as Phone.
7- Add a new Text Box and name it as ‘’ Address ’’ in both fields. Activate Multiline.
8- Let's run our app to see changes/newly added forms.
──────────────────────────────
In this form, we want to record the lead status form.
Lead: To show the lead
Status: To determine the status
Notes: To add notes
1- Add a new form and name it ‘’ USA_LEAD_STATUS ’’.
2- Add a new Drop Down Box and name it ‘’ Lead ’’ in both fields. After you make it you need to find the Field Style on the right panel and see SQL Query and leave as it is. We head to the database using Query Wizard and need to use this query;
SELECT
USA_LEADS.UserTableID,
USA_LEADS.Name
FROM
dbo.USA_LEADS WITH (NOLOCK)
3- From the toolbar, add a new Drop Down Box and name it as ‘’ Status ’’. After you make it you need to find the Field Style on the right panel and see Constant Value and leave as it is. Scroll down and see a blank field and fill it typing;
New
Relationship Building
Need Determination
Approved
Done
Canceled
4- Add a new Text Box and name it as ‘’ Notes ’’ in both fields. Activate Multiline.
5- Add a new List to display the entered values. Active Auto Extension. After you make it you need to find SQL Query. We head to the database using Query Wizard and need to use this query;
SELECT USA_LEAD_STATUS.UserTableID,
USA_LEAD_STATUS.CreateDate AS [Date],
XPODA_CLIENT_USERS.UserFullName AS [User],
USA_LEAD_STATUS.Status,
USA_LEAD_STATUS.Notes
FROM
dbo.USA_LEAD_STATUS WITH (NOLOCK)
LEFT OUTER JOIN
dbo.USA_LEADS WITH (NOLOCK) on
USA_LEAD_STATUS.Lead = USA_LEADS.UserTableID
LEFT OUTER JOIN
dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on
USA_LEAD_STATUS.CreateUser = XPODA_CLIENT_USERS.UserID
WHERE
Lead = '$PLead'
6- Let's run our app to see changes/newly added forms.
──────────────────────────────
In this form, we want to record the task management form.
Type: To show the Leads type
Lead: To show the lead
Responsible: To show the responsible
Status: To determine the status
Note: To add a note
1- Add a new form and name it ‘’ TASK_MANAGEMENT ’’.
2- Add a new Drop Down Box and name it ‘’ Type ’’ in both fields. See the blank field and fill it typing;
Lead
Other
3- Add a new Drop Down Box and name it ‘’ Lead ’’ in both fields. After you make it you need to find the Field Style on the right panel and see SQL Query and leave as it is. We head to the database using Query Wizard and need to use this query;
SELECT
USA_LEADS.UserTableID,
USA_LEADS.Name FROM
dbo.USA_LEADS WITH (NOLOCK)
4- Add a new Drop Down Box and name it ‘’ Responsible ’’ in both fields. After you make it you need to find the Field Style on the right panel and see SQL Query and leave as it is. We head to the database using Query Wizard and need to use this query;
SELECT
XPODA_CLIENT_USERS.UserID,
XPODA_CLIENT_USERS.UserFullName FROM
dbo.XPODA_CLIENT_USERS WITH (NOLOCK)
5- From the toolbar, add a new Drop Down Box and name it as ‘’ Status ’’. After you make it you need to find the Field Style on the right panel and see Constant Value and leave as it is. Scroll down and see a blank field and fill it typing;
New
Done
Cancelled
6- Add a new Text Box and name it as ‘’ Note ’’ in both fields. Activate Multiline.
7- Add a new List to view responsible information. Active Auto Extension. After you make it you need to find SQL Query. We head to the database using Query Wizard and need to use this query;
SELECT
USA_TASK_MANAGEMENT.UserTableID,
XPODA_CLIENT_USERS.CreateDate AS [Create Date],
USA_TASK_MANAGEMENT.FormTypeID,
XPODA_CLIENT_USERS.UserFullName AS Responsible,
USA_TASK_MANAGEMENT.Type,
USA_LEADS.Name AS Lead,
USA_TASK_MANAGEMENT.Status, USA_TASK_MANAGEMENT.Note
FROM dbo.USA_TASK_MANAGEMENT WITH (NOLOCK)
LEFT OUTER JOIN
dbo.USA_LEADS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Lead = USA_LEADS.UserTableID
LEFT OUTER JOIN
dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Responsible = XPODA_CLIENT_USERS.UserID
WHERE Status= 'New'
8- From the right panel, add a new action to update the value when the row is changed. Type of action should be ‘’ When the row is changed ‘’. Operation will be ‘’ Update Value ‘’. Value Area will be ‘’ All Forms ‘’ and we heading to our database using Query Wizard. We need to use this query;
SELECT * FROM
dbo.USA_TASK_MANAGEMENT WITH (NOLOCK)
WHERE
USA_TASK_MANAGEMENT.UserTableID = '$PUserTableID '
9- Add a new Button, name it ‘’ Request Completed ’’.
10- Add action to complete the request when the button is clicked. Type of Action should be "When Clicked". Operation will be "Update Value". Description, Heading and Value Area will be ‘’ GridList_1 ’’. We heading to the database using Query Wizard. We need to use this query;
SELECT
USA_TASK_MANAGEMENT.UserTableID,
XPODA_CLIENT_USERS.CreateDate AS [Create Date],
USA_TASK_MANAGEMENT.FormTypeID,
XPODA_CLIENT_USERS.UserFullName AS Responsible,
USA_TASK_MANAGEMENT.Type,
USA_LEADS.Name AS Lead,
USA_TASK_MANAGEMENT.Status,
USA_TASK_MANAGEMENT.Note
FROM dbo.USA_TASK_MANAGEMENT WITH (NOLOCK)
LEFT OUTER JOIN
dbo.USA_LEADS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Lead = USA_LEADS.UserTableID
LEFT OUTER JOIN
dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Responsible = XPODA_CLIENT_USERS.UserID
WHERE
USA_TASK_MANAGEMENT.UserTableID IN (SELECT USA_TASK_DETAILS.TaskID
FROM
dbo.USA_TASK_DETAILS WITH (NOLOCK)
WHERE
USA_TASK_DETAILS.RquestCompleted = '1' ) AND Status= 'New'
11- Add a new Button, name it ‘’ All Tasks ’’.
12- Add action to see all tasks when the button is clicked. Type of Action should be "When Clicked". Operation will be "Update Value". Description, Heading and Value Area will be ‘’ GridList_1 ’’. We heading to the database using Query Wizard. We need to use this query;
SELECT
USA_TASK_MANAGEMENT.UserTableID,
XPODA_CLIENT_USERS.CreateDate AS [Create Date],
USA_TASK_MANAGEMENT.FormTypeID,
XPODA_CLIENT_USERS.UserFullName AS Responsible,
USA_TASK_MANAGEMENT.Type,
USA_LEADS.Name AS Lead,
USA_TASK_MANAGEMENT.Status,
USA_TASK_MANAGEMENT.Note
FROM dbo.USA_TASK_MANAGEMENT WITH (NOLOCK)
LEFT OUTER JOIN dbo.USA_LEADS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Lead = USA_LEADS.UserTableID
LEFT OUTER JOIN dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Responsible = XPODA_CLIENT_USERS.UserID
13- Let's add action to this form. From the right panel, add action to update the value when the form is opened. Type of Action should be "When the form is opened" to handle button click. Operation will be " Update Value ‘’. Value Area will be ‘’ GridList_1 ’’.
14- Let's run our app to see changes/newly added forms.
──────────────────────────────
In this form, we want to record the task details form.
Activity: To enter an activity
Hours: To show how many hours of activity
Percent: To show what percentage of activity
1- Add a new form and name it ‘’ TASK_DETAILS ’’.
2- Add a new Text Box and name it as ‘’ Activity ’’ in both fields. Activate Multiline.
3- Add a new Numeric Box and name it ‘’ Hours ’’.
4- Add a new Numeric Box and name it ‘’ Percent ’’.
5- Add a new CheckBox and name it ‘’ Request Completed ‘’.
6- Add a new Button, name it ‘’ Add Details ’’, and assign an icon.
Add a new action to save the document when Add Details button is clicked. Type of Action should be "When Clicked" to handle button click. Operation will be "Save".
7- Add a new Numeric Box and name it ‘’ TaskID ’’.
Add a new action to update the value when the value changes. Type of Action should be ‘’ When the value changes ‘’. Operation will be ‘’ Update Value ‘’. Value Area will be ‘’ GridList_2 ‘’.
8- Add a new List to view details. Active Auto Extension. After you make it you need to find SQL Query. We head to the database using Query Wizard and need to use this query;
SELECT
USA_TASK_MANAGEMENT.UserTableID,
USA_TASK_MANAGEMENT.Type,
USA_LEADS.Name AS Lead,
USA_TASK_MANAGEMENT.Status,
USA_TASK_MANAGEMENT.Note
FROM
dbo.USA_TASK_MANAGEMENT WITH (NOLOCK)
LEFT OUTER JOIN dbo.USA_LEADS WITH (NOLOCK) on
USA_TASK_MANAGEMENT.Lead = USA_LEADS.UserTableID
WHERE
USA_TASK_MANAGEMENT.Status = 'New' AND Responsible= '$PActiveUser'
9- From the right panel, add a new action to update the value when the row is changed. Type of action should be ‘’ When the row is changed ‘’. Operation will be ‘’ Update Value ‘’. Value Area will be ‘’ Task ID ‘’ and we heading to our database using Query Wizard. We need to use this query;
SELECT '$PUserTableID$'
10- Add a new List to view all form details. Active Auto Extension. After you make it you need to find SQL Query. We head to the database using Query Wizard and need to use this query;
SELECT
CONVERT(DATE,USA_TASK_DETAILS.CreateDate) AS [Date] ,
XPODA_CLIENT_USERS.UserFullName AS [Create],
USA_TASK_DETAILS.UserTableID,
USA_TASK_DETAILS.Activity,
USA_TASK_DETAILS.Hours,
USA_TASK_DETAILS.ComPercent
FROM
dbo.USA_TASK_DETAILS WITH (NOLOCK)
LEFT OUTER JOIN
dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on
USA_TASK_DETAILS.CreateUser = XPODA_CLIENT_USERS.UserID
WHERE
USA_TASK_DETAILS.TaskID ='$PTaskID$'
11- Let's add actions to this form. From the right panel.
Add action to before recording validation. Type of Action should be "Before Recording ". Operation will be " Validation ‘’. Warning Message will be ‘’ Fill In The Blanks ! ‘’. We heading to the database using Query Wizard. We need to use this query;
SELECT CASE
WHEN
'$PActivity$' = '' OR '$PHours$' <= 0 OR
'$PComPercent$' <= 0 THEN 0 ELSE 1 END
12- Add action before recording validation. Type of Action should be "Before Recording ". Operation will be " Validation ‘’. Warning Message will be ‘’ Choose Task ! ‘’. We heading to the database using Query Wizard. We need to use this query;
SELECT CASE
WHEN
'$PTaskID$' >0 THEN 1 ELSE 0 END
13- Add action to update the value when the form is opened. Type of Action should be "When the form is opened". Operation will be " Update Value ‘’. Value Area will be ‘’ GridList_1 ’’.
14- Add action to send e-mail after recording. Type of Action should be ‘’ After recording ‘’. Operation will be ‘’ Send e-mail (form) ‘’. Description will be ‘’ RequestCompleted’’. The file format will be ‘’ Pdf ’’.To Whom will be ‘’ SQL Server ‘’. We need to use this query;
SELECT
'admin@xpoda.com'
Subject will be ‘’ Request Completed ‘’.
From the right panel scroll down to Content. We need to use this query to e-mail content;
A new request for completion has been entered!
Task ID: $PTaskID
Note: $PActivity
15- Add a new authority so that the TaskID field can not see. Type should be ‘’Authority or Field ‘’. Type of Authority will be ‘’ It can not see fields ‘’. Fields will be ‘’ Lbl_4’’ and ‘’ TaskID ‘’. Users Type will be ‘’ All Users ‘’.
16- Let's run our app to see changes/newly added forms.
