Attention! This is an introductory version of the lesson, the materials of which may be incomplete.
Login to the site as a student
Log in as a student to access school materials
Data composition system 1C 8.3 for beginners: connecting data sets
- Write a report that displays customers and their favorite products. Each client has a favorite color, and each product has its own color - based on these colors, you need to determine the “favorite” of the product. For example, if Andrey’s favorite color is red, then one of his favorite foods will be tomatoes (they are red).
- Apply two sets of data in the report. The first set is data from the "Clients" directory table. The second is data from the “Food” directory table.
- Implement connection between these two sets, so that the report contains only the favorite products for each client.
Create a new report
Open the "Gastronom" database in the configurator and create a new report through the main menu:
Document type - "External report":
In the form of a new report, indicate the name “Lesson 6” and click the “Open data composition diagram” button:
Leave the default schema name:
Adding the first data set
In the diagram that opens, go to the “Data Sets” tab and select “Add Data Set - Query” using the green plus sign:
Call the request constructor:
We indicate the "Customers" table and the fields that need to be obtained from the request:
Adding a second data set
Add a second set of data:
Select it (DataSet2) and call the query constructor again:
We indicate the table of the "Food" directory and the fields that need to be obtained from the request:
The resulting request text was:
Please note that we currently have two data sets in the report: Data Set1 and Data Set2. Each has its own request text and data.
Making names more descriptive
For clarity, let's rename DataSet1 to Customers and DataSet2 to Food.
Do this by double clicking on each of the sets:
We can use the data from each set in our report. We will access this data through fields.
IN this moment The “Customers” set has the following fields: “Name” and “FavoriteColor”, and the “Food” set has: “Name” and “Color”.
We see that the names overlap and we can easily get confused. So let's change the field names to be more descriptive.
Select the “Clients” set and change the field names like this:
Then select the “Food” set and change the field names like this:
Calling the settings constructor
Finally, go to the “Settings” tab and click the magic wand to bring up the settings designer:
Report type - "List...":
Select fields for the report from both sets:
See why it was so important to change the field names? At the stage of data composition settings, we do not see which sets these fields are from. We only see their names.
Checking the report
We save the report and generate it in user mode:
Yeah. Okay, but not really. A so-called cross-join of two sets has occurred (you should be very familiar with this from the joins in queries that we studied in previous modules). Each record from the Customers table has a corresponding record from the Food table.
But from all these records we need to leave only those whose field “Customer’s FavoriteColor” is equal to the field “FoodColor”:
We connect two sets of data
To do this, we will connect two sets of data (Customers and Food) using the fields Customer's FavoriteColor and FoodColor.
Go to the “Data Set Links” tab and click on the plus button to add a new link:
We configure the parameters as in the figure below:
I'll make an explanation.
Source and receiver of communication. Well, everything is clear here. We indicate the first set (Customers) and the second set (Food) of data. I would like to draw special attention to the fact that communication will be carried out according to the principle outer left join(we covered it in the topic of requests in previous modules). Based on this, you need to choose which set will be the source and which the receiver.
Expression source. We indicate here an expression or simply a field from the source data set (we specified the ClientFavoriteColor field from the Clients set here).
Receiver expression. We indicate here an expression or simply a field from the receiver data set (we indicated the ColorFood field from the Food set here).
Thus, this connection will leave from the previous list only those rows for which the Customer's FavoriteColor field is equal to the FoodColor field.
Let's save the report and run it in user mode:
Great!
I'll make an explanation about the field " Communication condition", about which novice programmers so often hit their spears.
The connection condition is an auxiliary field. You can write expressions there involving fields only from the data set specified in the link source.
In this case, for all rows from the link source, this expression (link condition) will be checked before the link is made. And if this expression is TRUE, then an attempt will be made to establish links between this row and rows from the link receiver. If the expression is FALSE, then no such attempts will be made.
At the moment, perhaps the best ACS console and more. Huge functionality. The author has done an incredible job! Allows you to create a report on the access control system in enterprise mode and export it to an external report with a choice of template.
From the functions:
1. Works in any configuration
2. The ability to create an arbitrary hierarchy with convenient transfer of elements
3. Advanced editing of parameters (periods, succession parameters)
4. Execution in the background (without configuration modification)
5. Comparison of query texts and query results
6. SKD
Flexibility, convenience and customizability. The code is open.
Registration of wishes and correction of errors via http://www.1cpp.ru/bugs/ project Query Console for 1C 8.1 (preferably)
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:
- Output of selection 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 SKD 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!