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

"Branch number with compound key "beforehand.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> Apply the function to narrow down the choices by the value of other items "function.

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.

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] | - |

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.

IF(EXACT(SCREENTYPE(),"copy"), CONCATENATE( TOSTR(${customerseq}), IF(${max_branch_no}>0, CONCATENATE("-",TOSTR(${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 concatenated with the argument string.

TOSTR converts numbers to strings.

Figure 7 Primary Key Expression

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(EXACT(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 the order of the specified character.

LEN returns the length of the string.

Figure 8 Expression of customer ID order part

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

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.

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

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

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

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

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.

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}) ) ) ) )

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