ACS is not only for reports - implementation of universal selections. Using selections in SKD SKD transfer of value in selection

Query language extension for data composition system

Extension of the query language for the data composition system is carried out using special syntactic instructions enclosed in curly braces and placed directly in the query text.

Syntactic elements of the data composition system query language extension

CHOOSE

This sentence describes the fields that the user will be able to select for output. After this keyword, aliases of fields from the main query selection list that will be available for configuration are listed, separated by commas.

For example:

(SELECT Item, Warehouse)

After the field alias there may be a combination of characters “.*”, which indicates the possibility of using child fields from this field.

For example, the entry Nomenclature.* indicates the possibility of using child fields of the “Nomenclature” field (for example, the “Nomenclature.Code” field). The SELECT element can only appear in the first join query.

WHERE

The fields on which the user can apply selection are described. This proposal uses table fields. The use of selection list field aliases is not allowed. Each part of the union can contain its own WHERE element.

(WHERE Nomenclature.*, Warehouse)

A simple example

It is necessary to get sales for the period + display the entire item, regardless of whether there were sales or not. That is, you need to select data from the turnover table of the Sales register, data from the nomenclature directory. Let’s skip the question of why we need all the nomenclature.

To solve the problem, you can create a query that connects the item and the Sales turnover table with a left join, as a result, for the item that was not sold in the selected period, we will get the field values Counterparty, Agreement, Quantity, Sum= Null. This request:

SELECT ReferenceNomenclature.Link AS Nomenclature, SalesTurnover.Counterparty, SalesTurnover.Counterparty Agreement, SalesTurnover.QuantityTurnover AS Quantity, SalesTurnover.CostTurnover AS Cost FROM Reference.Nomenclature AS ReferenceNomenclature LEFT CONNECTION RegisterAccumulations.Sales.Turnover AS SalesTurnover PO SprNomenclature.Link = SalesTurnover.Nomenclature

Here's the result:

Counterparty	Agreement	Nomenclature	Quantity	Sum
null	null	_Test1	null	null
LLC "Horns and Hooves"	Agreement1	Slippers	10	1200
"Gazprom"	Cool contract	Boots	5	13000
null	null	Galoshes	null	null
null	null	Slates	null	null

In this example, there were no sales of the items: “Overshoes” and “Slippers”

And everything would be fine, if we group the sample by counterparty, then all unsold items will fall into a separate group, where Counterparty = Null, but the client wants to have a random selection in the report by the counterparty field (naturally, this means the counterparty from the Sales register). What should I do? After all, in fact, we only need to filter the table Sales. If we use autocomplete in the ACS constructor, then the available selection fields will include the field Counterparty, everything seems to be fine, but when executing a report with selection by counterparty, we will lose all records from the connection with the item. For example, let's set the selection: Counterparty = LLC "Horns and Hooves". The result will look like this:

Not at all what we need, right?

To solve the problem, you can, of course, add a parameter to the query, for example, to the parameters of a virtual table SalesTurnover, but at the same time the flexibility of setting the type of comparison is lost.

There is a solution: for this in query builder on the tab Data composition => Tables add a condition field to the virtual table conditions SalesTurnover and change his nickname to CounterpartySelection

In order not to confuse the user with selection fields, we will disable the condition field Counterparty and change the title for the field CounterpartySelection

As a result of executing this scheme with selection enabled by the counterparty field, the resulting query will take the form:

SELECT SprNomenclature.Link AS Nomenclature, SalesTurnover.Counterparty AS Counterparty, SalesTurnover.Counterparty Agreement AS Counterparty Agreement, SalesTurnover.QuantityTurnover AS >Quantity, SalesTurnover.CostTurnover AS Cost, PRESENTATIONLINK(SalesTurnover.Counterparty Agreement) AS ContractCounterparty aPresentation, PRESENTATIONLINK(SalesTurnover.Counterparty) AS CounterpartyPresentation, SprNomenclature.Presentation AS NomenclaturePresentation FROM Directory.Nomenclature AS SprNomenclature LEFT CONNECTION Register Accumulations.Sales.Turnover(&P , , , Counterparty = &P3 ) AS SalesTurnover BY SprNomenclature.Link = SalesTurnover.Nomenclature

And accordingly the result:

Counterparty	Agreement	Nomenclature	Quantity	Sum
null	null	_Test1	null	null
LLC "Horns and Hooves"	Agreement1	Slippers	10	1200
null	null	Boots	null	null
null	null	Galoshes	null	null
null	null	Slates	null	null

Test1 is a group in the Nomenclature directory in which everything is located

Attached to the publication is the XML schema of the report used in the publication. I created the circuit in Integrated Automation, but I think everything will work fine in both UPP and UT 10

Summarizing

This example shows how to create your own selection settings in the access control system and disable the auto-created ones if you are designing a circuit with the flag enabled Autocomplete.

If you have enough experience to create circuits without using Autofill- then this advice does not make sense.

Software used

• Screenshot program SnimOK!
• XML File Editor

What can SKD be used for?

Established opinion is for reporting purposes.

In fact ACS capabilities go beyond creating universal reports.

And today we will show how using the access control system, the user will be able to configure flexible data selection. Further, this selection will be used when obtaining and processing data.

For what tasks will this be useful?

Let's give examples from typical configurations:

