どんな時にリンクを使うか?
リンクを使うと効果的な代表的なケースを紹介します。
一つの情報変更に複数セルの更新が必要な表(冗長なデータ)
顧客毎に営業担当を割り当て、担当者が携帯電話を持っているとします。この情報を一つの表で管理していると、担当者の電話番号が変わったら複数箇所書き換えが発生します。
冗長なデータ:営業担当「鈴木」の電話番号が変わると3カ所書き換えが発生
一つの情報を変更するのに複数箇所変えなくてはいけないのは保守性の悪いデータ管理です。このような場合、表を分割してリンクを使うと保守性が良くなります。(データベースの正規化)
テーブル分割:担当電話番号は担当者テーブルを参照
担当電話番号は担当者テーブルを参照しているため、担当者テーブルの電話番号を変更すれば参照している情報が一括で更新されます。
保守性の向上:担当電話番号の更新は担当者テーブルの電話番号の変更のみ
複数の表に関連があるとき
例えば、顧客一覧と注文一覧の表があった場合、関連があるならリンクでつなげると便利です。
関連のある表:注文テーブルの顧客は顧客テーブルと関連あり
顧客テーブルからは、詳細表示で顧客毎の注文データを確認出来ます。更に、顧客毎の注文データの集計も可能です。
関連データの一覧表示:顧客に関連する注文データの表示
注文テーブルからは注文した顧客の詳細情報を簡単に確認できるようになります。
リンク先の詳細表示:注文テーブルから関連する顧客データの表示
リンクの種類
リンクには2種類あります。キーリンクは、RDB(リレーショナルデータベース)のリレーションシップに相当します。
ダイレクトリンク
ダイレクトリンクではリンク先のレコードを手動で直接選択します。テーブル定義で「複数値」をオンにするとリンク先を複数選択出来ます。
ダイレクトリンクでは、ダイレクトリンク型のフィールドを1つ追加するだけでリンクの設定が完了します。
キーリンク
キーリンクでは自レコードの参照キーとリンク先のキーが一致するレコードを自動的にリンクします。キーリンクのフィールドは、リンク先のキーが「重複禁止」指定の場合は単一値、そうでない場合は複数値となります。重複禁止でないと、キーには同一の値を複数入力出来て、リンク先レコードが複数になるためです。
リンクの基本
多から1へのリンクが基本
リンクの基本は1対多の関係がある場合、多の方から1の方をリンクします。(Fullfree は多対多の関係もサポートしているのでやろうと思えばその逆も出来ます)
例えば顧客テーブルと予約テーブルがあった場合、一人の顧客が複数の予約を行うとします。この場合、顧客:予約=1:多 の関係があります。
リンクするには「ダイレクトリンク」型か「キーリンク」型のフィールドを追加する必要がありますが、そのフィールドは多の方にあたる予約テーブルに追加するのが基本です。リンクの設定方法は「階層とリンク」の説明をご覧下さい。
具体例
・予約管理デモの「予約」テーブル→「顧客」テーブルへのリンク
(リンクの設定は「予約」テーブルの「顧客」フィールドによるダイレクトリンク)
リンクでつなげて計算式で参照
リンクの目的は「つなげること」です。リンクによって別テーブルの項目を参照するわけではありません。別テーブルのデータを参照するにはリンクした後、計算式を使って参照します。
リンクでつながっていれば、計算/参照型のフィールドを追加するか、各型のフィールドで自動入力(計算/参照)の設定によって別テーブルの項目が参照出来ます。
尚、計算式を使った参照は、リンクしている側・リンクされている側どちらからでも参照出来ます。リンクされている側からの参照は「関連レコード (リンク元レコード)」の参照となります。
更に、リンクのリンクをたどって間接的にリンクしているデータを参照することも可能です。また、一度にたどれるリンクの段数に制限はありません。
計算/参照型の具体例
・予約管理デモの「予約」テーブルの「時間(分)」フィールドにて、リンク先の「コース」テーブルの「時間(分)」を参照
※あらかじめ「予約」テーブルでは、「コース」フィールドにて「コース」テーブルとリンクしている。
自動入力(計算/参照)の具体例
・注文管理デモの「注文」テーブルの「明細/単価」フィールドにて、リンク先の「商品」テーブルの「単価」を参照
※あらかじめ「商品」テーブルでは、「明細/商品」フィールドにて「商品」テーブルとリンクしている。
リンクでグループ化
リンクは「グループ化」の役割も果たします。予約テーブルから顧客テーブルをリンクする場合、予約テーブルは顧客テーブルの関連テーブルとなります。
関連テーブルは、設定中のテーブルのレコード毎にグループ化されるデータとも言えます。カード形式で開くとグループ化されたレコードが表示出来ます。
グループ化されたレコードは、計算式を使って集計が出来ます。SUM や AVERAGE 関数などに IF や AND 関数などを組み合わせて、条件付きの集計も可能です。
キーリンクにはフィールドが3つ必要
ダイレクトリンクの場合は、ダイレクトリンク型のフィールドを追加するだけで他テーブルとリンク出来ます。ただし、キーリンクやダイレクトリンクの自動入力を設定するには、他にフィールドが2つ(テーブル双方にキーが1つずつ)必要です。つまり、リンクのフィールドと合わせて3つのフィールドでリンクを実現します。
顧客管理デモを例に説明します。着信履歴テーブルから顧客テーブルをキーリンクしていますが、下記3つのフィールドでリンクが構成されています。
- 顧客テーブルの「電話番号」フィールド(検索されるキー)
- 着信履歴テーブルの「電話番号」フィールド(検索するキー)
- 着信履歴テーブルの「顧客」キーリンクフィールド(リンクの設定)
キーリンクでは、自テーブルの何と相手テーブルの何が同じレコードをリンクするのかを指定します。
ダイレクトリンクの自動入力の場合も同様です。自テーブルの何と相手テーブルの何が同じレコードを自動的に入力するか指定します。
このように、自テーブルと、リンクする相手テーブルとに同じ値が必要です。その上で、キーリンク型のフィールドを追加する必要があります。もし、顧客ID でキーリンクしたい場合は、両方のテーブルに顧客IDのフィールドが必要です。それから、参照する側のテーブルにキーリンク型のフィールドを追加して下さい。
基本操作
ダイレクトリンク・キーリンク共に、アイコンをクリックするとリンク先をカード形式で開きます。リンク先が未設定の場合は新規レコードの追加になります。
ダイレクトリンクの場合は項目部分をクリックするとリンク先を選択するためのドロップダウンリストが開きます。キーリンクの場合は設定された別のセル値から自動的にリンク先が決まるため編集は出来ません。
ドロップダウンリストで検索
ドロップダウンリストでは検索機能が使えます。ドロップダウンリストの上に縁にマウスを当てると、検索エリアを開閉するためのボタンが表示されるので、クリックして検索エリアを開いてから検索して下さい。
他テーブルの項目参照例
他テーブルのフィールド参照の方法を、デモデータの問い合わせ管理デモを例に説明します。問い合わせテーブルにて、顧客の住所を参照したいとします。
「リンクでつなげて計算式で参照」なので、まず、問い合わせテーブルから顧客テーブルへのリンクが必要です。デモデータでは「顧客」というダイレクトリンクのフィールドが既にあります。
次に、計算/参照型のフィールを追加し、計算式には顧客のリンク先の「住所」をダブルクリックすることで入力します。
これで、顧客を選択すると自動的にその顧客の住所が参照出来るようになります。尚、一度リンクすれば、計算/参照型のフィールを追加することで、いくつでもリンク先のフィールドが参照出来ます。
以上はダイレクトリンクを使った参照例ですが、キーリンクの場合も同様です。実際の例としては着信履歴テーブルをご覧下さい。電話番号によるキーリンクで顧客テーブルとリンクし、顧客分類というフィールドで顧客の分類を参照しています。
リンクの応用
リンクは集計を目的とした機能ではありませんが、グループ化されることを利用してグループ毎の集計が出来ます。また、Excel の VLOOKUP 関数との関係についても説明します。
○○毎の集計(例:顧客毎の集計)
「○○毎」といった集計をしたい場合、○○に相当するテーブルがまず必要です。顧客毎なら顧客テーブル、商品毎なら商品テーブル、都道府県毎なら都道府県テーブルです。集計結果を表示するテーブルであり、集計対象をグループ化するためのテーブルとも言えます。
次に、集計データから○○毎のテーブルをリンクします。(集計データをそのテーブルの関連テーブルにします)
そして、○○毎のテーブルに計算/参照型のフィールドを追加して関連テーブルの内容を集計します。
例:顧客毎に注文総額
例えば、顧客毎に注文データの総額を集計したい場合、顧客テーブルと注文テーブルが必要です。そして、注文テーブルにダイレクトリンク型かキーリンク型のフィールドを追加して、顧客テーブルをリンクします。これで注文テーブルは顧客テーブルの関連テーブルになります。
そして、顧客テーブルに計算/参照型のフィールドを追加して注文金額の合計を計算して下さい。具体例はデモデータの注文管理を参照して下さい。
年月毎の集計(完全一致のキーリンクが出来ない場合)
以上のように「○○毎」の集計を行うには○○テーブルを用意して、リンクを使って集計対象をグループ化するわけですが、うまくリンク出来ない場合の対応方法について説明します。
例えば、予約テーブルに予約日時があり、このデータを使って年月毎に予約回数(予約レコードの数)を集計したいとします。年月毎の集計なので年月テーブルをまず作成します。そして、予約テーブルから年月テーブルをリンクするために予約テーブルにキーリンクのフィールドを追加しました。
ところが、予約テーブルの予約日時は「年月日と時刻」のデータ、年月テーブルの年月は「年月」のデータです。キーリンクでリンクできるのはセルの内容が完全一致するものだけです。つまり、キーリンクできません。
この場合、予約日時から年月データを計算式で自動入力することで解決できます。予約テーブルに自動入力を設定した短いテキスト型のフィールドを追加して下さい。
常に内容が更新されるように自動入力タイミングは全部オンにします。
尚、このフィールドは、計算/参照型には出来ません。計算/参照型のフィールドはキーリンクには使えないためです。
以上で年月テーブルの年月フィールドとキーリンクできるようになります。年月テーブルの年月フィールドには「YYYY年MM月」形式でデータを入力して下さい。
Excel の VLOOKUP を行うには
Excel の VLOOKUP 関数は検索と値の参照を一つの関数で行いますが、Fullfree ではリンクと計算式に役割が別れています。リンクでつなげて計算式で参照します。
例1:顧客テーブルから顧客分類を参照
Excel の VLOOKUP 関数に最も近いのはキーリンク+計算/参照型フィールドの組み合わせです。例として「顧客名簿」デモをご覧下さい。着信履歴テーブルから顧客テーブルを電話番号でキーリンクしています。そして、着信履歴テーブルの顧客分類フィールドにて、顧客テーブルの分類フィールドを計算/参照型で参照しています。
もし、着信履歴の電話番号が顧客テーブルに複数見つかると、見つかったすべてのレコードとリンクされます。つまり、着信履歴テーブルの顧客フィールドには複数の顧客名が表示されます。Excel の VLOOKUP 関数と違い、Fullfree では検索結果が複数の場合にも対応しています。
例2:商品テーブルから商品単価を参照
Excel の計算の長所でもあり短所でもあるのは常にリアルタイムに再計算されることです。社員テーブルから所属部署を参照するような場合は、情報が常に最新で長所となります。ところが、商品テーブルから商品単価を注文テーブルに参照する場合、商品単価を値下げしたら過去の注文金額が一斉に変更されることになります。
Fullfree では、Excel のようなリアルタイム計算を行うには計算/参照型のフィールド、計算結果を保存して勝手に再計算されないようにするには各データ型の自動入力(計算/参照)を使います。
後者の例として「注文管理」デモをご覧下さい。注文テーブルは商品テーブルをダイレクトリンクしています。そして、注文テーブルの単価フィールドでは自動入力を使って商品単価を参照しています。
「自己リンク」による段数無制限の階層構造
自己リンクすると同じ種類のデータにて、段数無制限の階層構造を表現することが出来ます。例えば家系図のデータはこのように管理出来ます。
カード形式で開くと子レコードが表示出来ます。子レコードを使った計算も可能です。
リンクの設定では「同じテーブルとのリンク(自己リンク)」を選択してください。
尚、テーブル定義では既存のテーブルとしかリンク出来ませんので、新規のテーブルで自己リンクを設定する場合は、一旦テーブル定義を保存してからリンクのフィールドを追加して下さい。また、計算式で子レコード(=関連レコード)を使った計算を行う場合も、一旦自己リンクを含むテーブル定義を保存してから計算/参照型のフィールドを追加して下さい。