セットと命名セレクション

4D v16

セットと命名セレクション

ビデオ中で説明されている操作に加え、以下の点にも留意してください。
メモリの観点からみると:

セレクションの場合、セレクション中のレコードごとに4バイト必要です。
セットの場合、テーブル中のレコード数ごとに1ビット必要です。
セットは一連のビットで表現されます。総ビット数はテーブルの総レコード数に対応します。セットの各ビットはそのレコードがセレクションに含まれているかいないかを表します。

以下の表はセットと命名セレクションの違いをまとめたものです:

テーマ	セット	命名セレクション
1レコードあたりのメモリ消費量	1 bit	4 bytes
並び順を保持	No	Yes
カレントレコードを保持	Yes	Yes
結合	Yes	No
交叉	Yes	No
差	Yes	No
ディスクに保存	Yes	No
20,000テーブルレコードより10レコード選択時の使用メモリ	20,000 bit = 2,500 byte	10 x 4 byte = 40 byte

セット間の演算は同じテーブルのセットでのみ可能です。

警告: セットはレコードの削除などが行われると有効ではなくなります。限定した時間内でのみ利用すべきです。またセマフォーなどの使用も考慮すべきです。

セットのビットは各テーブルレコードの位置に対応します。レコードの削除と追加が行われると、削除されたレコードが新しいレコードに置き換えられるかもしれません。このときセットは期待したレコードを含んでいない状態となります。

しかしながら依然としてセットは二つのセレクションを比較する素早い方法です。

セレクションを保持する方法としてはほかにSELECTION TO ARRAYコマンドを使用して、レコード番号配列を取得することもできます。

メモリ中に保持したセレクションのコピーが必要なくなった時は、それを削除してメモリ領域を開放すべきです。

 CLEAR SET("SetName")

 CLEAR NAMED SELECTION("SelectionName")

Video script

In this video, we're going to learn how to keep a selection and perform functions (union, intersection, and difference).

Sets are a simple way to intersect lists of records from a single table.

They are one of the ways you can use to put a selection on hold so that it can be used again later on.
By performing an operation on two sets, we get a new set containing the result of the operation performed (according to conventional set theory).

We're going to apply these principles right away to the list of interventions in the navigation form. We can create a set from the current selection.

We're going to create a button that searches for all the interventions that were started before 9:00 a.m.

Here is the method of the button:

