Lesson 19. Database Search

Estimated duration of the lesson is 1 hour 30 minutes.

CONTENT:

General Information on Full Text Data Search.
Creating a Full Text Index.
Creating a Data Search Report.
Quiz.

At this lesson you will understand full text data search and will create a report to search in the database using full text index. Using this report as an example, you will learn to develop a form from scratch and populate it with attributes and commands.

The infobase of the Jack of All Trades is very small for now. True, in the process of creating the configuration and testing its operation we only added a few products and posted a small number of documents.

But real-life infobases contain much more various information and sometimes locating required data is a challenging task, especially for a user who is not deeply familiar with the products (services) or with the list of contractors the company works with.

To make it easier to search for some unknown information in the database, 1C:Enterprise offers full text data search. The advantage of such search is that it makes it possible to search for data using simple and natural search queries. Example: "peterson's phone". You can also use special operators similar to those used for Internet search (AND, OR, NOT, etc.).

Full text search is very convenient when you do not know the exact location of the required data (for example, a specific catalog). Full text search is absolutely vital when you do not know exactly what you need to search for (e.g. when you don't remember the exact name of a product or a contractor).

Besides, full text search makes it possible to find data where other search tools are very time-consuming or require creation of special algorithms and data processors. For example, full text search is very convenient to work with lengthy text fields and fields of the ValueStorage type.

During this lesson we will learn general information on full text data search, create a full text index and develop a report that will enable users to execute full text search in the database of the Jack of All Trades.

General Information on Full Text Data Search

Full text search in 1C:Enterprise 8 is based on two components:

- full text index,

- full text search tools.

A full text index is required for full text search to be available. A full text index is only created once and should be updated regularly.

Search only covers the data contained in the full text index. So if a database is used heavily (data is modified, new data is added), full text index should be updated as frequently as possible. But if there are moderate volumes of modified or new data, full text index can be updated less frequently, i.e. once a day when the system experiences the least load.

Full text index can be created and updated both interactively in the 1C:Enterprise mode and using 1C:Enterprise script tools. During this lesson we will cover interactive indexing while the next lesson will demonstrate you how full text index can be updated automatically.

In the process of infobase operation, the platform tracks modifications of data in the configuration objects that may be included in the full text search. Such objects include exchange plans, catalogs, documents, chart of characteristic types, chart of accounts, chart of calculation types, registers (information, accumulation, accounting, calculation), business processes and tasks.

In the future the system analyzes the data stored in attributes of such objects when the full text index is created or updated and includes such data in the index. Note that it is true only for some attributes: those of the types Ref, Number, Date, ValueStorage and those of referential type (e.g. CatalogRef.Products).

Full text search itself is carried out using 1C:Enterprise script tools. Leaping ahead, we will note here that full text search is carried out in compliance with user rights. So if some information is not available to a specific user, this user will not be able to obtain this information using full text search either.

The results of a full text search are retrieved in batches and they are also sorted using a specific order. This increases probability of the user finding the required data in the beginning of the first batch. In practice, when a search query is written well, the required data is retrieved in the top three to five results.

Now that you understand how full text search works in general, proceed to the first portion of the actions we need to take - creating full text index.

The second portion will involve creation of a report that will execute full text search itself using this index.

Creating a Full Text Index

First we will discuss the configuration properties and objects in charge of full text search.

In the Designer Mode

Every configuration object that may have its data participating in full text indexing, has the property FullTextSearch. By default when a new object is created, the property has the value Use.

Hence, right now we do not need to make any changes but to familiarize ourselves with the property we will still open the properties palette of the Products Catalog configuration object (fig. 19.1).

Fig. 19.1. Full Text Search property

Fig. 19.2. Full Text Search property

The Full Text Search property is available not only for configuration objects but for attributes of such objects as well. So we can define specific attributes that should have their data included in full text indexing.

By default, for new attributes this property also has the Use value so here no changes are needed either.

For example, open the properties palette of the attribute ProductType of the Products catalog (fig. 19.2).

Hence by default in our configuration full text search is used for all the available attributes of all the available configuration objects. Switch to the 1C:Enterprise mode.

In the 1C:Enterprise Mode

Use menu command All Functions4Standard4Manage Full Text Search...

This opens the window where you can manage full text search (fig. 19.3).

Fig. 19.3. Full Text Search Management

This window enables you to interactively create and update full text index. Besides, it also makes it possible to allow or prohibit all the operations related to full text search: update, clear full text index, full text search.

To know if full text search operations are currently enabled, click Setup... button. Full text search setup window will be opened (fig. 19.4).

As you can see, full text search is also enabled by default so you can close this window and return to full text search management.

As you can see on the figure 19.3, the system informs you that full text index needs to be updated. This is true because in our case index is absent in general. In order to create (or update) full text index, click the button Update Index.

Note that when an infobase is large, creating and updating full text index may take a few minutes. This is why when index is updated, 1C:Enterprise status bar displays the information on the data portion that is currently processed.

When full text index is created, an appropriate message will be displayed (fig. 19.5), while the full text indexing window will display the relevance date of the full text index, i.e. the date when the index was last updated (see fig. 19.6).

Fig. 19.5. Full Text Indexing complete

Fig. 19.6. Full Text Search Management Window

So we have now created a full text index for our infobase.

Now proceed to creating a report that will enable users to carry out full text search in the database (fig. 19.7).

Creating a Data Search Report

In the Designer Mode

Add a new Report configuration object named DataSearch.

Navigate to the Forms tab, click the opening button , create a default report form and proceed to editing the form.

You can see that the form controls list is currently empty. Let us now start to populate it.

As we have mentioned above, form controls must be connected to some data because they won't be displayed otherwise. So we will first create respective attributes and commands of the form and then drag them to the form controls window.

Now on the Attributes tab create an attribute SearchExpression and drag it to the form controls window.

In the properties palette of the SearchExpression field enter its title as Phrase.

The View field is automatically populated by the Text Box value.

This is exactly what we need (fig. 19.8).

Fig. 19.8 Creating form attributes, commands, and controls

In the SearchExpression text box we will enter the phrase to search for in the database.

Next on the Commands tab create a Search command and click the opening button in the Action row.

For now we will not populate the command handler template opened in the form module. Instead navigate to the Form tab and drag this command to the form controls window.

In the properties palette of the StartSearch button that opens check DefaultButton (fig. 19.9).

But you see that all the added form controls are arranged vertically one under another. This is due to the fact that Vertical default form controls arrangement is used (you can see it in the general form properties in the Form row) (fig. 19.10).

Fig. 19.9. Creating form attributes, commands, and controls

Fig. 19.10. Form properties

While this is more or less fine with us, some form controls, including the SearchExpression field and the Find button, should better be arranged horizontally near each other.

To do so, you need to add a group to the form and select Horizontal for the controls arrangement.

Highlight the row Form in the form controls tree, click the Add button in the command bar and select the following control type: Group - Standard Group.

In the group properties palette that opens select Horizontal for grouping type (fig. 19.11).

Fig. 19.11. Form group properties

Now drag the controls SearchExpression and StartSearch to this group.

So we now have the required arrangement of controls (fig. 19.12).

Fig. 19.12. Creating form attributes, commands, and controls

Now add an attribute MessageAboutResult and drag it to the form controls window.

In the properties palette of the field that opens select No for TitleLocation.

In the View field select Label Field as the value (fig. 19.13).

The label field named MessageAboutResult will display the message on search results.

Add an attribute SearchResult to the form and drag it to the form controls window.

In the properties palette of the field that opens select No for TitleLocation.

In the View field select HTML Document Field as the value (fig. 19.14).

Fig. 19.13. Field properties

Fig. 19.14. Field Properties

The HTML document field named SearchResult will display the search results.

Next on the Commands tab create the commands Previous and Next one after another.

Click the opening button in the Action row for every command.

For now do not fill in the templates of event handlers and instead navigate to the Form tab, highlight the root of controls tree and add a new group.

Select Horizontal grouping type for this group.

Next drag our commands one by one to this group.

This will result in the following appearance of the form (fig. 19.15).

Now add the SearchResults attribute to the form that should have ValueList as a type. It will host the identified search results.

Also add a CurPosition attribute to the form (Number type) to store current search position.

These attributes are supplementary and should not be dragged to the form.

Now we will implement form operation using a code written using 1C:Enterprise script.

Fig. 19.15. Creating form attributes, commands, and controls

For the event handlers of clicking the buttons Find, Previous, and Next we will create a code that will enable us to carry out search in compliance with search direction (search from the beginning, search down or search up) (listing 19.1).

Listing 19.1. Event handlers for the buttons Find, Previous, Next

&AtClient
Procedure StartSearch(Command)
	Search(0);
EndProcedure
  
&AtClient
Procedure Previous(Command)
	Search(-1);
EndProcedure
  
&AtClient
Procedure Next(Command)
	Search(1);
EndProcedure

All these handlers call for the procedure Search() that verifies if a search expression is specified and calls for the full text search procedure itself that is executed on the server SearchServer() and receives a search direction as well (listings 19.2, 19.3).

Listing 19.2. Client search procedure

&AtClient
// Search, obtain and display the result
Procedure Search(Direction)

	If IsBlankString(SearchExpression) Then
		DoMessageBox("Search string is empty.");
		Return;
	EndIf;
	
	SearchServer(Direction);
	
EndProcedure

Listing 19.3. Server search procedure

&AtServer
Procedure SearchServer(Direction) Export

	SearchList = FullTextSearch.CreateList();
	SearchList.SearchString = SearchExpression;
	
	If Direction = 0 Then
		SearchList.FirstPart();
	ElsIf Direction = - 1 Then
		SearchList.PreviousPart(CurPosition);
	ElsIf Direction = 1 Then
		SearchList.NextPart(CurPosition);
	EndIf;
	
	SearchResults.Clear();
	
	For Each Result In SearchList Do
		SearchResults.Add(Result.Value);
	EndDo;
	
	SearchResult = SearchList.GetRepresentation(FullTextSearchRepresentationType.HTMLText);
	CurPosition = SearchList.StartPosition();
	TotalCount = SearchList.TotalCount();
	If SearchList.Count() <> 0 Then
		ResultMessage = "Shown " + String(CurPosition + 1) + " - " + String(CurPosition
			+ SearchList.Count()) + " from " + String(TotalCount);
		Items.Next.Enabled = (TotalCount - CurPosition) > SearchList.Count();
		Items.Previous.Enabled = (CurPosition > 0);
	Else
		ResultMessage = "Not found.";
		Items.Next.Enabled = False;
		Items.Previous.Enabled = False;
	EndIf;
	
EndProcedure

First in this procedure we create a search list using the CreateList() method of the FullTextSearch object and save it to the SearchList variable.

Next we set the user-entered search expression as the search string for the full text search. Next depending on the search direction we execute one of the methods FirstPart(), PreviousPart() or NextPart() that actually launches full text search itself and retrieves either the first batch of results, the previous batch or the next batch respectively depending on the current search position. By default a batch contains 20 items.

Next we clear the SearchResults list and populate it with the identified items.

Now we receive full text search results as an HTML code and save this code in the SearchResult attribute that has HTML document as its type.

Next we analyze the number of items in the search list. If it does not contain any items, an appropriate message is displayed in the form. Otherwise we generate a message regarding the items displayed and total number of items found. Based on the batch of the obtained results that is displayed, we select availability of the buttons Previous and Next.

The final thing to do is creating an event handler for OnClick of the HTML document field SearchResult that is located in the form.

The thing is that full text search results presented as an HTML code include hyperlinks to the numbers of search list items. And we would want the system to open the form of the object contained in this list item when a user clicks the link.

To do so, we need to create an event handler for OnClick of the HTML document field SearchResult where we will get a number of a list item from a hyperlink and open a form of the respective object (fig. 19.16, listing 19.4).

Finally, in the editor of the DataSearch Report configuration object on the Subsystems tab check all the subsystems so that users could employ data search in compliance with their rights.

Fig. 19.16. Creating OnClick event handler for the SearchResult field

Listing 19.4. OnClick() event handler for the SearchResult field

&
AtClient
Procedure SearchResultOnClick(Item, EventData, StandardProcessing)

	ElementHTML = EventData.Event.srcElement;
	If (ElementHTML.id = "FullTextSearchListItem") Then
	
		// Get file name (number in list for the search string), contained in the hyperlink
		NumberInList = Number(ElementHTML.nameProp);
		
		// Get search list row by number 
		RowSelected = SearchResults[NumberInList].Value;
		
		// Open form of the found object 
		OpenValue(RowSelected);
		StandardProcessing = False;
		
	EndIf;
	
EndProcedure

In the 1C:Enterprise Mode

This completes creation of the report. Now run 1C:Enterprise in the debugging mode and review the opportunities provided by the report.

We do not have a large volume of data in our database but even this volume will enable us to review major benefits of full text search.

First we will try to find the data connected to the name Johnson.

Enter johnson (fig. 19.17).

Search results include 5 items and the word occurrences in the attributes of the items are highlighted by a yellow background.

Note that in addition to the catalog item Johnson and the document recording Rendering Services to the customer named Johnson, there are also other objects found that contain various forms of the entered expression (John in this situation).

To search for the exact entered expression, enclose it in quotation marks (fig. 19.18).

Fig. 19.17. Search for Expression "Johnson"

Fig. 19.18. Search for Expression "Johnson"

Normally a couple of words is recommended for a search expression to achieve the best results of full text search.

For example, if you need to know when the client named Turner had a transformer in his TV set replaced, you can use transformer Turner as a search expression (fig. 19.19).

If you click the hyperlink, the Rendering Services No. 3 document will be opened and you will be able to view the entire list of services carried out for this customer.

In addition to the data contained in the configuration objects, the index also includes names of attributes and metadata objects.

So if you want to locate the information regarding the employee named John A. Walkman, in the search expression you should specify the name of the required catalog: employee walkman (fig. 19.20).

Fig. 19.19. Search for "Transformer Turner" expression

Fig. 19.20. Search for "employee walkman" expression

As you can see, the system has also found the employee accruals documents that contain various forms of the word "employees" but the required catalog named Employees is the first in the list of search results.

You can use various operators to generate search expressions.

Such operators are described in detail in the documentation (manual "1C:Enterprise 8.2. Developer Guide", appendix 5 "Search Expressions for Full Text Search"). Use of such operators should not pose any difficulties.

Quiz

• What is the purpose of full text data search?
• What is the main full text index? What is a supplementary full text index?
• What strategy is used for full text infobase indexing?
• How does one create a report for data search?
• How does one compose basic search expressions?