Filling out the table of values 1s. What methods exist and how to search for several values simultaneously

18.11.2023

In order to account for money and goods, various tables are widely used in business. Almost every document is a table.

One table lists the goods to be shipped from the warehouse. Another table shows the obligations to pay for these goods.

Therefore, in 1C, working with tables occupies a prominent place.

Tables in 1C are also called “tabular parts”. Directories, documents and others have them.

The query, when executed, returns a table that can be accessed in two different ways.

The first - faster - selection, obtaining rows from it is possible only in order. The second is uploading the query result into a table of values and then random access to it.

//Option 1 – sequential access to query results

//get the table
Select = Query.Run().Select();
// we go through all the lines of the query result in order
While Select.Next() Loop
Report(Selection.Name);
EndCycle;

//Option 2 – uploading to a table of values
Request = New Request("SELECT Name FROM Directory.Nomenclature");
//get the table
Table = Query.Run().Unload().
//further we can also traverse all lines
For each Row from Table Cycle
Report(String.Name);
EndCycle;
//or arbitrarily access strings
Row = Table.Find("Shovel", "Name");

An important feature is that in the table that is obtained from the query result, all columns will be strictly typed. This means that by requesting the Name field from the Nomenclature directory, you will receive a column of the String type with an allowable length of no more than N characters.

Table on the form (thick client)

The user works with the table when it is placed on the form.

We discussed the basic principles of working with forms in the lesson on and in the lesson on

So, let's place the table on the form. To do this, you can drag the table from the Controls panel. Similarly, you can select Form/Insert Control from the menu.

The data can be stored in the configuration - then you need to select the existing (previously added) tabular part of the configuration object whose form you are editing.

Click the "..." button in the Data property. In order to see the list of tabular parts, you need to expand the Object branch.

When you select the tabular part, 1C itself will add columns to the table on the form. Rows entered by the user into such a table will be saved automatically along with the reference book/document.

In the same Data property you can enter an arbitrary name and select the Value Table type.

This means that an arbitrary table of values has been selected. It will not automatically add columns, nor will it be automatically saved, but you can do whatever you want with it.

By right-clicking on the table you can add a column. In the properties of a column, you can specify its name (for reference in 1C code), the column heading on the form, the connection with the attribute of the tabular part (the latter - if not an arbitrary table is selected, but a tabular part).

In the table properties on the form, you can specify whether the user can add/delete rows. A more advanced form is the View Only checkbox. These properties are convenient to use for organizing tables intended for displaying information, but not editing.

To manage the table, you need to display a command panel on the form. Select the menu item Form/Insert Control/Command Bar.

In the command bar properties, select the Autofill checkbox so that the buttons on the panel appear automatically.

Table on form (thin/managed client)

On a managed form, these actions look a little different. If you need to place a tabular part on the form, expand the Object branch and drag one of the tabular parts to the left. That's all!

If you need to place a table of values, add a new form attribute and in its properties specify the type – table of values.

To add columns, use the right-click menu on this form attribute, select Add attribute column.

Then also drag the table to the left.

In order for a table to have a command bar, in the table properties, select the values in the Usage – Command bar position section.

Uploading a table to Excel

Any 1C table located on the form can be printed or uploaded to Excel.

To do this, right-click on an empty space in the table and select List.

In a managed (thin) client, similar actions can be performed using the menu item All actions/Display list.

A value table is a specific universal object designed to store data in a tabular representation. The key difference between a table and application objects is the lack of binding to physical database tables. The table of values exists only in RAM, which, on the one hand, provides unique opportunities, and on the other, imposes certain restrictions. Nevertheless, the possibilities of interaction with the table are comparable to interaction with objects that actually exist in the database.

Historically, the table of values in 1C has a dual purpose, being a virtual analogue of existing tables, but at the same time it is also a control element. With the move to a managed application, much of this functionality has been deprecated, but it can still be a UI element today, but with some significant limitations.

Structure of a value table as an object

The properties of a value table are determined by combinations of two predefined collections: its columns and rows.

Table of values Columns

A column of a table of values is its defining property. It is the set of table columns that determines its structure. The columns correspond to the fields of physical tables or columns familiar from the user interface of a tabular section or document journal. A column can have an internal name, a value type, and a title that is displayed when working interactively with the table.