• Processing “Uploading data to the site”
• Formation of a price list
• Uploading data to TSD (data collection terminal)
• Segmentation of products, partners
• Formation of orders (clients, suppliers) according to needs
• Repair planning in 1C:ERP.

That is, the tool is useful wherever it is necessary to provide the user with wide selection options.

Creating a random selection in a controlled form using ACS

The lesson covers working with settings builder data layout schemes:

• Selection output on the form
• Software connection between the settings builder and the layout diagram
• Creating default selections in the access control system.

Receiving data from the database with filtering by ACS selection

The video shows how you can select data using the previously set selection in the settings builder.

A “scary” object is used – ProcessorOutputResultCompositionDataIntoValueCollection.

In fact, not everything is so complicated - in 8 minutes we solve the problem.

Storing SKD selections in the information base

In the lesson we will look saving ACS builder settings using the Value Store.

Let’s figure out how to solve this problem in the “1C: Manufacturing Enterprise Management 1.3” configuration.

Enjoy watching! :)

In general, ACS provides a lot of opportunities.

Here are a few “tricks” that we didn’t even have time to describe on the course page.

If you need to collect data from different sources, there are three options.

You can try to compose one “universal” query for all data (long), you can display the data of several queries in a loop (rigid, inflexible structure) - or you can simply use ACS and connection of data sets instead of one request.

At the same time, it is important to obtain correct results - this has its peculiarities if there are several sources

With the help of SKD you can simply get a snapshot of the latest for each date in the report.

Or, for clarity, - get the product price for each sale date.

With the help of ACS you can organize displaying all dates for the period in the report, and not only those for which there was data in the report (adding dates without programming, only with the capabilities of the access control system)

Using ACS, you can organize nested groupings with the addition of periods (year/quarter/month, etc.)

Arrange the report as desired, for example, display the column header vertically and the data in this column horizontally.

Allow the user to choose the frequency with which data will be displayed in the report (by year, by quarter, by month) - solely by settings, without editing the report module.

How to combine several conditions using OR in a report? This option, for example, is not available in the report builder, but is available in the ACS

If you display summary data in the form of a chart, you need to be able to precisely control its appearance: the visibility of markers for a series, alternating colors of chart series, separating past data from future data on a chart with a vertical line, etc.

Of course, there are nuances that need to be paid attention to.

When creating the report, the developer debugged the query text, but when using this query in a report on the access control system, the system produces an incorrect result.

Therefore, it is important to be able to obtain the query that the system actually executes to obtain data from the database, and to debug such a query.

When adding details to a document-recorder to a report, the system sometimes produces “incorrect” opening and closing balances.

Therefore, it is important to correctly configure the fields in the report on the access control system so that the balances are displayed correctly both with and without detail to the document.

The report result may change depending on where selection by resource value is configured - at the report level and at the level of an individual group, you need to monitor this.

If you want to master ACS professionally and daily apply in your work, sign up for the course:

Support - 2 months. Course scope – 34 teaching hours.

Don't delay your studies!

To write processing, programmers often had to use queries to obtain data and then process it. The data, in turn, was obtained from the request. Well, a request without selection or filter is rare. Let's talk about selections in such requests, using the example request:

In order to organize selection by counterparty for the user in a regular form, the programmer had to place three elements on the form, which looked something like this:

How much effort does it take for a programmer to describe different types of comparisons (equal, not equal, in a list, in a group...) and, based on these types of comparison, refine his final request for obtaining data.

Let's look at how this can be done using ACS. In our processing, let's create a Layout with the Data Composition Scheme type and fill it with our request:

On the Settings tab, we will add a new grouping without detail and, in our example, the Account field, since in the end we will get everything into the table of values:

And on the Selection tab, add the Counterparty to the selection:

Now let's start creating the form. Let's display on the form of the processing itself the Selection with which the user will work. We will display an element of the Table Field type on the form and give it the name Selection with the data type Composer.Settings.Selection:

Now let’s create event handlers for the OnOpen form and a handler for clicking the Run button, the code is presented below:

Perem Layout; Procedure ButtonExecutePress(Button) Result.Clear(); LayoutLinker = newDataLayoutLayoutLinker; Layout Layout = Layout Composer.Execute(Layout, Composer.GetSettings(), Type("DataCompositionLayoutGeneratorForValueCollection")); CompositionProcessor = newDataCompositionProcessor; LayoutProcessor.Initialize(LayoutLayout); Output Processor = New Output Processor of the Result of Composition of Data into a Collection of Values; OutputProcessor.SetObject(Result); OutputProcessor.Output(CompositionProcessor); If FormElements.Result.Columns.Quantity() = 0 Then FormElements.Result.CreateColumns(); endIf; EndProcedure ProcedureOnOpen() Layout = GetLayout("Layout"); SourceAvailableSettings = New SourceAvailableSettingsDataComposition(Layout); Linker.Initialize(SourceAvailableSettings); Linker.LoadSettings(Layout.DefaultSettings); EndProcedure

The processing is ready, having launched it, you can immediately see in our Selection the Counterparty that has appeared in our Selection, from which you can select any type of comparison, as well as add additional selection lines using the details of the Counterparties directory:

That's all, I hope this article will help you improve the flexibility of selections in your processing.

Kononov Sergey