/
REST Service for File Search with Advanced Options and Pagination

REST Service for File Search with Advanced Options and Pagination

Owned by Deepak Kumar (Deactivated)
Last updated: Jul 25, 2024 by Rajeev Rai
11 min read

Use case Description

This use case outlines the procedure for retrieving data from relational tables in a database by executing a DB query within an Adeptia process flow. The retrieved information is then presented on a web form reflecting data from the past 30 days. This process includes establishing a REST provider endpoint that triggers the execution of the process flow incorporating the DB query.

Actors:

  • User

  • Backend Server

  • Adeptia Process Flow

  • Database (Solution DB)

Preconditions:

  • Data is stored in multiple relational tables within the Solution DB.

  • Adeptia process flow is configured to execute DB queries.

  • REST provider endpoint is created and accessible.

  • UI (web form) is ready to display the fetched data.

Postconditions:

  • Relevant data is successfully retrieved from the database.

  • Data is displayed on the web form for the user.

Steps:

1. Design Process flow

Overview

When the file queue page is accessed, the backend server performs specific steps to ensure that all relevant file queue data is accurately retrieved and displayed on the front end. There are three primary flows, each with a distinct purpose. Once designed, these flows need to be published for the REST provider.

Process Overview

When the file queue page is accessed, the backend server performs the following steps to ensure that all relevant file queue data is accurately retrieved and displayed on the front end. Here are three flows working and each flow has a purpose.

Once the flow has been designed, it must be published for the REST provider.

image-20240621-060942.png

a. Default Flow: Retrieve All File Information For the Last 30 days

image-20240722-085205.png

This process will extract all file information from the last 30 days.

Process Steps

Step 1: Receive Request

  • Action: The backend server receives a request from the frontend to access the file queue page.

  • Trigger: Initiates the process of retrieving necessary file queue data.

Step 2: Query the Database

  • Action: The backend server queries the database to retrieve all relevant information.

  • Data Retrieved: File Phase, Error Descriptions, Total Record Count, Unique Record Count, Duplicate Record Count, File Status.

Step 3: Send Data to Frontend

  • Action: The backend sends the retrieved data to the front end.

  • Outcome: The front end displays the data, providing users with a comprehensive view of the file queue status and details.

Process flow activity:

  1. Source Activity - The source comprises a database. It is necessary to create DbInfo and DbLayout to be invoked in the source activity

You must utilize the EDIT Query option to efficiently retrieve attributes from multiple tables and display relevant information on the user interface. Additionally, you implement conditions to filter the data as needed.

Table Name: DB/file_queueFQ/

SELECT FQ.ID,FQ.PARTNER_NAME,FQ.TRANSACTION_TYPE,FQ.FILE_REF_ID,FQ.PHASE,FQ.OVERALL_STATUS,FQ.ERROR_CODE,FQ.INCOMING_FILE_DATE,FQ.TOTAL_RECORDS,FQ.DUPLICATE_RECORDS,FQ.UNIQUE_RECORDS,FQ.GROUP_ID,FQ.V3_GROUP_ID,FQ.V3_PHASE,FQ.V3_STATUS,FQ.V3_TOTAL_RECORDS,FQ.V3_GOOD_RECORDS,FQ.V3_ERROR_RECORDS,FQ.V3_PASS_PERCENTAGE,FQ.V3_ERROR_MESSAGE,FQ.CORE_PHASE,FQ.CORE_STATUS,FQ.CORE_TOTAL_RECORDS,FQ.CORE_GOOD_RECORDS,FQ.CORE_ERROR_RECORDS,FQ.CORE_PASS_PERCENTAGE,FQ.CORE_ERROR_MESSAGE,FQ.CREATED_BY,FQ.LAST_MODIFIED_BY,FQ.LAST_MODIFIED_DATE,FND.FILE_NAME,FND.CREATED_DATE,ER.ERROR_MESSAGE,FQ.TRANSACTION_NAME FROM file_queue FQ LEFT JOIN file_name_details FND on FND.FILE_QUEUE_ID=FQ.ID LEFT JOIN error_code ER on FQ.ERROR_CODE=ER.ERROR_CODE

WHERE FND.FILE_TYPE='INPUT_FILE' and DATE_SUB(NOW(), INTERVAL 30 DAY) < FND.CREATED_DATE ORDER BY FQ.ID DESC