Since columns are a collection of objects, you can add, delete, and edit columns.

Value table row

From a software interface perspective, strings are a separate collection embedded in a table of values. They are analogous to records in physical tables, that is, rows familiar to the user in a tabular section or document journal. Each individual row is an object with a set of named properties, the names of which correspond to the names of the table columns.

Thus, interacting with a string is very similar to interacting with other objects. You can read and write its properties, including using the predefined function “FillPropertyValues()”. Since rows are the main collection of the value table, the “Clear()” method is used to delete all rows of the table.

Create table of values

There are many ways to get a table of values ready for use. Let's look at some of them. Each example will be provided as code listings with comments.

Creating a table using the constructor

The main method that allows you to create exactly the table that the developer needs is, unfortunately, the most labor-intensive, since it requires manually specifying all the necessary table properties.

DemoTable = New ValueTable; // First of all, we initialize the TK // Next, we determine the necessary parameters for new columns and add them to the collection // Creating the "Nomenclature" column Name = "Nomenclature"; ValueType = New TypeDescription("DirectoryLink.Nomenclature"); Title = "Nomenclature (product)"; DemoTable.Columns.Add(Name, ValueType, Header); // Creating the "Quantity" column Name = "Quantity"; ValueType = New TypeDescription("Number"); DemoTable.Columns.Add(Name, ValueType); // As a result of these manipulations, we created an empty table with typed columns // If you need to use more precise typing of primitive types, then you should use the extended syntax of the “Description of Types” constructor

Creating a table by copying

If you have a reference with a suitable structure and/or composition on hand, you can copy or download the reference table of values. If the reference table is another table, then you need to use the “Copy reference tables” method. If you are dealing with a tabular part or a set of register records, you must use the “Unload table of values” method. If you only need the structure, you can use the “Copy Columns” method.

// Option with copying all rows from the technical specification standard but preserving only the two specified columns. Columns of the Standard = "Nomenclature, Quantity"; DemoTable = TableEtalon.Copy(, ColumnsEtalon); // Option with copying pre-selected rows from the technical specification standard, while preserving the two specified columns. Rows of the Standard = SelectIntoArrayThe Rows We Need From the Table of the Standard(); ColumnsStandard = "Nomenclature, Quantity"; DemoTable = TableEtalon.Copy(RowsEtalon, ColumnsEtalon); // Option for copying rows from the technical specification standard using the specified filter, preserving one column “Nomenclature” // All rows where the value in the Quantity column is 0 will be selected, only the Nomenclature column Row Selection = New Structure("Quantity" will appear in the resulting table , 0); ColumnsStandard = "Nomenclature"; DemoTable = TableEtalon.Copy(RowsEtalon, ColumnsEtalon); // Option with a complete copy of the table and subsequent deletion of one row with the quantity field value equal to zero and deletion of the entire “Quantity” column. Row Selection = New Structure("Quantity", 0); ColumnsStandard = "Nomenclature"; DemoTable = TableEtalon.Copy(RowsEtalon, ColumnsEtalon); TableRow = DemoTable.Find(0, "Quantity"); DemoTable.Delete(TableRow); DemoTable.Columns.Delete("Quantity"); // Similar options and their modifications can be applied to tabular parts and sets of register records

Creating a table with a query

If a template of the table you need exists in the database, then you can use a query to quickly create a table with the desired structure.

// An example of creating an empty table based on the structure of the accumulation register // It is easy to guess that in this way you can also get a filled table Query = New Query("SELECT FIRST 0 * From Accumulation Register. Products in Warehouse"); RequestResult = Request.Execute(); DemoTable = Query Result.Unload(); // An example of creating an empty table using explicitly specified types and field names Query = New Query; Query.Text = "SELECT TOP 0 | Value(Directory.Nomenclature.EmptyLink) AS Nomenclature, | EXPRESS(0 AS NUMBER(15, 3)) AS Quantity"; RequestResult = Request.Execute(); DemoTable = Query Result.Unload(); // IMPORTANT! Don’t forget that the types of column values obtained from a request always contain the Null type // Thus, the TK created by the request always has composite column types

Conclusion

