Support > Repository > Primary key option > Branch number applying narrow-down function with single key
ja | en

We will explain how to calculate the maximum number of branches by narrowing down our own model.

"Branch number by compound keyPlease look through.The method of setting "branch number" as part of the composite key is easier to set than the content described on this page.

I will explain how to set the branch number function with a single key, not a compound key here."Reference other model> narrow down the choices by the value of other itemsApplication function.

specification

The settings described here are based on the following specifications.

  • "Customer ID" of the customer model is a character string type and uses the order.
  • In the normal new registration processing, the customer ID increases to "1000", "1001", "1002", ....
  • In the copy registration function, the order is not used.For this reason, duplicate keys are given in copy registration.Then assign a branch number.
  • The branch number is expressed as "1000 - 1" by adding a hyphen and a number to the end.
  • When copying and registering the same data over and over, "1000 - 2", "1000 - 3", ... are incremented each time.
  • The branch number is not included as part of the primary key.

Behavior

We will create a new customer model.The first customer ID is "1000".This is allocated using order.

Figure 1 Customer registration
Figure 2 No branch number is assigned to customer ID

Use copy registration function.

Fig. 3 Copy registration

The branch number is given.After that, if you register for data with customer ID "1000", the number of branches will increase.

Figure 4 A branch number was assigned to a customer ID
Fig. 5 Copy registration was done (branch number increased)

There are four items related to primary key and branch number.

Figure 6 Defining customer model
item name Item ID Type Primary key order DB storage Hidden on input Hidden on output Calculation at registration Other
Customer ID customerid String - - [To be described later] Do not use the order when registering
Customer ID ordering part customerseq Numerical value - - [To be described later] -
Maximum branch number max_branch_no Model reference
(Your own branch_no)
- - - - Refine by [see below]
Branch number branch_no Numerical value - - [To be described later] -

Customer ID

The customer ID is the primary key.In this example it is a string and the order is used.The starting number is from 1000.

Since it is a primary key, it is automatically mandatory.Moreover, it becomes a hidden item when inputting.

Figure 6 Customer ID definition (using string type and order)

The expression at registration is as follows.In the case of copy registration, branch numbers are assigned, and in case of new registration, standard order values ​​are used.

CONTATENATE ("-", TOSTR ($ {max_branch_no})), "IF" (EXACT (SCREENTYPE (), "copy"), CONCATENATE (TOSTR ($ {customerseq}), IF ($ {max_branch_no} ), $ {Customerid})

Based on the value of the customerseq item, if the value of max_branch_no is greater than 0, give the value of max_branch_no after "-".This is the branch number part.

CONCATENATE Returns a string that combines the argument strings.
TOSTR Converts a number to a string.

Figure 7 Primary Key Expression

Customer ID ordering part

The customer ID ordering section prepares as an item to cut out and store only the ordinal part of the customer ID.DB save it, and make it hidden when inputting and outputting.

The expression at registration is as follows.In the case of copy registration, the order currently held is used as it is, and in the case of new registration, the sequence part (numerical part) is cut out.

IF (EXT (SCREENTYPE (), "copy"), $ {customerseq}, TOINT (LEFT ($ {customerid}, IF (FIND ("-", $ {customerid})> 0, FIND ("-", $ {Customerid}) - 1, LEN ($ {customerid}))))))

Holds the customer ID value converted to a number.At this time, if there is a branch number (beginning with "-"), remove it.

TOINT Converts the argument string to a number.
LEFT Gets the number from the left of the argument string.
FIND Returns what number the specified character is contained in.
LEN Returns the length of the string.

Figure 8 Expression of customer ID order part

Maximum branch number

For a group of data having a specific "customer ID ordering part", it is set to hold the value of the maximum branch number.

Fig. 9 Image of narrowing down

Suppose we are referring to our own model (self) and displaying the value of the branch number in the list box, furthermore "narrowing down by the order part" is carried out, "rearranging in the descending order of the branch number", " By doing so, we will obtain the maximum value of the current branch number.

Figure 10 Model reference setting
Fig. 11 Refine setting
Figure 12 Sort in descending order of branch number

Branch number

Holds the value of the current branch number.

The expression at registration is as follows.

IF (ISBLANK ($ {max_branch_no}), 1, $ {max_branch_no # content} + 1)

Save 1 if the maximum branch number is blank, and +1 if it already exists.

ISBLANK Determines whether the argument is blank.

Figure 13 Expression of branch number
In the first configuration, it is a good idea to make the above items that the system use internally "read-only" instead of "hidden".The test can grasp the movement of the branch number.Please make it "hidden" where you can check the operation.

Screen example

Figure 14 shows an example where the branch number part is zero-filled and three-digit notation.

Fig. 14 Branch number part is zero padded 3 digit notation

Setting customer ID

Change the formula for registering the customer ID to use the TEXT function as follows.

IF ($ {max_branch_no}> 0, CONCATENATE ("-", EXTT (SCREENTYPE (), "copy"), CONCATENATE (TOSTR ($ {customerseq}TEXT(${max_branch_no}, "000")), "")), $ {Customerid})

TEXT Perform formatted conversion.

Screen example

Figure 15 shows an example in which the branch number is alphabetized ("A", "B", "C", ...) and no hyphens are given.

Figure 15 Branch number is alphabetical and without hyphen

Setting customer ID

Change the formula for registering the customer ID to use the A1FORMAT function as follows.

IF (EXACT (SCREENTYPE (), "copy"), CONCATENATE (TOSTR ($ {customerseq}), IF ($ {max_branch_no}> 0,
      A1FORMAT(${max_branch_no}), "")), $ {Customerid})

A1FORMAT Replaces numeric values ​​with alphabets in accordance with Excel's column name rules.

Setting of customer ID ordering part

Change the expression when registering the customer ID ordering part so that it is determined by regular expression as follows.

IF (EXACT (SCREENTYPE (), "copy"), $ {customerseq}, TOINT (LEFT ($ {customerid}, IF (FINDRE("[A-Z]", ${customerid})>0,
        FINDRE("[A-Z]", ${customerid})-1, LEN ($ {customerid})))))

FINDRE If it contains a character that matches the regular expression, it returns its position.