SELECT FQ.ID,FQ.PARTNER_NAME,FQ.TRANSACTION_TYPE,FQ.FILE_REF_ID,FQ.PHASE,FQ.OVERALL_STATUS,FQ.ERROR_CODE,FQ.INCOMING_FILE_DATE,FQ.TOTAL_RECORDS,FQ.DUPLICATE_RECORDS,FQ.UNIQUE_RECORDS,FQ.GROUP_ID,FQ.V3_GROUP_ID,FQ.V3_PHASE,FQ.V3_STATUS,FQ.V3_TOTAL_RECORDS,FQ.V3_GOOD_RECORDS,FQ.V3_ERROR_RECORDS,FQ.V3_PASS_PERCENTAGE,FQ.V3_ERROR_MESSAGE,FQ.CORE_PHASE,FQ.CORE_STATUS,FQ.CORE_TOTAL_RECORDS,FQ.CORE_GOOD_RECORDS,FQ.CORE_ERROR_RECORDS,FQ.CORE_PASS_PERCENTAGE,FQ.CORE_ERROR_MESSAGE,FQ.CREATED_BY,FQ.LAST_MODIFIED_BY,FQ.LAST_MODIFIED_DATE,FND.FILE_NAME,FND.CREATED_DATE,ER.ERROR_MESSAGE,FQ.TRANSACTION_NAME FROM file_queue FQ LEFT JOIN file_name_details FND on FND.FILE_QUEUE_ID=FQ.ID LEFT JOIN error_code ER on FQ.ERROR_CODE=ER.ERROR_CODE

WHERE FND.FILE_TYPE='INPUT_FILE' and DATE_SUB(NOW(), INTERVAL 30 DAY) < FND.CREATED_DATE ORDER BY FQ.ID DESC

  1. Target Layout - The JSON schema for the target data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

 

  1. Mapping - You must load the database schema and the target schema (JSON) to finalize the mapping process.

  1. Context Target Using ContextTarget to store data in the flow which will be carried by the REST provider.

In the contextTarget, the variable name should strictly be "restResponse" to ensure the provider generates the desired output.

b.Search Flow: Search by Filename & Transaction Id

This flow process is for searching specific file information within the system. It enables how the user initiates the search, the interactions between the front end and back end, and the final display of the filtered results to the user.

Process Overview

The search process for file information involves several key steps, starting from user initiation to the final display of search results. The following sections detail each step in the process.

Process Steps

Step 1: User Initiates Search

  • Action: The user initiates a search for specific file information.

  • Interface: The search is conducted using a search bar or input field on the file queue page or a dedicated search interface.

Step 2: Frontend Sends Request

  • Action: The frontend application captures the user's search query.

  • Request: The search query is encapsulated in a request and sent to the backend server for processing.

  • Data Sent: The request typically includes the search term(s) and any additional filters or parameters specified by the user.

Step 3: Backend Searches File Queue Data

  • Action: Upon receiving the request, the backend server initiates a search operation.

  • Database Query: The backend performs a query on the file queue data, searching for records that match the search query.

  • Search Criteria: The search operation may include matching filenames, dates, statuses, or other relevant metadata associated with the files.

Step 4: Backend Returns Filtered Results

  • Action: The backend processes the query results and filters out the relevant records.

  • Response: The filtered search results are then encapsulated in a response.

  • Data Returned: The response includes all relevant records that match the search criteria, formatted for easy consumption by the front end.

Step 5: Frontend Displays Results

  • Action: The front end receives the response from the backend.

  • Display: The filtered search results are displayed to the user clearly and organized.

  • User Interface: The results can be displayed in a list, table, or any other suitable format, allowing the user to browse and analyze the information easily.

Process flow activity:

  1. Gateway Activity: This condition retrieves a value based on user-provided search criteria through the UI's advanced search option. The code facilitates the retrieval of data according to the specified start_date

Code Description :

Gateway Code

Description

Gateway Code

Description

String Query = context.get("start_date") == null ? "" : context.get("start_date");

if(Query!=null&&Query.length()>0)
{
return true;

}
else{
return false;
}

This code snippet performs the following actions:

  1. It initializes a string variable named Query.

    • If context.get("start_date") is null, Query is assigned an empty string ("").

    • If context.get("start_date") is not null, Query is assigned the value of context.get("start_date").

  2. It then checks if the Query variable is not null and has a length greater than 0.

    • If both conditions are true, the code returns true.

    • Otherwise, it returns false.

In summary, this code checks whether the start_date value from the context is non-null and non-empty. If it is, it returns true; otherwise, it returns false.

  1. Source Activity: The source comprises a database. It is necessary to create DbInfo and DbLayout to be invoked in the source activity

Table Name: DB/file_queueFQ/

SELECT FQ.ID,FQ.PARTNER_NAME,FQ.TRANSACTION_TYPE,FQ.FILE_REF_ID,FQ.PHASE,FQ.OVERALL_STATUS,FQ.ERROR_CODE,FQ.INCOMING_FILE_DATE,FQ.TOTAL_RECORDS,FQ.DUPLICATE_RECORDS,FQ.UNIQUE_RECORDS,FQ.GROUP_ID,FQ.V3_GROUP_ID,FQ.V3_PHASE,FQ.V3_STATUS,FQ.V3_TOTAL_RECORDS,FQ.V3_GOOD_RECORDS,FQ.V3_ERROR_RECORDS,FQ.V3_PASS_PERCENTAGE,FQ.V3_ERROR_MESSAGE,FQ.CORE_PHASE,FQ.CORE_STATUS,FQ.CORE_TOTAL_RECORDS,FQ.CORE_GOOD_RECORDS,FQ.CORE_ERROR_RECORDS,FQ.CORE_PASS_PERCENTAGE,FQ.CORE_ERROR_MESSAGE,FQ.CREATED_BY,FQ.LAST_MODIFIED_BY,FQ.LAST_MODIFIED_DATE,FND.FILE_NAME,FND.CREATED_DATE,ER.ERROR_MESSAGE,FQ.TRANSACTION_NAME FROM file_queue FQ LEFT JOIN file_name_details FND on FND.FILE_QUEUE_ID=FQ.ID LEFT JOIN error_code ER on FQ.ERROR_CODE=ER.ERROR_CODE

  1. Target Layout - The JSON schema for the target data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

  1. Mapping - You must load the database schema and the target schema (JSON) to finalize the mapping process.

  1. Context Target -The target will be ContextTarget to store data in the flow for the REST provider.