In this short article, we looked at the basic properties and practical techniques for creating a table of values, sufficient for understanding and starting to use. The value table object itself is so multifaceted that a detailed description of its capabilities requires writing a separate article on techniques and methods of work.

Search in the table of 1C values

What methods exist and how to search for several values simultaneously.

There are two special methods for searching a table of values:

1. Find

TVHorizon = Directories.Nomenclature.FindByName("TVHorizon");
FoundString = TZNomenclature.Find(TVHorizon);
//we can also specify which columns to search in to speed up the search
FoundString = TZNomenclature.Find(TVHorizon, "Nomenclature");

This method returns the first row found with the desired value, or Undefined if it does not find it. Therefore, it is convenient to use it to search for unique values, because otherwise, when a value is found, you will have to remove it from the table in order to find the next one.

To avoid this hassle, there is the following method that allows you to find an array of matching strings:

2. FindStrings

Selection Structure.Insert("Nomenclature", TVHorizon); // first indicate the column where to look, and then what to look for.

This method always returns an array, but it can be empty if nothing is found. And this method, like the previous one, returns the rows of the table of values themselves, and not the values themselves in a separate array. Therefore, by changing the values in the array string or, as in the previous method, for the found string, you will change the value in the processed table of values.

Another good thing about this method is that it can search through several columns of the value table at once:

SelectionStructure = New Structure;
Selection Structure.Insert("Nomenclature", TVHorizon);
Selection Structure.Insert("Quantity", 10);
FoundArray of Rows = TZNomenclature.FindLines(SelectionStructure);

The only negative, as you can see, is that you cannot use other types of comparison other than “equals”

Here's a little fact for starters - simple examples of working with a table of values:

1. Create a table of values

ValueTable = New ValueTable;

2. Create columns for the table of values:

ValueTable.Columns.Add("Name");
Value Table.Columns.Add("Last Name");

3. Add new rows using column names:

NewLine.Name = "Vasily";
NewLine.LastName = "Pupkin";

4. How to look up a value in the value table:
It is necessary to find a table row containing the desired value.

FoundRow = ValueTable.Find(SearchValue);

5. Find the first occurrence in certain columns of the table of values

FoundRow = ValueTable.Find(SearchValue, "Supplier, Buyer");

6. If you need to find all occurrences in the table of values:
We use the search structure.

SearchStructure = Structure("Employee", SearchValue);
Array of FoundRows = ValueTable.FindRows(SearchStructure);

Let's create a search structure, each element of which will contain the name of the column as a key and the desired value in this column as a value. We pass the Search Structure as a parameter to the FindLines() method. As a result, we get table rows.
If you add a search for the desired value to the search structure, for example, also in the Responsible column, then as a result of applying the FindLines() method we will get all rows where both the Employee and the Responsible are equal to the searched value.

7. How to iterate through a table of values in random order

For Each Current Row From Table Of Values Loop
Report(CurrentRow.Name);
EndCycle;

Same thing using indexes:

SeniorIndex = ValueTable.Quantity() - 1;
For Account = 0 to SeniorIndex Cycle
Report(TableValues[Account].Name);
EndCycle;

8. Deleting an existing value table row

ValueTable.Delete(Row to be deleted);

by index

ValueTable.Delete(0);

9. Deleting an existing column of the value table

ValueTable.Columns.Delete(ColumnDeleted);

by index

ValueTable.Columns.Delete(0);

It is necessary to take into account that deleting a row (or column) “from the middle” of the table of values will lead to a decrease by one in the indexes of the rows located “after” the deleted

10. How to fill a table of values if the column names are contained in variables?

NewRow = ValueTable.Add();
NewRow[ColumnName] = Value;

11. How to fill the entire column of the value table with the desired value?
The Fiscal Accounting Flag column in the Table of Values table of values must be filled in with the value False

Table of Values. Fill in Values (False, "Fiscal Accounting Flag");

We use the FillValues() method for the value table. The first parameter is the value to be filled. The second parameter is the name of the column to be filled.

12. How to fill the value table “Receiver Table” with data from the value table “SourceTable”?

If the Recipient Table does not yet exist at the time of the operation or its previous columns do not need to be saved, you can create it as a complete copy of the original

Recipient table = Source table.Copy();

Option two: the ReceiverTable table exists, and it would be a pity to lose its columns and restrictions on the column data types. But you need to fill in the data for the columns whose names match the names of the source table.

