To create this kind of directories, a slightly different database structure is required. Indeed, as already mentioned, such directories are created to facilitate the selection of a value from a list with a large number of entries that are filtered by the selected value (parameter) in another list. And this means that you will need to link two reference tables, organizing a composite directory from them.
In the previous chapter, instead of creating many references of the same type, it was proposed to make one reference form layout and link the data source to it at startup. But you can go even further. Let's use the idea proposed by Valery Kruk - we will make one reference table and store the data of all directories in it. This will require one more field - the identifier of the "reference table" to determine which reference it belongs to this entry. And to implement a two-level directory, we will create a second table that will store records related to the first table.
When establishing relationships between tables DIRECTORY and DIRECTORY Sub check the "Cascade delete related records" box - this is exactly the case when such a procedure will be useful. Indeed, when a record is deleted from the DIRECTORY table, the data associated with it becomes unnecessary.
If you open the data schema, you will only see two related lookup tables. In the previous example, all directories were linked to the main table "Destination", and I also talked in detail about links, flags ... The fact is that with this organization scheme of the reference system, we will not be able to attach the "DIRECTORY" table to any field, because in it now stores all reference data. Yes, there is no need for this. If we take into account that the user will work with the database exclusively through forms, then the probability of entering records into the main table that are not in the reference table is reduced to almost zero.
Dictionary parameters are stored in the service table tSystemFormPar. The Tabl field contains the identifier of the corresponding directory.
Consider the differences from the previous version.
I transferred all global variables and constants to a special module, which is called Constants. Although you can declare them in any module, the main thing is that they be in the General section and start with keyword Public, but it is better to store them in one place for clarity.
Added to the SprawForm module new feature- fFillListBox. It is in many ways similar to fFilForm, but it serves to filter the list, so lst As ListBox is set in the parameters instead of frm As Form. In addition, the filter generation line has been changed in both functions:
strFiltr = " WHERE Left([" & strFieldName & "]," & Len(strFiltr) & ") = "" & strFiltr & """ & " and DIRECTORY.Type = " & strTableName
After all, now you need to filter not only by the value in the filter field, but also by the parameter (handbook identifier) in the tSystemFormPar table. Also, if the data source for the form is specified via
RecordSource = strSql1 & strFiltr & " " & strSql2
then for the list you need to use the RowSource property
RowSource = strSql & strFiltr & " " & strSql1
In the form module "Directory" added selection condition for the Type field of the DIRECTORY table
Me.Subfrm.Form.RecordSource = strSql2 & " WHERE REFERENCE.Type = " & strTableName & strSql3
and a new one appeared - assignment to a field Type values by default, equal to the current value of the variable strTableName
Me.Subfrm.Form!Type.DefaultValue = "(!LANG: & strTableName
After all, if this is not done, then the record in the DIRECTORY table will not be linked to the current type of the directory.
In the form module of the two-level directory "DirectoryM" we see that two pairs of lines are formed - data sources: strSql, strSql1- for list and strSql2, strSql3- for tabular form. The lines are divided into pairs because a selection condition will need to be inserted between them. (WHERE…) In order for the newly entered value in the form field to be immediately displayed in the list, use the procedure
Private Sub Fld_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
Me.ListB.RowSource = strSql &" WHERE СПРАВОЧНИК.Type = " & strTableName & strSql1!}
end sub
And to navigate the form (search for the desired record) we use
Private Sub ListB_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst " = " & Str(Nz(Me!, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
end sub
To exclude the possibility of entering records into the tabular form (source - DIRECTORY Sub) that are not related to the main form (source - DIRECTORY), there is a procedure that displays a corresponding message when such an attempt is made and blocks it:
Private Sub Subfrm_Enter()
If flgDeleteRecord = False Then
If IsNull() Then
MsgBox "You need to get some basic data first!", vbCritical, NomWers
Fld.SetFocus
End If
End If
end sub
Since the second filter field appeared on the form, the corresponding filtering procedure for this field appeared
Private Sub P2_Change()
strFiltr = Me.P2.Text
Set idField = Me.p2
Call fFilForm(strFiltr, strSql2, strSql3, Me.Subfrm.Form, "Name")
end sub
This type of organization of the help system - using common tables for different directories, not only greatly simplifies the creation of directories (to do this, it is enough to fill in the tSystemFormPar table accordingly), but also reduces the size of the database due to fewer forms and tables.
And now you can experiment by creating different types of directories - simple and two-level.
Directories are designed to simplify the input of various information into primary documents and reduce the number of input errors. Directories contain information that does not change during the operation of the system or changes rarely.
Reference books are multi-level, largest number levels is three. For example, directories are multi-level Nomenclature, Employees. A directory can also be a simple list.
Each element of the directory has a unique code. It is assigned automatically when a new element is entered ͵ ensures the integrity of the infobase.
Some requisites of directories have the property of periodicity. This property allows you to store a history of value changes, for example, the exchange rate. The new value entered in the directory will be valid starting from the date of the change. The frequency is used when filling out a document͵ and when generating a report͵ in this regard, before changing the value of such a variable, you should check the working date set in the system parameters ( Service Options General).
Some directories are subordinate to other directories. For example, a guide bank accounts subordinate to directory Firms, handbook Settlement accounts- reference book Counterparties, directories Equipment and Units- reference book Nomenclature.
When working with a child directory, the parent directory must first be opened. The child dictionary will contain only elements related to one element of the parent directory, while the display mode must be in the form of a hierarchical list.
Viewing and editing directories is possible from the main menu. Working with them facilitates data entry into primary documents. Some directories can be edited in the process of work.
There are many tools for editing directories. The toolbar buttons are the most commonly used. Some commands are executed from the menu Actions, from context menu or using hotkeys.
The procedure for deleting directory elements has its own peculiarities and consists of two stages. When deleting, the document is first only marked as a candidate for deletion. Real removal should be done through the menu Actions Delete.
When this command is executed, the data is checked first. If the deleted documents contain related data (used in other tables), this data cannot be deleted.
4.2.1. Directory "Firms"
In the model of a typical configuration, enterprises that are combined into a single trade enterprise are called "firms". From point of view management accounting͵ This is one enterprise. From the point of view of financial accounting, each company is an independent legal entity.
Directory "Firms" is designed to maintain a list of companies and store their details required when filling out primary documents, keeping records and reporting. The reference book is single-level. The number of firms is not limited.
For periodic details only in this directory, the following rule is established: if the value of a periodic variable is entered for the first time, then it will be recorded at the beginning of the year of the working date, and not on the working date. With subsequent changes to such details, the value will be remembered on the date of the change (working date).
Entering and editing information about the company is performed in a dialog box containing three tabs.
Constants Some data in the accounting system changes very rarely or, ideally, does not change at all. Such data is usually stored in constants. For example, this is information about the currency of regulated accounting. Usually such records are kept in rubles, but in the configuration ... [read more]
Grammars Textbooks and teaching aids List of abbreviations Names of quoted writers A. A. - A. Akhmatova A. B. - A. A. Blok A. V. - A. Voznesensky Ars. – Arseniev A. G. – A. Galich A. Gr. – A. S. Griboedov A. K. – A. Kuprin Aks. - S. T. Aksakov A. S. - A. I .... [read more]
Principal technological scheme of the construction of the facility. Topic number 4. Composition and development of documentation for the initial stage of modeling. 4.1. Principal technological scheme of the construction of the facility. 4.2. Reference books of executors. 4.3. Local network models... [read more]
Meta search engines The Internet is developing at a rapid pace - hundreds of thousands of new Web pages appear every day. The growth in the number of documents is happening faster than the search engines manage to index them. This leads to the disappointing conclusion that, ... [read more]
1. Abramov N. Dictionary of Russian synonyms and expressions similar in meaning. Any edition. 2. Ageenko F.L., Zarva M.V. Dictionary of stresses of the Russian language. M., 2000 3. Belchikov Yu.A., Panyusheva M.S. Dictionary of paronyms of the Russian language. M., 1994 4. Large spelling dictionary of the Russian language. Ed .... [read more]
The correctness of accounting requires the accountant to have a good knowledge of the existing regulatory framework. It is quite difficult to keep track of all changes in legislation on your own. Therefore, many accountants and economists are users of legal databases....
Reference books. Multi-level and subordinate. Periodic details of directories
In the 1C:UT program, directories are used to store conditionally permanent information, support intra-company or general classifiers. They are also used to organize analytical accounting. Directories are organized in the form of tables, which store the same type of information about objects.
The use of directories allows you to eliminate ambiguity when filling in certain details of documents or other directories.
For example, if it is required to enter the name of the organization - the client in the invoice, selecting an element from the directory of clients will avoid erroneous entry of the name by different end users.
Work with reference books can be carried out in two modes:
Ø Viewing and editing - the directory form is opened using the main menu, the button on the toolbar, by pressing the button of another form;
Ø Choice - opens only when you enter the value of the subconto, constant, attribute of the document or attribute of another directory, using the F4 key.
The difference between these two modes is that in the selection mode, you can place the cursor on the required element of the directory and, by pressing the Enter key, enter the current element as the value of the attribute or constant being edited.
At the stage of creating directories, a single-level or multi-level directory up to 10 levels of nesting can be declared.
A single-level reference is a simple list of items.
A multilevel directory is a hierarchical list in which entries are combined into groups.
The use of multi-level directories allows you to organize information with the desired level of detail.
System 1C: Enterprise has a mechanism for supporting subordinate directories. This mechanism allows you to link elements of different directories:
Ø organization and agreements with him,
For a subordinate directory, each element is associated with a specific element of the directory - the owner.
There are several common elements that each directory form has.
The top line - the heading in it the name of the directory is displayed. If the directory is a child, then the name of the element from the parent directory is displayed in the title of the directory window.
The window in the general case is a table consisting of separate columns. The composition and relative position of the graphs displayed in the form of a directory can distinguish between several modes of operation.
If the directory has a hierarchical structure, the directory window may contain a tree of groups.
If the directory contains many elements and has many columns, then scrollbars appear in the table.
If the current directory in the "Actions" menu is set to display as a hierarchical list, then the following symbols appear:
Ø the line contains the name of the group and is open for viewing;
Ø string is a reference element;
Ø line corresponds to a group of elements, a double click opens access to the directory elements.
Hierarchical directory interface
Hierarchical directories of the system have a multi-level structure. good example such a structure can serve as an ordinary cabinet with the documentation stored in it. The documents are not collected in one large pile, but are filed or bound into separate folders, the folders are placed on the shelves in a certain order, and there are several such shelves in the cabinet. With a large number of documents, they are distributed over several cabinets, and not at random, but again in a certain order. As a rule, the organizational structure of any medium or large enterprise or organization is also multi-level. An enterprise, for example, can be subdivided into workshops and sections, an organization into departments, departments and departments.
A hierarchical or multi-level directory is organized in a similar way. It is divided into sections, which, in turn, may consist of subsections (sections of a lower level). Any of these subsections can also be subdivided into smaller subsections (even lower level sections).
Elements that can be drilled down with a list of values of a lower level of the hierarchy are called vertices and are marked in hierarchical reference books with the icon. The final elements of the directory, i.e. elements that cannot be drilled down are called leaves and are marked with the icon.
In general, a hierarchical directory can have an arbitrary nesting depth. A vertex can contain as elements both leaves and other vertices (sub-vertices) nested in this one.
Consider the interface of a hierarchical directory on the example of a directory Subdivisions.
When working with a hierarchical directory, the window menu barDirectory maintenancecontains the following items:
- Control – includes commands for adding, modifying and deleting directory elements, moving them up/down the list of elements, commands for importing and exporting directory data, as well as a command for searching for the necessary elements;
- Sorting – consists of commands for sorting elements;
- View – includes commands that determine the type of the list of directory elements (with or without logically deleted elements);
- Window – contains commands for controlling the position of working windows;
- Reference – includes commands for calling help information;
- close – command to end work with the current directory.
If hot keys are set for any command, they are indicated in the menu next to it.
The following buttons are located on the window toolbar:
|
Button view |
Executable command |
|
Add a top element to the directory. Duplicate commands Office, Add Vertex; |
|
|
Add a sheet element to the directory. Duplicate commands Management , Add sheet ; |
|
|
Edit the selected element. Duplicate commands Manage , Edit ; |
|
|
Delete (restore) the selected element. Duplicate commands Office, Delete (restore); |
|
|
Move the selected item down one position in the list. Duplicate commands Office , line down ; |
|
|
Move the selected item up one position in the list. Duplicate commands Office , line up ; |
|
|
Go to the search for elements that meet the specified criteria. Duplicate commands Management , Search ; |
|
|
Include logically remote elements in the list of directory elements. Clicking the button again causes the list of elements to display only active (active) elements. Duplicate menu commands View ; |
|
|
End the current directory. Duplicate team Close . |
In hierarchical directories such asList of organizations and Document groups, due to their specifics, in addition to the specified menu commands, additional commands are used, and the toolbar contains buttons that duplicate these commands (see sections on maintaining these directories).
When a hierarchical reference window is opened, a list of the top level of the hierarchy is formed in it, containing, as a rule, one vertex. The title of the window contains only the name of the directory.
To open a vertex (show the list of elements contained in it), double-click on it with the mouse or select it and press the key Enter . A list of elements of a lower level of the hierarchy will appear, in the top line of which the name of the open vertex will be indicated, equipped with the corresponding icon -. In the window title, the name of the open vertex will be added to the directory name.
Vertices located at lower hierarchical levels can be sequentially opened until only leaves remain in the list of elements. In this case, all overlying vertices, which include this vertex or leaf, will be shown in the window.
To close a vertex, double-click on it with the mouse or use the keyboard shortcuts or PageUp set the pointer to the line with the name of the open vertex and press the key Enter .
To return to any of the higher levels of the hierarchy, close the vertex one level below the desired one.
Directory elements can be combined into groups. If the directory objects can be grouped, then in the menu Actions command is used A new group, and the button on the window toolbar A new group(yellow folder on top panel any open form). For each new group a unique code is automatically set (but you can change it) and a name. To open a group, double-click on the " yellow folder» or choose Actions \ Next Level. Some directories allow you to create a tree of groups, that is, groups within groups (groups of the second, third, etc. levels). The group tree is displayed on the left half of the multi-level directories. By clicking on the +(-) sign on the group tree, you can collapse and expand the group structure. double click by folder on the tree opens the group. To return to the list of the previous level, double-click on the icon. open group (open folder) or choose Actions \ Previous level.
Fig.8. Moving through the levels of the directory.
Subordinate directories. The system allows you to link elements of different directories. For example, in the guide Treaties the numbers of contracts concluded with a specific organization (or individual) are stored - an element of the directory Counterparties. They say that one directory is subordinate to another. In this case Counterparties- directory-owner, and Treaties- subdirectory.
TYPES OF DIRECTORIES
Nomenclature.
This directory is designed to save a list of goods and materials - goods, products, materials, services, etc. - which are performed and received. It is used both for filling out documents and for conducting analytical accounting for accounting accounts. You can use multiple units of measurement for the items in this directory.
Non-current assets.
The directory is designed to save a list of fixed assets, intangible assets, other non-current tangible assets, other non-current assets and is used when editing documents and when maintaining analytical accounting according to a three-level structure, which makes it possible to combine its elements into arbitrary groups and subgroups.
Each non-current asset is characterized by a type that is specified in the “Type of non-current asset” variable, unambiguously defines the account and accounting methodology for the asset and can take on the following values: “Fixed assets”, “Other non-current tangible assets”, “Intangible assets”, “Other non-current assets ". Unlike the "Inventory and Materials" directory, there is only one unit of measure for the elements of this directory.
Product warehouse.
This is a subordinate directory; directory - owner - "TMC".
For each inventory item with the “Products” type set, the “Product composition” directory contains a list of materials and other costs that usually go to the production of this inventory item. In addition to the standard menu, the reference book can be opened using the "Product composition" button when editing goods and materials of the "Products" type.
Subordinate directory directory "Nomenclature". It stores a list of consignments of goods and materials. In the case of using the accounting method "By batches", entries in the document are created automatically - when posting receipt documents, for example, "Incoming invoice" or "Calculation". To account for goods and materials at a weighted average price in the directory, there is only one lot - "Batches by default", according to which all accounting is kept.
Units.
Directory - owner - "TMC". Editing occurs by opening from the "Inventory" and selecting the type of inventory, the units of measurement of which must be edited, and then going to the "Units of measurement" reference book. An important attribute of the unit of measure is the “Coefficient”. It sets the conversion factor from the specified unit of measure to the base one (which takes into account the balance of goods). For example, if a certain product is accounted for in pieces and packages, and there are 5 pieces in one package, then for such a product you can set the base unit "pcs." (in the “Inventory and Materials” directory itself), and in the “Units of Measurement” directory, create an additional unit “pack.”, for which specify the conversion factor equal to 5.
The directory is designed to save a list of currencies and their rates, and is also used when generating accounting entries for accounts that have the sign of currency accounting. The directory has a single-level structure. On the “Basic” tab, for each element of the directory, you must specify the name, short name, NBU rate and multiplicity.
Money accounts
single level; subordinate directory "Contractors". Save the lists of counterparty accounts for making cashless payments.
Storage locations.
Designed to maintain a list of storage locations for inventories (fixed assets, intangible assets, materials) of an organization. This directory is used both for issuing primary documents and for conducting analytical accounting for accounts 10,11,12,20,26,28, etc.
Each element of the directory is characterized by the type of storage location - a warehouse or a materially responsible person, who has a specific accounting object, as well as the actual name of the storage location.
Our company accounts.
A list of all places where cash and non-cash funds can be stored is stored here. This directory is used both for editing documents and for conducting analytical accounting for such accounting accounts: “Cashier”, “Bank accounts”, “Book value of currency (tax accounting)”.
Employees.
The directory is designed to store information about employees working in the organization. The directory has a three-level structure; employees in it can be combined into arbitrary groups and subgroups.
Gross income/expenses.
The reference book is used for conducting analytical accounting on off-balance accounts "Gross expenses" and "Gross incomes". Implemented on three levels. The top level is BP's Gross Expenditures, IA's Gross Revenues, and the Non-Income and Non-Expense element. The directory is organized according to the structure of the Profit Declaration. If in each posting on the accounts of BP, VD in each document it is correct to indicate the article from this directory, then the Profit Declaration will be generated automatically.
Cost types
A three-level directory used when creating documents and maintaining analytical accounting for accounting accounts.
Taxes and deductions.
The directory contains a list of taxes, deductions, fees to various state trust funds, as well as Additional information about each type of deductions and taxes: rate, border, recipient organization, etc. The reference book is intended for automatic calculation of the amounts of taxes and deductions, when editing documents and maintaining analytical accounting. Each element of the directory is characterized by a unique text code and a set of props.
COMPLETING THE DIRECTORY "NOMENCLATURE".
Let's repeat the concepts of the reference book " Nomenclature”, which stores a list of goods and materials and services provided or received.
For the convenience of searching for information, the directory is implemented as a three-level one, i.e. TMC in it can be combined into arbitrary groups and subgroups. Prices with and without VAT for the selected element are displayed in the lower part of the lookup window.
With the button " Open parties» you can view a list of all batches of goods and materials, on which in this moment cursor is set.
Information about each element of the directory is divided into tabs « Prices" and " Additionally". Each inventory item has a selling price and a selling currency. This means that when issuing documents (invoices and waybills), these prices will be converted into the currency of the generated document at a predetermined rate. The purchase and sale prices are kept without VAT, and the retail price - with VAT, and only in UAH. Note that prices cannot be “fixed”, they can be changed in each specific document.
We indicate the features of accounting for each type of goods and materials:
« Product". Goods are goods and materials that can be bought and sold. Accounting account of goods by default - 28.1.
« Service". Service refers to both the services provided by us and those provided to us by third parties. The default service account is 84.
« Tara". The difference between the container and the goods is only in the peculiarities of filling out the document "Incoming invoice". The default tare account is 28.4.
« Products". This term refers to goods and materials produced and sold by our organization. The default production count is 26.
« Semifinished". This is an inventory item that is produced and sold by our organization and included in the composition of products during production. The default count of semi-finished products is 25.
« Material". This is an inventory item that is purchased by an organization and then used to manufacture products. The default account is 20.1.
« Building material», « Fuel". These types of goods and materials include building materials and fuel used for own needs. These items are not for sale. The default account is 20.5 and 20.3.
« Spare parts". This includes spare parts and assemblies intended for the repair and replacement of worn-out machines, vehicles, and various equipment. Accounting for account 20.7
« MBP". Low-value and wear items (small equipment, inventory, overalls, etc.). Account - 22.
For each type of goods and materials, in addition to the accounting account, it is also necessary to indicate the cost account and type of costs that will be used when generating postings for the receipt of services and for writing off goods and materials to production. FOR MBP in the details " Check" and " Cost type» it is necessary to indicate the account and item of expenses to which the accounting cost of the IBE will be written off when they are put into operation.