Overview

On this lesson, we are going to review the “Upload” control that will help us to allocate files under an specific repository in Wayfast. File uploading is a feature for accepting and managing images, videos, PDFs, or other documents. A file uploader looks like a button on a website that opens a dialogue box where they can choose, attach, and submit files. Let’s understand how we can setup this control in simple steps!

Preconditions

1-Create a new table in SQL

Initially we need to create a new table in our database to connect the “Upload” action with the Wayfast repository where we are going to allocate the files.

CRM_Activities table will store the name and the upload details from the file loaded. “RepositoryId” is an important attribute that will be the identifier on Wayfast repository.

Let’s take a look on the SQL query:

CREATE TABLE dbo.CRM_Activities(
  [RepositoryId] INT,
  [dbo].[Hola_Mundo_UploadFiles](
  [NEWGuid]FileName]     VARCHAR(300),
  UploadDate     DATETIME
  )
GO

Once the table is created, automatically the ID attribute will be generated by default including the procedures to manage the information stored in the Database for this table. There are 3 procedures that Wayfast generated automatically:

• spWF_CRM_Activities_CreateOrUpdate

• spWF_CRM_Activities_Delete

• spWF_CRM_Activities_Get

2-Create a Repository table to storage the files

It’s important to create another object Repository_File_Repository as a generic table to storage all the files loaded from the Upload control in Wayfast.

CREATE TABLE dbo.Repository_File_Repository(
  [RepositoryId] [int]  IDENTITIY(1,1) NOT NULL primary key,
  [FileName]     [varchar](max300) NULL,
  [RepositoryId]Size]         [intbigint] NULL,
  [UserId]Data_File]    [intimage] NULL,
  [CreationDate] [datetime] NOT NULL DEFAULT (getdate()),
  [ID]   GO

...

        [uniqueidentifier] NULL
  )
GO

This is specially needed when we are working on a testing environment and we want to run this script to have everything setup.

3-Create a new stored procedure

This We need to create an stored procedure “sp_CRM_Activities_Insert” to establish the connection between the “CRM_Activities” table for upload and the Wayfast repository using the RepositoryID RepositoryId as key parameter to identify in which place this should be stored.

In the example we are using the generic table from Step 2 to insert the new files into the repository.