Partial data transfer for columns with matching names:

For Each Row of the SourceTable From the SourceTable Cycle
FillPropertyValues(NewRow, SourceTableRow);
End of the Cycle

For each row of the source table, a new row is added to the receiving table and the values are filled in those columns of the new table whose names match the names of the columns in the source table

If the tables do not have columns with the same names, the destination table will end up containing as many rows with null values as there were rows in the source table.
If for some columns of the same name the data value type from the source table does not fall into the array of allowed column types in the destination table, we will get empty values in such fields.
Let's consider the third case. In the case of columns of the same name, the column of the destination table must be brought into full compliance with the column of the source table.

Full copying of data for columns with matching names

Same Columns = New Array();

For Each Column From SourceTable.Columns Cycle
MatchingColumn = TableReceiver.Columns.Find(Column.Name);

If Matching Column<>Undefined Then

// Get column properties.
Name = Column.Name;
ValueType = Column.ValueType;
Header = Column.Header;
Width = Column.Width;

// Replace columns in the destination table.
Index = TableReceiver.Columns.Index(MatchingColumn);

TableReceiver.Columns.Delete(Index);
ReceiverTable.Columns.Insert(Index, Name, ValueType, Header, Width);

// Add the next name of matching columns to the array.
Same Columns.Add(Column.Name);

endIf;

EndCycle;

// Cycle through the rows of the source table.
For each Row of the SourceTable from the SourceTable cycle

// Add a new row to the destination table.
NewRow = TableReceiver.Add();

// Fill in the values in the matching cells.
For each Name Columns From Columns of the same name Cycle
NewRow[ColumnName] = SourceTableRow[ColumnName];

EndCycle;

We will have to replace the column in the destination table with a new one, whose properties will fully correspond to the column of the source table.
Therefore, if a column of the same name is found in the recipient table, we collect all the properties for the new column in variables. Next, delete the old one and create a new column. Then we loop through the rows of the source table.
In the loop, we add a new row to the receiving table and open a loop over the column names in the array of matching columns.
Inside this nested loop, we fill the cells of the destination table with the data of the source table cell.

13. How to add columns to the “ValueTable” table of values with type restrictions?

When adding a column, you can simply specify its name and leave the second parameter of the Add() method untouched. In this case, the column data type is arbitrary.

Adding a column without specifying a data type

// Add a column without restrictions on type.
ValueTable.Columns.Add("Object");

You can fill in the value of the second parameter. There you need to pass a description of the type allowed for the column. The description itself can be obtained using the constructor, passing to it as a parameter the string name of the type (if there are many types, separated by commas) or an array of valid types.

Adding a column indicating the data type

// Restrictions on column data types:
// Only elements of the "Counterparties" directory.
Table of Values.Columns.Add("Account", New Description of Types("DirectoryLink.Accounts"));

If among the types allowed for filling column data there is a string, you can limit its bit depth (length), specify the use of a variable or fixed length. All this is achieved by creating an object using the String Qualifiers constructor. Next, this object will be used as one of the parameters of the TypeDescription constructor.

Using qualifiers to specify the data type of a value table column

// Prepare and set restrictions for data of the String type.
String Qualifiers = New String Qualifiers(20, AllowedLength.Variable);
ValidTypes = NewTypeDescription("String", StringQualifiers);
ValueTable.Columns.Add("NoteStringShort", ValidTypes);

Similar actions can be performed with respect to number and date qualifiers.
Please note: type descriptions can be built by the constructor either “from scratch” or an existing type description can be used as a basis.

Using existing type declarations to specify the data type of a value table column

// Extension of the previously used type description.
QualifiersNumbers = New QualifiersNumbers(10, 2, ValidSign.Non-negative);
DateQualifiers = New DateQualifiers(DateParts.Date);
Extended ValidTypes = New TypeDescription(ValidTypes, "Number, Date", Number Qualifiers, Date Qualifiers);

ValueTable.Columns.Add("Note", ExtendedAcceptableTypes);

The table of values in the 1C 8.3 (8.2) platform is a universal collection of values that a developer can use during software development to implement his algorithms. Essentially, a 1C value table is a dynamic set of values that have columns and columns.