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"
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);
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.
Greetings to all readers of infostart. This article will be devoted to the issue of creating an arbitrary table of values on the form of a managed application programmatically.
Task features.
Everyone who programmed in a regular application often faced the task of getting an arbitrary table of values on a form. An arbitrary table of values is understood as a table, the number and type of columns of which is not known in advance. That is, there can be 3 columns, or maybe 6, or maybe 8. In a normal application, everything is simple: you could place the “Value Table” element on the processing form, and then programmatically transfer the created table of values \u200b\u200bto this element. Then with a simple command:
FormElements.TableField.CreateColumns();
to receive the ready table of values on the form. It would seem that it could be easier.
It was all in a normal application. Things have changed in a managed application. So just an arbitrary table can not be created. Now you need to either rigidly parameterize the table of values on the form, or create it programmatically (to describe, well, this, in fact, is the essence of the managed application itself). This is what we will try to do: programmatically create an arbitrary table of values on a managed form.
The solution of the problem.
The first thing we need to do is determine how the table will appear on the form. The main thing is that you do not need to create any form element in processing. We will create it programmatically, like the entire table. That is, the table will be described, and created at the time of opening the form or with the help of a button - this is how you need it.
The creation of a table on the form occurs through the description of the table of values as an attribute:
ArrayChoiceType = New Array; ArrayChoiceType.Add(Type("ValueTable")); ChoiceTypeDescription = New ChoiceTypeDescription(ChoiceTypeArray); ArrayAttributes = New Array; ArrayAttributes.Add(New FormAttribute("ScheduleTable", ChoiceTypeDescription, "", "TRN")); Now we have to create a programmatic value table that contains the data. If the table of values is obtained from a query, then everything is more or less in order. If the table is created manually, then the value of the columns that will contain numbers or dates can be created through the "Description of Types". The bottom line is that the columns in the value table must have some type. If, for example, it is assumed that the user will fill in the data in these columns interactively, then you cannot add a value table column with a simple name, it must have a type. Keep in mind - this is very important. we will transfer these types to the table on the form.
Create a table that contains several columns:
CD = New DateQualifiers(DateParts.Time); ArrayCD = New Array; ArrayKD.Add(Type("Date")); TypeDescriptionTime = New TypeDescription(ArrayKD,KD); TK = New ValueTable;
TK.Columns.Add("From", TypeDescriptionTime);
TK.Columns.Add("Before", TypeDescriptionTime);
TK.Columns.Add("Name");
TK.Columns.Add("Note");//Name and Note - strings Next, we will fill in our program table TK with the necessary data. We get a TK table that contains the necessary values and is ready to be transferred to the created form attribute. For Each Column From TK.Column Loop
ArrayAttributes.Add(New FormAttribute(Column.Name, Column.ValueType,"ScheduleTable"));EndCycle;
ChangeAttributes(ArrayAttributes);
SelectionFieldTable = Elements.Add("TZN", Type("FormTable"));
SelectionFieldTable.DataPath = "ScheduleTable";
SelectionFieldTable.Display = DisplayTable.List;
Here is such a simple combination and our table is ready.
For Each Column From TK.Column Loop
NewElement = Elements.Add(Column.Name, Type("FormField"), ChoiceFieldTable);EndCycle;
NewItem.View = FormFieldView.InputField;
NewItem.DataPath = "ScheduleTable." + Column.Name;
NewElement.Width = 10;
Conditional design, if we need we also write manually, the command menu - manually. Table handlers are also written by hand. For example, to add an event handler for the "Choice" table:
ChoiceFieldTable.SetAction("Choice","TCChoice");
To handle this event in the form of a procedure, a separate procedure is written:
&AtClient
Procedure TSNSelect(TK, SelectedRow, Field, StandardProcessing)
//handler commands EndProcedure
Note that the table handlers fire on the client and therefore must have a compiler pointer command
&AtClient
Well, the last thing I wanted to add is that after all these actions, we must not forget to transfer the finished table to the form attribute:
ValueVFormAttribute(TK, "Schedule Table");
Here is what we have as a result:
And here is the handling of the "Select" event:
Afterword.
I hope the article will help those 1C programmers who are starting to create tables on the form programmatically.
You can download a processing that programmatically creates a table of values and outputs to a managed form with comments to help you create your tables.
