1s 8 insert a row into the table of values. What methods exist and how to search for multiple values at the same time
In order to take into account money and goods, different tables are widely used in business. Almost every document is a table.
One table lists the goods to be shipped from the warehouse. In another table - the obligation to pay for these goods.
Therefore, in 1C, work with tables occupies a prominent place.
Tables in 1C are also called "table parts". Reference books, documents and others have them.
The query returns a table as a result of its execution, which can be accessed in two different ways.
The first - faster - selection, getting rows from it is possible only in order. The second is unloading the query result into a table of values and then random access to it.
//Option 1 - sequential access to query results
// get table
Selection = Query.Execute().Select();
// bypass all rows of the query result in order
While Selection.Next() Loop
Report(Selection.Name);
EndCycle;
//Option 2 - uploading to the table of values
Query = New Query("SELECT Name FROM Directory.Nomenclature");
// get table
Table = Query.Execute().Upload().
// then we can also bypass all lines
For each Row from Table Loop
Report(String.Name);
EndCycle;
//or arbitrarily access strings
String = Table.Find("Shovel", "Name");
An important feature is that in the table, which is obtained from the result of the query, all columns will be strongly typed. This means that by requesting the Name field from the Nomenclature lookup, 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 control panel. Similarly, you can select the Form/Insert control from the menu.
Data can be stored in a configuration - then you need to select an 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 choosing a tabular part, 1C itself will add columns to the table on the form. The strings entered by the user into such a table will be automatically saved along with the directory/document.
In the same Data property, you can enter an arbitrary name and select the ValueTable type.
This means that an arbitrary table of values has been selected. It will not automatically add columns, it will not 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 the column, you can specify its name (for reference in the 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 ViewOnly checkbox. These properties are useful for organizing tables intended for displaying information, but not for editing.
To manage the table, you need to display the command panel on the form. Select the menu item Form/Insert Control/Command Panel.
In the properties of the command bar, select the Autocomplete checkbox so that the buttons on the toolbar 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 section on the form, expand the Object branch and drag one of the tabular sections to the left. And that's it!
If you need to place a table of values, add a new form attribute and specify the type in its properties - a table of values.
To add columns, use the right mouse button menu on this form attribute, item Add attribute column.
Then also drag the table to the left.
In order for the table to have a command bar, in the table properties, select the values in the Usage - Command bar position section.
Exporting 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 Show List.
In a managed (thin) client, similar actions can be performed using the menu item All actions/Display list.
Search in the table of values 1C
What methods exist and how to search for multiple values at the same time.
There are two special methods for searching a table of values:
1. Find
TVHorizon = Directories.Nomenclature.FindBy Name("Horizon TV");FoundString = TZNomenclature.Find(TVHorizon);
//we can also specify which columns to search in to speed up the search
FoundString = TKNomenclature.Find(TVHorizon, "Nomenclature");
This method returns the first string found with the value you are looking for, or Undefined if it doesn't find it. Therefore, it is convenient to use it to search for unique values, because otherwise, when finding a value, you will have to delete it from the table in order to find the next one.
In order not to suffer like this, there is the following method, which allows you to find an array of suitable strings:
2. FindStrings
Selection Structure.Insert("Nomenclature", TVHorizon); // first we specify the column where to search, and then what to search.
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 line or, as in the previous method, for the found line, you will change the value in the processed table of values.
What else is good about this method is that it can search several columns of the value table at once:
SelectionStructure = New Struct;
Selection Structure.Insert("Nomenclature", TVHorizon);
Selection Structure.Insert("Quantity", 10);
FoundStringArray = TKNomenclature.FindStrings(SelectionStructure);
The only negative, as you can see, you can not use other types of comparison other than "equal"
A value table is a specific generic object designed to store data in a table view. The key difference between a table and application objects is that it is not linked to physical database tables. The table of values exists only in RAM, which, on the one hand, provides unique opportunities, and on the other hand, imposes certain restrictions. Nevertheless, the possibilities of interacting with a table are comparable to interacting 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 - and a control element. With the transition to a managed application, most of this functionality has been deprecated, but even now it can also be a user interface element, but with a number of significant limitations.
Structure of the table of values as an object
The properties of a value table are defined by combinations of two predefined collections: its columns and rows.
Table of values Columns
The value table column is its defining property. It is the set of columns in a table that determines its structure. Columns correspond to fields in physical tables or to familiar user interface columns in a tabular section or document log. A column can have an internal name, a value type, and a header that is displayed when interacting with the table.
Since columns are collections of objects, you can add, remove, and edit columns.
Value table row
From the point of view of the program interface, strings are a separate collection embedded in a table of values. They are analogous to the records of physical tables, that is, the rows of a tabular part or a journal of documents that are familiar to the user. Each individual row is an object with a set of named properties whose names 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 table of values, 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 consider some of them. Each example will be provided as code listings with comments.
Creating a table with a constructor
The main method, which allows you to create exactly the kind of table that the developer needs, is unfortunately the most time-consuming, since it requires you to manually specify all the necessary properties of the table.
DemoTable = New ValueTable; // First of all, we initialize the TK // Next, we define the necessary parameters for new columns and add them to the collection // Creating the "Nomenclature" column Name = "Nomenclature"; ValueType = New TypeDescription("ReferenceReference.Nomenclature"); Title = "Nomenclature (item)"; DemoTable.Columns.Add(Name, ValueType, Title); // Creating the "Amount" column Name = "Amount"; ValueType = NewTypeDescription("Number"); DemoTable.Columns.Add(Name, ValueType); // As a result of these manipulations, we have 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 “TypeDescription” constructorCreating a table by copying
If you have a reference with a suitable structure and / or composition at hand, you can copy or download the reference table of values. If the reference is another table, then you must apply the method "Copy the reference table". 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, then you can use the "CopyColumns" method.
// Variant with copying all rows from the TK-standard but with saving only two specified columns Reference Columns = "Nomenclature, Quantity"; DemoTable = TableReference.Copy(, ColumnsReference); // Variant with copying preliminarily selected rows from the TK-standard, while preserving the two specified columns. Reference Columns = "Nomenclature, Quantity"; DemoTable = TableReference.Copy(RowsReference, ColumnsReference); // Option with copying rows from the TK-standard according to the specified filter, while maintaining one column “Nomenclature” // All rows will be selected where the value in the Quantity column is 0, only the Nomenclature column will be included in the resulting table Row Selection = New Structure("Quantity" , 0); Reference Columns = "Nomenclature"; DemoTable = TableReference.Copy(RowsReference, ColumnsReference); // Variant with complete copying of the table and subsequent deletion of one row with the value of the quantity field equal to zero and deletion of the entire column “Number” RowSelection = New Structure("Number", 0); Reference Columns = "Nomenclature"; DemoTable = TableReference.Copy(RowsReference, ColumnsReference); TableString = DemoTable.Find(0, "Count"); DemoTable.Delete(TableString); DemoTable.Columns.Delete("Quantity"); // Similar options and their modifications can be applied to table parts and register record setsCreating a table with a query
If the database has a template for the table you need, then you can use a query to quickly create a table with the desired structure.
// An example of creating an empty table following the structure of the accumulation register // It is easy to guess that this way you can also get the filled table Request = New Request("SELECT FIRST 0 * From Accumulation Register. Goods in Warehouse"); QueryResult = Query.Execute(); DemoTable = QueryResult.Upload(); // An example with creating an empty table by explicitly specified types and field names Query = New Query; Query.Text = "SELECT FIRST 0 | Value(Catalog.Nomenclature.NullReference) AS Nomenclature, | EXPRESS(0 AS NUMBER(15, 3)) AS Quantity"; QueryResult = Query.Execute(); DemoTable = QueryResult.Upload(); // IMPORTANT! Do not forget that the Null type is always present in the column value types obtained from the query // Thus, the TM created by the query always has composite column typesConclusion
In this short article, we have covered the basic properties and practical techniques for creating a value table, enough to understand and start using. The table of values object itself is so multifaceted that a detailed description of its capabilities requires writing a separate article on techniques and methods of work.
Here is a little fact to start - simple examples of working with a table of values:
1. Create a table of values
ValueTable = New ValueTable;
2. Create columns of the table of values:
ValueTable.Columns.Add("Name");
ValueTable.Columns.Add("Last Name");
3. Add new rows using column names:
NewString.Name = "Vasily";
NewRow.LastName = "Pupkin";
4. How to search for a value in the value table:
It is necessary to find a table row containing the desired value.
FoundString = ValueTable.Find(LookupValue);
5. Find the first occurrence in certain columns of a table of values
FoundString = ValueTable.Find(LookupValue, "Supplier, Buyer");
6. If you need to find all occurrences in the table of values:
We use the search structure.
SearchStructure = Structure("Employee", LookupValue);
ArrayFoundStrings = ValueTable.FindStrings(SearchStructure);
Let's create a search structure, each element of which will contain the name of the column as a key and the searched value in this column as a value. We pass the Search Structure as a parameter to the FindStrings() method. As a result, we get the rows of the table.
If we add the search for the desired value to the search structure, for example, also in the column Responsible, then as a result of applying the FindRows() method, we will get all rows where both Employee and Responsible are equal to the desired value.
7. How to loop through a table of values in random order
For Each CurrentRow From ValueTable Loop
Report(CurrentLine.Name);
EndCycle;
Do the same using indexes:
SeniorIndex = ValueTable.Count() - 1;
For MF = 0 by SeniorIndex Cycle
Report(ValueTable[Count].Name);
EndCycle;
8. Deleting an Existing Value Table Row
ValueTable.Delete(RemoveRow);
by index
ValueTable.Delete(0);
9. Deleting an existing column of the table of values
ValueTable.Columns.Delete(RemoveColumn);
by index
ValueTable.Columns.Delete(0);
It must be taken into account that deleting a row (or column) “from the middle” of the table of values will lead to a decrease by one of the indexes of the rows that were “after” the deleted
10. How to fill in the value table if the column names are contained in variables?
NewRow = ValueTable.Add();
NewRow[ColumnName] = Value;
11. How to fill the entire column of the table of values with the desired value?
The FiscalAccounting Flag column in the value table of the Value Table must be filled with the value False
ValueTable.FillValue(False, "Fiscal Accounting Flag");
We use the FillValues() method for the table of values. The first parameter is the value to fill. The second parameter is the name of the filled column.
12. How to fill the table of values "TableRecipient" with the data of the table of values "SourceTable"?
If the Receiver 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
TableReceiver = TableOriginal.Copy();
Option two: table TableReceiver exists, and it's a pity to lose its columns and restrictions on 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 SourceTable From SourceTable Loop
FillPropertyValues(NewString, SourceTableString);
EndCycle
For each row of the source table, a new row is added to the destination 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 don't have columns with the same name, the destination table will end up with 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 types of the column of the destination table, we will get empty values in such fields.
Let's consider the third case. In the case of columns with the same name, the column of the destination table must be brought into full compliance with the column of the source table.
Full data copy for columns with matching names
SimilarColumns = New Array();
For Each Column From SourceTable.Columns Loop
MatchingColumn = TableReceiver.Columns.Find(Column.Name);
If MatchedColumn<>Undefined Then
// Get column properties.
Name = Column.Name;
ValueType = Column.ValueType;
Title = Column.Title;
Width = Column.Width;
// Replace columns in the destination table.
Index = TableReceiver.Columns.Index(CoincidentColumn);
TableReceiver.Columns.Delete(Index);
TableReceiver.Columns.Insert(Index, Name, ValueType, Title, Width);
// Add the next name of the matching columns to the array.
Same-nameColumns.Add(Column.Name);
EndIf;
EndCycle;
// Loop through the rows of the source table.
For each Row of SourceTable From SourceTable Loop
// Add a new row to the destination table.
NewString = TableReceiver.Add();
// Fill in values in matching cells.
For each NameColumns Of Same NameColumns Loop
NewString[ColumnName] = SourceTableString[ColumnName];
EndCycle;
EndCycle;
We will have to replace the column in the destination table with a new one, whose properties will fully match the column of the source table.
Therefore, if a column of the same name is found in the recipient table, we collect in variables all the properties for the new column. 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 recipient table and open a loop through the names of the columns in the array of matching columns.
Inside this nested loop, we fill the cells of the recipient table with the data of the cell of the source table.
13. How to add columns to the table of values "Table of Values" with type restrictions?
When adding a column, you can simply specify its name, and do not touch the second parameter of the Add() method. In this case, the data type of the column is arbitrary.
Adding a column without specifying a data type
// Add a column with no type restrictions.
ValueTable.Columns.Add("Object");
You can fill in the value of the second parameter. It is necessary to pass a description of the type allowed for the column there. The description itself can be obtained using the constructor, passing the string name of the type as a parameter (if there are many types, then separated by commas) or an array of valid types.
Adding a column specifying the data type
// Restrictions on column data types:
// Only elements of the "Contractors" directory.
ValueTable.Columns.Add("Account",NewTypeDescription("ReferenceReference.Accounts"));
If there is a string among the types allowed for filling in the column data, you can limit its bit depth (length), specify the use of a variable or fixed length. All this is provided by creating an object using the StringQualifiers constructor. Further, 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 limits for String type data.
String Qualifiers = New String Qualifiers(20, ValidLength.Variable);
AllowedTypes = NewTypeDescription("String",StringQualifiers);
ValueTable.Columns.Add("NoteStringShort", ValidTypes);
You can do the same for number and date qualifiers.
Please note: the type description can be built by the constructor both "from scratch", and you can use an existing type description as a basis
Using Existing Type Declarations to Specify the Data Type of a Value Table Column
// Extension of the previously used description of types.
Number Qualifiers = New Number Qualifiers(10, 2, ValidSign.Non-negative);
DateQualifiers = New DateQualifiers(DateParts.Date);
ExtendedValidTypes = NewTypeDescription(ValidTypes, "Number, Date",NumberQualifiers,DateQualifiers);
ValueTable.Columns.Add("Note", ExtendedAllowedTypes);
The value table in the 1C 8.3 platform (8.2) is a universal collection of values that a developer can use in software development to implement their algorithms. In fact, the 1C value table is a dynamic set of values \u200b\u200bthat have columns and columns.
Articles about other universal collections of values in 1C
Learn programming in 1C in a place from my book "Program in 1C in 11 steps"
- The book is written in clear and simple language - for a beginner.
- Learn to understand 1C architecture;
- You will begin to write code in 1C language;
- Master the basic techniques of programming;
- Consolidate the acquired knowledge with the help of a task book;
An excellent guide to developing in a managed 1C application, both for novice developers and experienced programmers.
- Very accessible and understandable language
- The book is sent by e-mail in PDF format. Can be opened on any device!
- Understand the ideology of a managed 1C application
- Learn how to develop a managed application;
- Learn to develop managed forms 1C;
- You will be able to work with the basic and necessary elements of managed forms
- Programming under a managed application will become clear
Promo code for a 15% discount - 48PVXHeYu
If this lesson helped you solve any problem, liked it or was useful, then you can support my project by transferring any amount:
can be paid manually:
Yandex.Money — 410012882996301
Web Money - R955262494655
Join my groups.