c. Advanced Process Flow - Filtering Records by (Start Date & End Date)

Overview

This Process flow is used for advanced search in which a user can filter records based on their start date and end date using the Advanced Search functionality. This includes the interactions between the frontend and backend systems to accomplish this task.

Process Steps

1. Date Range Selection

  • User Action:

    • The user selects a date range or enters specific dates to filter records by the created date.

  • UI Elements:

    • Date pickers for selecting the start and end dates.

    • A button to submit the filter request.

2. Request Submission

  • Frontend Action:

    • The frontend constructs a request with the selected date range like Start Date & End Date

3. Date Filter

  • Backend Action:

    • The backend processes the request by filtering the file queue data based on the provided date range.

    • Implementation:

      • The backend queries the database to retrieve records where the createdDate falls within the startDate and endDate.

4. Response Handling

  • Backend Action:

    • The backend returns the filtered results to the front end.

5. Display Results

  • Frontend Action:

    • The front end processes the response and displays the filtered results to the user.

  • UI Elements:

    • A table view to present the filtered file records.

    • The results section updates dynamically upon receiving the response.

Process flow activity:

 

  1. Gateway Activity: This condition retrieves a value based on the search criteria provided by the user interface, using the advanced search option. The code facilitates the flow to retrieve data according to the specified search query.

Code Description:

Gateway Code

Description

Gateway Code

Description

String Query = context.get("searchQuery") == null ? "" : context.get("searchQuery");

if(Query!=null&&Query.length()>0)
{
return true;

}
else{
return false;
}

The code is designed to determine whether the searchQuery retrieved from the context is valid, meaning it is not null and not an empty string. If searchQuery is valid, it returns true; otherwise, it returns false. This could be used to validate that a search query has been provided before proceeding with further operations.

  1. Source Activity - The source comprises a database. It is necessary to create DbInfo and DbLayout to be invoked in the source activity

Table Name: DB/file_queueFQ/

  1. Target Layout - The JSON schema for the source data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

  1. Mapping - You must load the database schema and the target schema (JSON) to finalize the mapping process.

  1. Context Target -The target will be ContextTarget to store data in the flow for the REST provider.

2. Create REST Provider End-point

Overview

A REST (Representational State Transfer) Provider is a service that offers resources and functionalities over HTTP(S) using standard REST principles. It enables clients to interact with the service through predefined APIs, facilitating the integration and communication between different systems.

In this particular use case, the process flow is shared by developing a REST provider that will eventually be integrated with the user interface.image-20240621-061858.png

Follow the steps below to create a REST Provider:

  1. Click Configure > WEB SERVICES > REST Provider.

  2. Click Create REST Provider. 

  3. In Create REST Provider window, type the name and description of the new Web Service Provider service in the Name and Description fields respectively.

  1. Type API Version, for example, your API version can be 1

  2. Type the resource end path URL in the Resource End Path field. The URL can be static or dynamic. Static URL example:  /get/FileQueueDetails 

  3. Select the process flow name to trigger with the web service from the Process Flow Name field.

    1. Set parameterName attribute of the context source should be restRequest

      1. searchQuery - To enable the Advanced Search option

      2. start_date - Advanced search criteria

      3. end_date - Advanced search criteria

      4. start - Pagination control in the user interface

  1. Click Add Method and select GET as the method type.

  1. Select the Response Schema (For example, we are opting for the schema that has been utilized in the process flow as the target schema)

  1. Save it.

3. Publish the Provider (REST URL) at UI

Once the above steps have been completed, it is necessary to link the REST provider URL with the user interface (UI) to present the data on the UI

 

 

Related content

SIC: Overview of Adeptia Rest Services Creation for GUI
SIC: Overview of Adeptia Rest Services Creation for GUI
Adeptia Implementation Service Knowledge Base
More like this
Getting Started with REST Service Data Retrieval
Getting Started with REST Service Data Retrieval
Adeptia Implementation Service Knowledge Base
More like this
UNUM : Enrollment File Transformation Process
UNUM : Enrollment File Transformation Process
Adeptia Implementation Service Knowledge Base
Read with this
Frequently Ask Question
Frequently Ask Question
Adeptia Implementation Service Knowledge Base
More like this
Implementation
Implementation
Adeptia Implementation Service Knowledge Base
Read with this
REST Services for Data Retrieval like Good Record, Error Record, and file status
REST Services for Data Retrieval like Good Record, Error Record, and file status
Adeptia Implementation Service Knowledge Base
More like this