This tutorial shows how to develop an application for incorporates document and content capture, workflow, document repositories, output systems, and information retrieval systems. Also, the processes used to track, store, and control documents.
──────────────────────────────
Design Your Application
In this example, we will create 5 different forms to cover functionality:
File Definition :Form to be created to define a file.
Add Document Revision :The form in which to add document revision and save, read, request actions.
Read Document :The form which to select and show document status.
User Parameters :The form which the parameters of users.
Document List :The form in which the document list is created.
──────────────────────────────
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.
──────────────────────────────
File Name: To type the file name.
File Group: To select the file group.
Status: To select document status (Active/Passive).
Access Authority: To authorize the selected user's access.
1- Add a new form and name it ‘XP_FILE_DEFINITION’’.
2- From the toolbar, add a new Text Box and name it as ‘’ File Name ’’ in both fields.
3- Add a new Drop Down Box and name it ‘’ File Group ’’ in both fields. 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 File_Name, File_Name
FROM XP_FILE_DEFINITION WITH (NOLOCK)
WHERE Name NOT IN ('','$PFile_Name$')
4- 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;
Active
Passive
5- Add a new Drop Down Box and name it ‘’ Access Authority ’’ in both fields. 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 UserID, UserFullName
FROM
dbo.XPODA_CLIENT_USERS WITH (NOLOCK)
6- Add a new List to view responsible information. 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 DF.UserTableID, CU.UserFullName AS [Create User], DF.File_Name, File_Group AS [File Group], DF.Status AS [Status], CU2.UserFullName as [Access Authority]
FROM
dbo.XP_FILE_DEFINITION DF WITH (NOLOCK)
LEFT OUTER JOIN XPODA_CLIENT_USERS CU WITH (NOLOCK) ON CU.UserID = DF.CreateUser
LEFT OUTER JOIN XPODA_CLIENT_USERS CU2 WITH (NOLOCK) ON CU2.UserID = DF.Access_Authority
7- Let’s add actions to controls. Select ‘’File Name’’. From the right pane, 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 ‘’ All Forms ‘’ and we heading to our database using Query Wizard. We need to use this query;
SELECT * FROM XP_FILE_DEFINITION WITH (NOLOCK) WHERE File_Name = '$PFile_Name$'
8- 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 ‘’ FileGroup ‘’.
9- Select ‘’List’’. 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 XP_FILE_DEFINITION WITH (NOLOCK) WHERE UserTableID = $PUserTableID
10- Let's add actions to this form. From the right pane, 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 ‘’.
11- Add the second action to validate before recording. Type of Action should be "Before recording" to handle button click. The operation will be " Validation ‘’ and we heading to the database using Query Wizard. We need to use this query;
SELECT CASE WHEN '$PFile_Name$' = '' THEN 0 ELSE 1 END
12- Let's save and run our app to see changes/newly added forms.
──────────────────────────────
Document Code: To show the document code.
Add Document: To add documents from the computer.
Document Name: To type the document name.
Status: To select document status (Active/Passive).
Reading Authority: To reading authorize the selected person.
Revision Authority: To revision authorize the selected person.
Reading Status: To show the reading status of the document.
Last Reading Date: To show the last reading date of the document.
1- Add a new form and name it ‘XP_ADD_DOCUMENT ’’.
2- From the toolbar, add a new Text Box and name it as ‘’ Document Code ’’ in both fields.
3- Add a new Numeric Box and name it as ‘’ RevisionID ‘’.
4- Add a new Drop Down Box and name it ‘’ Folder ’’ in both fields. 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 UserTableID, File_Name FROM XP_FILE_DEFINITION WITH (NOLOCK)
WHERE Status <> ‘Passive’ AND File_Group <> '' AND $PActiveUser = Access_Authority
5- From the toolbar, add a new Text Box and name it as ‘’ Document Name ’’ in both fields.
6- Add a new Drop Down Box and name it as ‘’ Status ’’. Set Autofill and Selection Compulsory properties to YES. 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;
Active
Passive
7- Add a new Drop Down Box and name it ‘’ Reading Authority ’’ in both fields. Set Autofill and Selection Compulsory properties to YES. 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 UserID, UserFullName FROM XPODA_CLIENT_USERS WITH (NOLOCK)
8- Add a new Drop Down Box and name it ‘’ Revision Authority ’’ in both fields. Set Autofill and Selection Compulsory properties to YES. 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 UserID, UserFullName FROM XPODA_CLIENT_USERS WITH (NOLOCK)
9- Add a new Text Box and name it as ‘’ Revision Comment ’’ in both fields. Set Multiline property to YES.
10- Add a new Add File and name it as ‘’Add Document’’.
11- Add a new Text Box and name it as ‘’ Reading Status ’’ in both fields. Set Multiline property to YES.
12- Add a new Date Time and name it as ‘’ Last Reading Date ‘’ in both fields.
13- Add a new Numeric Box to a separate field and name it ‘’DocumentID’’.
14- Add a new Text Box to a separate field and name it ‘’ReadUserID’’.
15- Add a new Button from the toolbar, name it ‘’Document List’’ and assign an icon. You can change the color of the button.
16- From the right pane, add action to open form when the ‘’Document List’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be "Open Form (List)" because we want to open a form list. Value Area will be ‘’All Forms’’ and we heading to our database using Query Wizard:
SELECT
UserTableID,FormTypeID,ProjectID,Document_Code,RevisionID,Document_Name,CASE
WHEN
Status, Revision_Comment, Reading_Status
FROM
XP_ADD_DOCUMENT WITH (NOLOCK)
WHERE
UserTableID IN (SELECT MAX(UserTableID) FROM XP_ADD_DOCUMENT WITH (NOLOCK) Group By Document_Code )
ORDER BY Document_Code
17- Add a new Button from the toolbar, name it ‘Revision’’ and assign an icon.
18- Add action to validate when the ‘’Document List’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Validation’’. Warning Message will be ‘’ Please save the document!’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN $PUserTableID = 0 OR '$PUserTableID$' = '' THEN 0 ELSE 1 END
19- Add action to create new revision when the ‘’Document List’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Create New Revision’’. Value Area will be "RevisionID".
20- Add a new Button from the toolbar, name it ‘‘Save’’ and assign an icon.
21- Add action to save when the ‘’Save’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Save’’.
22- Add a new Button from the toolbar, name it ‘‘Read’’, and assign an icon.
23- Add action to validate when the ‘’Read’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Validation’’. Warning Message will be ‘’ Please save the document!’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN $PUserTableID = 0 OR '$PUserTableID$' = '' THEN 0 ELSE 1 END
24- Add action to update value when the ‘’Read’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Update Value’’. Value Area will be ‘’ Itself ’’ and we heading to our database using Query Wizard :
UPDATE XP_ADD_DOCUMENT SET Reading_Status = 'Read', Last_Reading_Date = GETDATE(), ReadUserID = '$PReadUserID$' WHERE UserTableID = '$PUserTableID$'
25- Add action to show message value when the ‘’Read’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Show Message’’. Message Title will be ‘’Document Status’’. Message Type will be ‘’ Information ’’ and we heading to our database using Query Wizard :
Document status updated.
26- Add a new Button from the toolbar, name it ‘‘Request Revision’’ and assign an icon.
27- Add action to validate when the ‘’Request Revision’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Validation’’. Message Type will be ‘’ Documents must be read! ’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN '$PReading_Status$' = '' THEN 0 ELSE 1 END
28- Add action to send e-mail when the ‘’Request Revision’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be ‘’Send e-mail (form)’’. File format will be ‘’Pdf’’ and we heading to our database using Query Wizard :
SELECT UserEmail FROM XPODA_CLIENT_USERS WITH (NOLOCK) WHERE UserID = ‘$PRevision_Authority$’
Scroll down the action pane and in the Subject field, type :
Request Revision
Scroll down the action pane and in the Content field, type :
Revision mail
Confirmation form will be ‘’ Add_Document_Revision’’. Confirmation ID Field will be ‘’DocumentID’’. In the Link Text field, type ‘’ Click to revise the document’’.
29- Let's add actions to controls. Add first action to ‘’Document Code’’. Type of Action should be ‘’When the value changes’’.Operation will be ‘’Change Properties’’.Condition will be ‘’Equal’’. Value will be ‘’1’’. Select ‘’Btn – (Revision)’’ option from Fields. Type will be ‘’Passive’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN $PActiveUser = Revision_Authority THEN 0 ELSE 1 END
FROM XP_ADD_DOCUMENT WITH (NOLOCK) WHERE UserTableID = (SELECT MAX(UserTableID)
FROM XP_ADD_DOCUMENT WITH (NOLOCK) WHERE Document_Code = '$PDocument_Code$' )
30- Add second action to ‘’Document Code’’ . Type of Action should be ‘’When the value changes’’.Operation will be ‘’Change Properties’’ Condition will be ‘’Equal’’. Value will be ‘’1’’. Select ‘’Btn – (Read)’’ option from Fields. Type will be ‘’Passive’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN $PActiveUser = Reading_Authority THEN 0 ELSE 1 END
FROM XP_ADD_DOCUMENT WITH (NOLOCK)
WHERE UserTableID = (SELECT MAX(UserTableID)
FROM XP_ADD_DOCUMENT WITH (NOLOCK)
WHERE Document_Code = '$PDocument_Code$' )
31- Add action to ‘’Reading Status’’. Type of Action should be ‘’When the value changes’’.Operation will be ‘’Change Properties’’ Condition will be ‘’Equal’’. Value will be ‘’1’’. Select ‘’ ’Lbl – (Last Reading Date)’’ and ‘’Last Reading Date’’ options from Fields. Type will be ‘’Visibility’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN '$PReading_Status$' <> '' THEN 1 ELSE 0 END
32- Now let's add actions to this form. Add the first action to validate before recording. Type of action should be ‘’Before recording’’. Operation will be ‘’Validation’’ and we heading to our database using Query Wizard :
SELECT CASE WHEN '$PDocument_Name$' = '' OR '$PStatus$' = '' OR '$PReading_Authority$' = '' OR '$PRevision_Authority$' = '' THEN 0 ELSE 1 END
33- Add the second action to update 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 ‘’Active User’’ and we heading to our database using Query Wizard :
SELECT '$PActiveUser$'
34- Add the third action to update 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 ‘’Document_Code’’ and we heading to our database using Query Wizard :
Select ISNULL(max(UserTableID),0)+1 From XP_ADD_DOCUMENT WITH ( NOLOCK)
35- Add the fourth action to update 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 ‘’Last_Reading_Date’’ and we heading to our database using Query Wizard :
SELECT CreateDate FROM XP_ADD_DOCUMENT WHERE UserTableID = '$PUserTableID$'
36- Add the first authority to this form to can not change the selected fields. Type should be ‘’ Authority of Field’’. Type of Authority will be ‘’It can not change fields’’. Select ‘’Document_Code’’, ‘’RevisionID’’ and ‘’ReadingStatus’’ from Fields. Users Type will be ‘’All Users’’.
37- Add the second authority to this form to can not see the selected fields. Type should be ‘’ Authority of Field’’. Type of Authority will be ‘’It can not see fields’’. Select ‘’Last Reading Date’’, ‘’Lbl – (Last Reading Date)’’, ‘’Lbl – (DocumentID)’’ ‘’DocumentID’’, ‘’Btn – (Request Revision)’’ and ‘’Lbl – (ReadUserID)’’ from Fields. User Type will be ‘’All Users’’.
38- Let's save and run our app to see changes/newly added forms.
──────────────────────────────
Status: To select document status (All/Read).
List: To list the saved status.
Button: To show the records in the list.
1- First of all, in this form, we want to create a report screen.
Add a new form and change the type of form as a report.
2- 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;
All
Read
3- Add a new List to view responsible information. 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 AD.UserTableID, AD.CreateDate, UserFullName AS [Create User], AD.Document_Code, AD.RevisionID, DF.File_Name AS [File Name], AD.Document_Name, AD.Revision_Comment
FROM dbo.XP_ADD_DOCUMENT AD WITH (NOLOCK)
LEFT OUTER JOIN dbo.XPODA_CLIENT_USERS WITH (NOLOCK) on AD.CreateUser = UserID
LEFT OUTER JOIN dbo.XP_FILE_DEFINITION DF WITH (NOLOCK) on AD.Folder = DF.UserTableID
WHERE AD. Status = ‘Active’ AND $PActiveUser = Reading_Authority AND (Reading_Status = '$PStatus$' OR '$PStatus$' = 'All') AND AD.UserTableID IN (SELECT MAX(UserTableID)
FROM XP_ADD_DOCUMENT WITH (NOLOCK) GROUP BY Document_Code)
4- Add a new Numeric Box to a separate field and name it ‘’DocumentID’’.
5- Add the first action to List to update 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 ‘’DocumentID’’ and we heading to our database using Query Wizard :
SELECT $PUserTableID
6- Add the second action to List to open form when double clicked on row. Type of action should be ‘’When double clicked on row’’. Operation will be ‘’Open Form(Linked)’’. Condition will be ‘’In New Windows’’. Project will be ‘’Document Management’’. Form will be ‘’Add Document and Revision’’. Closing Action will be ‘’ReadDocument | list’’ and we heading to our database using Query Wizard :
DocumentID|0|DocumentID
7- Add a new Button from the toolbar, name it ‘’Search’’ and assign an icon. You can change the color of the button.
8- From the right pane, add action to update value when the ‘’Search’’ button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be "Update Value". Value Area will be ‘’GridList_1’’.
9- Let's add actions to this form. From the right pane, 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 ‘’ Status ‘’ and we heading to our database using Query Wizard :
SELECT 'Read'
10- Add the second 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 ‘’
11- Add authority to this form to can not see the selected fields. Type should be ‘’ Authority of Field’’. Type of Authority will be ‘’It can not see fields’’. Select ‘’Lbl – (DocumentID)’’ and ‘’DocumentID’’ from Fields. User Type will be ‘’All Users’’.
12- Let's save and run our app to see changes/newly added forms.
──────────────────────────────
User: To select the registered user
List: To list the checked user.
1- Add a new form and name it ‘XP_USER_PARAMETERS ’’.
2- Add a new Drop Down Box and name it ‘’ Users ’’ . 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 UserID, UserFullName FROM dbo.XPODA_CLIENT_USERS WITH (NOLOCK)
3- Add a new Check Box and name it as ‘’Check Manager’’.
4- Add a new List to view responsible information. 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 UserTableID,UserFullName, CASE WHEN Check_Manager = 1 THEN 'Manager' ELSE '' END AS [Status]
FROM XP_USER_PARAMETERS WITH (NOLOCK)
LEFT OUTER JOIN XPODA_CLIENT_USERS WITH (NOLOCK) ON UserID = Users
5- Add a new Button from the toolbar and assign the search icon.
6- From the right pane, add action to open form when the button is clicked. Type of Action should be "When Clicked" to handle button click. The operation will be "Open Form (List)". Condiyion will be ‘’In New Window’’. Value Area will be ‘’All Forms’’. List Value Field will be ‘’UserTableID’’ and we heading to our database using Query Wizard:
SELECT UserTableID,Users AS [|UserID], UserFullName, Check_Manager
FROM dbo.XP_USER_PARAMETERS WITH (NOLOCK) left outer join XPODA_CLIENT_USERS WITH (NOLOCK) ON UserID = Users
7- Let's add actions to this form. From the right pane, 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. The operation will be " Update Value ‘’. Value Area will be ‘’ GridList_1 ‘’.
8-Let's save and run our app to see changes/newly added forms.
──────────────────────────────
List: To show the document list.
1- First of all, in this form, we want to create a report screen.
Add a new form and change the type of form as a report.
2- Add a new Label and name it as ‘’Document List’’.
3- Add a new List to view responsible information. 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 AD.UserTableID ,Document_Code AS [Document Code] ,RevisionID AS [Revision Number] ,DF.File_Name AS [Document Name] ,Status AS [Status] ,CU2.UserFullName AS [Reading Authority] ,CU.UserFullName AS [Revision Authority] ,Reading_Status AS [Reading Status] ,CASE
WHEN CONVERT(DATE,Last_Reading_Date) = '18991230' THEN '' ELSE CONVERT(NVARCHAR,Last_Reading_Date,120) END AS [Last Reading Date] ,Revision_Comment
FROM dbo.XP_ADD_DOCUMENT AS AD WITH (NOLOCK)
LEFT OUTER JOIN XP_FILE_DEFINITION AS DF WITH (NOLOCK) ON AD.Folder = DF.UserTableID
LEFT OUTER JOIN XPODA_CLIENT_USERS CU WITH (NOLOCK) ON CU.UserID = AD.Revision_Authority
LEFT OUTER JOIN XPODA_CLIENT_USERS CU2 WITH (NOLOCK) ON CU2.UserID = AD.Reading_Authority
4- Let's add actions to this form. From the right pane, 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 ‘’.
5- Let's save and run our app to see changes/newly added forms.