CREATE PROCEDURE dbo.sp_HolaCRM_MundoActivities_UploadFileInsert
@NEWGuid  varchar(max),
@RepositoryId int,
@UserIdInsert
@RepositoryId int,
AS
BEGIN
  INSERT Hola_Mundo_UploadFiles(NEWGuid,RepositoryId,UserIdINTO dbo.CRM_Activities(RepositoryId,FileName,UploadDate)
  SELECT @NEWGuid,@RepositoryId,@UserId
ENDr.RepositoryId,r.[FileName], GETDATE()
  FROM   dbo.Repository_File_Repository r (nolock)
  WHERE  r.RepositoryId=@RepositoryId  
END
GO

Now that we insert the “RepositoryID” into the UploadFiles Repository_File_Repository table, we can use the information to display all the files in grid table under any page in the application.

We can use these parameters to identify different information about the upload files. For example:

...

NEWGuid: This could be used to identify an specific session where certain files were uploaded.

...

in

...

the application

...

.

...

UserID: This parameter will help us to identify which user performed the upload action for each file.

Using this stored procedure, we will handle the file insertion on the table where we allocate all the files in the repository.

How to create an Upload control?

Now that we have the table and the stored procedure created, we can start creating the “Upload” control. Let’s synchronize first the objects created in database to make sure that they are now visible in Wayfast.

...

A popup is displayed, click on “Synchronize” button. Now we are able to select all the objects from the Database in our project’s environment.

Navigate to “Project Pages” at the top menu and enter to the page where we are going to work in this lesson. Select the “Upload” control type.

...

Click on “New” button and popup is displayed on the screen with the control’s required fields to complete this functionality.

...

On “General” tab, it’s important to identify the default value as “FieldID” in the control. in following values as required:

Object Name: RepositoryId
Displayed Name: Attach files
Default Value: &RepositoryId

This “Upload” control allow us to create multiple actions depending on what we are going to do with the uploaded file. On next steps we will understand the importance to connect the “Upload” action with the objects previously created in database.

In the meantime, let’s continue with Let’s navigate to the next “Data Binding” tab. In this section we are not seeing fields to connect to dataset or stored procedures like we used to in control’s creation. We only have actions to connect this control. Our first action should be related to store the uploaded files in the Wayfast repository or the generic table that we created in Step 2 named Repository_File_Repository table

...

Wayfast allow us to create a new action at the same time that we create the control. Click on “New Action”

...

Select “Action Group” as “Upload” type and then we can choose if we want to point the “Repository Type” between different options: Before complete this step, let’s review all the fields and the different options that we have to create the action:

...

In our example we are going to include the following information:

...

Let’s focus on the parameters:

We can complete all the required fields for this action and click on “Submit” button to confirm the creation. The new action is now linked to the “Upload” control.

...

At this point, the action associated to the control will allow us to storage the files in the repository. Early in the “General” tab, we mentioned the importance to identify the default value in “Upload” control, now that we finished with the setup, we can say that the triggered action automatically will return a “FieldIDRepositoryId” value for each uploaded file. This is something important if we want to display all the files uploaded in another page using a grid table.

Don’t forget to click on “Submit” button to create our new “Upload” control and finish the process.

...

Let’s click on “Preview” button to verify the control in the app

...

Once we click on “Select files…” button under the “Upload” control, automatically a popup will open our system directory to indicate which file needs to be uploaded to our repository.

...

How to preview all the uploaded files in the Application?

In our later example, we created the control and the action to upload files. We know that this action trigger a return value as “FieldID” that we can use this first example to make them all the files visible, for example, on a grid table. Let’s check how we can show this information from “Upload” control in our application and make these files available to be listed or downloadable.

Preconditions

1-Create a new stored procedure to get all the files uploaded

This stored procedure will select all the files from Hola_Mundo_UploadFiles table and retrieving the information from the Wayfast repository where we stored the uploaded files.

CREATE PROCEDURE sp_Hola_Mundo_UploadFileList
AS
  BEGIN
    SELECT a.*,b.[FileName]
      FROM Hola_Mundo_UploadFiles a with(nolock)
      INNER JOIN Asignet2.dbo.Repository_FileRepository b with(nolock) on a.repositoryId=b.RepositoryID
  END
GO

Now that we insert the “RepositoryID” into the UploadFiles table, we can use the information to display all the files in grid table under any page in the application.

2Step 1-Create a new dataset to get the information from Upload Files table

Before start with the grid table, it’s necessary to create a new dataset in the page.

...

A popup is displayed on the screen and we need to complete the required fields based on the stored procedure that we created in the previous step. In this case, we are going to name this dataset as “DS_DocumentAtta” “DTSGetListActFile” to identify this on controlsthe Upload control.

...

After we identified the dataset, go to “Data Binding” tab and connect to the stored procedure that we created in database named sp_WF_Hola_Mundo_UploadFileList

...

CRM_Activities_Get. Keep in mind that this stored procedure was created by default when the CRM_Activities table was generated in Database.

...

Parameters: '-1'

We can take note on the relationship between the “Upload” control and this dataset by clicking on “Add Relationship” button. Click on “Submit” button to generate the dataset. Now we are ready to make the files visible in the screen.

...

How to list all the uploaded files in the application?

Navigate to “Controls” section and focus again on the “Upload” control previously created in the lesson.

...

Click on “Upload” link and navigate Step 2-Create a new action using the Stored Procedure to store the information from Upload Files table

Click on “Upload” link

...

Navigate to “Data Binding” tab

...

In previous example, we analyzed the “Upload” action into the repository. Every time that we upload a file, the action stored a unique identity under the “FieldID” “RepositoryId” variable in the “Upload” control.

...

This

...

This identity identity now will be used to create a new action that will connect the stored procedure sp_HolaCRM_MundoActivities_UploadFileInsertInsert and display the new files in grid table that we can retrieve from HolaCRM_Mundo_UploadFiles table.

...

As soon as we select the “Stored Procedure” in “Action Groups”, we can choose the “Execute Stored Procedure (FORM)” action. Then we can search and select the stored procedure sp_Hola_Mundo_UploadFileInsert that we mentioned in previous example. Activities table that we created initially on Preconditions.

...

In the parameters, we are going to look for the new identity as &NEWID, &FieldID and &UserIDRepositoryId. Click on “Submit” button and verify that the action is created.

...

Now that we had have the dataset and the new stored procedure to retrieve the file’s information from the table that we stored the objects when uploading those files, let’s create the grid table to display the information in the application.

We can set 2 Step 3 - Create the Grid Controls to make visible the Uploaded files in the page

Initially we are going to set set 3 columns, one to display the “Filename” and other , another one to “Download” the file and finally one to download “Delete” the file stored in our repository. Select from the table.

Navigate to “Controls” section and select the “Grid_Column” control type and click on “New” button

...

.

...

A popup is displayed , complete the required fields in “General” tab information.

...

Click on “Data Binding” tab and connect this column to the dataset created as “DS_DocumentAtta”. Click on “Submit” button to save the changeson screen, let’s fill the information according to the example. On “General” tab, include the following details:

• Object Name: FileName.

• Displayed Name: File.

On “Data Binding” tab, include the following details:

• Dataset: “DTSGetListActFile” generated in the previous step.

...

Click on “Submit” to create the first grid column.

Repeat the steps and create another “Grid Column” control to display the download icon related to the file.

...

In “Data Binding” tab, we are going to indicate the same dataset and also in the “Attachment” field, we will include the repository where we stored the uploaded files.

...

.

...

On “General” tab, include the following details:

• Object Name: RepositoryId.

• Displayed Name: Download.

On “Data Binding” tab, include the following details:

• Dataset: “DTSGetListActFile” generated in the previous step.

• Attachment: RepositoryId,4,Asignet2.dbo.Repository_File_Repository,Filename,size,Data_File

Navigate to the “Layout” tab and search for the icon that will trigger the “Download” file action. Include the following details:

• Place Holder: E1

• Oder: 4

• Pictures: flechi-verde.gif

...

Click on “Submit” button to save the changes.

Repeat the steps and create the last “Grid Column” control to display the Delete icon related to the file.

...

On “General” tab, include the following details:

• Object Name: Delete.

• Displayed Name: Delete.

On “Data Binding” tab, include the following details:

• Dataset: “DTSGetListActFile” generated in the previous step.

• Action: Execute Stored Procedure

• Stored Procedure: sp.WR_CRM_Activities_Delete (This object was generated by default when creating the CRM_Activities at the beginning of this lesson)

Navigate to the “Layout” tab and search for the icon that will trigger the “Download” “Delete” file action.

...

Include the following details:

• Place Holder: E1

• Oder: 5

• Pictures: delete.gif

Click on “Submit” button

...

Let’s review how the application works after all these new implementations that we learnt step by step. Click on “Preview” button

...

To start the process, click on “Select files…” button under the “Upload” control, automatically a popup will open our system directory to indicate which file needs to be uploaded to our repository.

...

After selecting all the files, click on “Open” button.

Once the files are uploaded into the Wayfast repository, we can observe the “File”, “Download” and “Delete” columns that shows the information from all the listed files that the user we uploaded in the application. If the user wants to download, click on the icon and the file is automatically retrieved from the repository where we stored.

...

...

How to display an image uploaded into a Wayfast Repository?

select Img='<img src=image.ashk?IdRepositorio='+Cast(@RepositoryId, Varchar)

Recap

In the first part, we revised the preconditions to create a new table in database to stored all the uploaded files in our repository. We understand the important connection between the “Upload” control and the action for this purpose. In the second part, we dove into all the uploaded files in the repository and display them on a grid table that every user can check to download the stored information.