【DataModel】Extending the standard writeback form - Cascading Lookup

As I wrote in my previous article [[DataModel] Extending the standard writeback form - Labels and Input Types](https://qiita.com/Miki_Yokohata/items/9c99bb9f9211828c8576), when the table definition in the [Sharperlight](https://www.sharperlight.com/) datamodel is set as `Writable`, you can use the standard writeback form.

In the standard writeback form, the values can be entered using search windows, etc.
In that case, you may want to narrow down the search list for the 2nd filter with the selected value of the 1st filter.
I would like to configure it this time.

There are reference tables **Manufactures** and **Models**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1b1ace7c-898c-dc21-b7e3-41eb36eeba5d.png)
When entering the following **Transactions** data using the standard writeback form, each value will be entered using the each reference table above.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7b5338a-6975-bc89-75c6-0a373e323bef.png)
In that case, you want to narrow down the search list for Models by the entered Manufacture value. Now let's set it up.

## DataModel:The definition of Transactions table ##
In the Sharperlight data model, the **Transactions** table is defined like this:
This time, we will extend the standard writeback form of this table.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/1f82a615-04fc-e421-e4c4-6c5cf3527b1d.png)
### Adding JOIN by using ID field ###
The diagram above shows the JOINs are already defined, but I'll show you how to do it.

The JOIN will be created for each of the Manufacture_ID field and Model_ID field.
You can create and add them like this with a dedicated JOIN editor.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2b2a5275-521c-1a2b-2323-0fbd08377c59.png)

### Configure the attributes for Manufacture_ID field ###
#### Attribute: Lookup ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0b48997b-604b-aa2d-8d28-8e10ad2b3d2b.png)
Use the `Inherit` option to specify the reference table and the field to use for searching.
Select the Manufacturers table as the reference table and the Name field as the field to use.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6969d3bc-66e4-b701-bae0-ebcf93ff4a48.png)
#### Attribute: Custom SQL Validation ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/015371bc-ae18-d2ab-cf06-82be83f1df5b.png)
Enter the SQL statement to check the entered value here. `{_fieldValue}` is a `Sharperlight control tag` to reference the value entered in this field.
Since we set the Lookup attribute to use the **Name** field, the WHERE clause must be `where [Name] = {_fieldValue}`.

With the settings up to this point, a search window like this will be displayed in the standard writeback form.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b1a8a545-0cc7-a619-8eb5-1d50ed03bac9.png)
#### Attribute: Lookup Filter Driver ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5c3d06fb-0672-0054-ef16-620609968d68.png)
Declare that this Manufacture_ID field will be the driver for narrowing down the other field values.

### Configure the attributes for Model_ID field ###
#### Attribute: Lookup #### 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e120decd-5052-2009-6818-1dd4e4b2c72c.png)
Use the `Inherit` option to specify the reference table and the field to use for searching.
Select the Models table as the reference table, and the Code field as the field to use.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4614a136-0ab2-7852-06f4-079ca2e8a755.png)
#### Attribute: Custom SQL Validation ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2f55bb6e-2864-8a79-65ff-5cecab1c742e.png)
Enter the SQL statement to check the entered value here.
```
select [Row_ID] from [Models]
where [Code] = {_fieldValue}
    AND [Manufacture_ID] = (select [Row_ID] from [Manufactures] where [Code] = {/Manufactures/Code})
```
`{_fieldValue}` is a `Sharperlight control tag` to reference the value entered in this field. We set the Lookup attribute to use the Code field, so the WHERE Clause must be `where [Code] = {_fieldValue}`.
`{/Manufactures/Code}` is also a `Sharperlight control tag` and references the Code value obtained using the predefined Manufacturers table JOIN.

With the settings up to this point, a search window like this will be displayed in the standard writeback form.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/baf0fb3b-b095-06bc-4d08-b6cdea2abb2a.png)
#### Attribute: Lookup Filter Target ####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/52bba30e-a41f-1ff4-746f-4d84f4ccb03c.png)
Declare that this Model_ID field is the Target field of the Lookup Filter Driver field closest to the top.
In other words, the input value of Manufacture_ID, which is the closest `Lookup Filter Driver` field, will narrow down the search list of the Model_ID field, which is the `Lookup Filter Target` field.

The settings are now complete. Save the datamodel and start the Sharperlight service.

## The standard writeback form for Transactions table ##
Display the standard writeback form for the Transactions table with this URL.
```
http://localhost/DemoRest/EntryForm/?productcode=TRAIN2&tablecode=Transactions
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4349bd23-0a3c-25fe-eae1-694ed1848557.png)
Now open the Manufacture search window. You can open it with the button on the right end of the text field.
I will choose **Nissan**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/026aadb1-dfd2-ccf8-4635-c467cb4847b6.png)
Next, open the search window for Model. Well, the selected Manufacture value **Nissan** filtered the Model list.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/eef5fa95-855a-d774-481d-e75ef760190f.png)