QUERY([Interventions];[Interventions]Intervention_Time

and we're going to create a 2nd button that will give us all the interventions that are not yet 100% completed. We can program this button as follows:

QUERY([Interventions];[Interventions]Progress<100)

For the moment, in the file we imported, the progress % is not filled in.

So first we're going to apply a formula on all the interventions. Here we have a selection of 11,732 interventions where we're going to apply a formula and specify that:

progress is equal to the rounded-off value (returned by Round) of the remainder (calculated with Mod) of Random (which returns a value form 0 to 32,767), the Mod per 100 rounded off to the nearest tenth.

If we now produce a report to check what progress % values have been filled in, we see that they range from 0 to 100.

Of course, if we click on the 1st button and then on the 2nd, we will have lost the selection created during the 1st click.
So in the 1st button, it may be useful, after the query, to keep the information in a set that we can call "Morning_Interventions".

CREATE SET([Interventions];"Morning_Interventions")

Similarly, here, after retrieving the set of interventions that have not yet been completed, we can call this set "Interventions_in_progress".

CREATE SET([Interventions];"Interventions_in_progress")

Then we can compare the sets:

By adding them together to obtain the set of morning interventions + interventions in progress.
We duplicate the button and indicate that these are the interventions before 9:00 a.m. + those still in progress;
Then we can just write
UNION("Morning_Interventions";"Interventions_in_progress";"Interventions_Result")

in a 3rd set call Interventions_Result so as not to overwrite the first two sets.

If we want morning interventions that are still in progress, we have to compare the common part of both sets, so that we get all the morning interventions that are still underway. In this case, the command to use is not UNION, but INTERSECTION.

And if we want to get completed interventions, we need to apply DIFFERENCE to the 2 sets: all the morning interventions minus the ones still in progress will give us the resulting set.

We can test these different possibilities by executing the navigation form.
We take all the interventions, there are 11,732, and here (after clicking on the query buttons), we still have 11,732.

Why is that? Remember that in the previous video, in the Navigation_Function we added, at the very bottom, this line that gives us the number of records found after a new selection is created.

vNumRecords:=Records in selection([Interventions])

We're going to copy this code and apply it to the 2 query buttons.
Currently, we do not have the table pointer here; we're going to process the interventions.

Then we can copy this line of code for 4 other buttons:

the union, we see that on these 3 buttons, something is missing...
the intersection
on the difference
and also here on the query button.

Now, if we go back to the navigation form, we have:

631 interventions before 9:00 a.m.
11,192 interventions that are not yet finished.

Interventions before 9:00 a.m. + those at 100%, 9:00 a.m. interventions still in progress or 9:00 a.m. interventions that are finished, don't seem to change the number that is here.

Why is that? It's because simply creating a set does not imply its use.
In this button here, after we have created the union that creates a 3rd set, we must indicate the particular set that needs to be used.

So we're going to modify the code and:

Here, we add the USE SET command.
Copy, then we're going to put it in the other 2 buttons.
So we add the line for the set to use in the other 2 buttons

And that way we can test:

interventions in progress: we have 601
completed interventions: we have 30
Which corresponds to the interventions before 9:00 a.m.: in other words, 631

To complete this approach, we're going to close the form and launch the Navigation method again to check its operation.
If we click on one of the three buttons to the right, an error message indicates that the sets do not exist. Naturally, the sets must exist before we can use them.
We're going to trace the creation of these sets by clicking on the 1st query button.

We can see, in the list on the left, the "Sets" theme that shows the list of existing sets in the process being traced. Currently, there are none.

When the method is executed:

We name a first set
Then we're going to name a 2nd one while executing this one
Using trace mode, this will allow us to see the creation of the 3rd set when the 1st line is executed.

And then we'll see, because the set already exists, it's only the quantity that changes.

You need to keep in mind that a set is only a list of records that is not necessarily the current selection since right now the current selection of the Interventions table contains 601 records.

When we switch to the USE SET command, the selection now contains a number of records matching the number indicated in the set and the vNumRecords variable is also adapted accordingly.

Another use of sets that is very convenient consists in selecting a certain number of records and then indicating that we want to make them the current selection; in other words, when we click the button, there are now only these X records.

To do this, we must:

Program the button
Indicate that we want a selection
Copy an entry in the method
And write the corresponding code.

The code looks like this:

 $SetName:="Userset_"+String(Milliseconds)

 GET HIGHLIGHTED RECORDS($TablePointer->;$SetName)

 USE SET($SetName)

 vNumRecords:=Records in selection($TablePointer->))

In other words, we're going to:

Create a set name (such as a text part + the conversion into text of the Milliseconds)
Get the highlighted records, like those that were clicked in the table in question, and create the corresponding set
Use this set
Recalculate the number of records to which this corresponds
Then clear the set so as to free up memory.

We can test:

all the interventions
We can select a few of them
And we draw the selection button

At this level, we have:

a set name
the set will be created here; so we have 3 items in this set.
We'll use it so that it becomes the current selection of the Interventions table
We'll recalculate the number of records
Then we'll clear the set.

In our array, we now have the list of records that we selected.

A set is made up of one bit per record and therefore does not preserve the sort order.

When you need to keep this information, you must use commands related to named selections:
COPY NAMED SELECTION and USE NAMED SELECTION

Here we're going to add a button that allows us to:

Create a first selection (T1 named selection)
We'll copy the code into the named selection
And duplicate the button: T2

It's now possible to store 2 selections that will take the sorting order into account.

They exist in memory but will not be used.

Then we're going to:

Duplicate these buttons
Modify their formula to request the use of these selections (here we don't need to specify the table.)

If we take all the interventions:

Let's take the 1st ones
Selection
Sort them by date and time of the intervention
Create a 1st selection
Then take the same selection
We'll sort it by object and intervention time
And then we create the 2nd selection.

Now:

If we call back the 1st selection, it comes sorted according to the criteria indicated: date and time
If we click on the 2nd button, they are sorted as we requested first by object and then by date.