はじめに
レイアウト上に時刻型フィールドを置く際、データの外観書式を統一するために、データの書式設定で「時分」や「時分秒」などの書式を設定することはよくあると思います。
ただし、書式設定でデータ表示形式の指定を行うと、ユーザがデータ入力した際に不完全な時刻型の値でも、見かけ上正しい時刻型のデータに見えるということが発生しますので、取扱いには注意が必要です。
言いたいこと
- FileMaker ではフィールドの型定義に基づいて値の推測が行われる。
- このため、ユーザが不完全な値を入力したとしても、レイアウトの書式設定で上書きされていると気づきにくい。
- そういうデータをエクスポートすると形式が異なる値として出力されて泣くことになる。
- エクスポートのオプションを設定したとしても、対策としては完全ではない。
- データ入力時に値形式を正規化しとこう
「不完全な時刻型の値」とは
不完全な時刻型の値の一例です。
２つのレコードの「TIME」フィールドには、「時分」タイプの書式設定がされています。
ID[1]、[2]のどちらのレコードにも"7:00"という値が入力されているように見えますが、ID [2]のTIMEフィールドにフォーカスを置くと
"7"と入力されていることがわかります。
FileMaker では、時刻型フィールドに入力された、このようなコロン区切りで明確に[h:mm]と指定していないデータに対しては、推測した値を時刻として扱います。この例で言えば、"7"->"7:00"として推測した扱いをしています。
この推測は計算上でも同様に行われます。
上の図では、RESULTという計算フィールドを配置しました。計算式は
TIME + time( 6 ; 0 ; 0 )
としており、どちらも7時間＋6時間＝13時間という計算結果を得ています。
このように[h:mm]形式ではない、不完全な時刻型の値では推測に基づいた表示／計算が行われるため、FileMakerの上での実用は問題なく意図した結果を得ることが出来ます。
問題が発生する場面
ならば「[h:mm]形式でないデータが入力されたとしても困ることは無いのではないか」と思ってしまうところですが、「入力されたデータを外部のソフトで利用する」場合には問題が発生することが考えられます。
代表的な例では「レコードのエクスポート」です。エクスポートのデフォルト設定では、不完全な時刻型の値は入力された値そのままで出力されます。
このレコード２件をExcel形式でエクスポートした場合、
| ID | TIME | 出力結果 |
|---|---|---|
| 1 | 7:00 | 時刻 |
| 2 | 7 | 数値 |
というように、FileMaker 上では同一の[7:00]のはずだった値が、Excelでは時刻と数値として別の値として扱われます。この出力結果を基に、時刻の計算や集計などを行おうとした場合、意図した結果は得られません。下図はエクスポートした値をSUM関数で合計したイメージですが、当然ながら数値として扱われた値を合計しても、本来得たかった[14:00]という結果にはなりません。
数件程度のエクスポート結果であれば、目視確認するなどでこのような「不完全な時刻型の値」の混入を発見することが出来るかもしれませんが、エクスポート件数が数万レコードとなれば、確認することは簡単ではありません。
解決策その１（エクスポートのオプション設定）
このような事例が起こりうる事を知ってか知らずか、FileMaker では、エクスポート時にレコードの値に対して、レイアウトで指定した書式設定を上書きするオプションがあります。
「エクスポートされたデータに現在のレイアウトのデータ書式を適用する」というオプションを適用すれば、レイアウト上で表示されている書式の値がそのまま出力されます。
このオプションを設定しておけば、エクスポートしたデータの値の書式が統一されるのですが、次のような問題があります
- エクスポート速度が遅い
- オプションを設定しない（保存された値をそのまま）出力よりも明らかに遅い
- 毎回オプションを設定する手間
- スクリプト化しておけば忘れることはないでしょうが、出力するデータごとにスクリプトを作成する手間が残る
- 書式がレイアウトに依存するため、書式を設定していないレイアウトでは適用不可
- スクリプトの仕様（エクスポート前にレイアウト移動をしていないなど）で、意図しない書式設定で出力される可能性がある
- 「フィールドのエクスポート」以外の出力ではレイアウトの書式設定を適用できない
- 具体的に言うと「全レコードコピー」と「フィールド内容のエクスポート」スクリプトステップでは、入力された値で出力される。
特に最後の「全レコードコピー」と「フィールド内容のエクスポート」スクリプトステップで書式設定を適用できないのは致命的ですので、この解決策はあくまで応急的なものとして使用するのが賢明かと思います。
解決策その２（入力時に正規化する）
やはり根本的解決策としては、データ入力時に自動的に正規化を行う仕様とするのが正しいでしょう。
FileMaker では、フィールドの自動入力でSelf関数を使えば、ユーザが入力した値に対して計算結果で上書きが出来ます。
時刻型フィールドの計算値に対して、GetAsTime( Self )と計算式を入力すれば、入力された値を時刻型に変換して上書きします（このとき、「フィールドに既存の値が存在する場合は置き換えない」オプションのチェックを外しておかなければなりません。）。変換結果は、前述の「推測」された値と同値になりますので、ユーザーエクスペリエンスが変わることはなく、統一されたデータ型によるレコードが作成できます。
既存値に対する正しいフォーマットの適用
「フィールド内容の全置換」で修正できます。ただし、Self関数は使用できませんので、GetAsTime( {フィールド名} )という形式で計算式を指定することになります。
全置換を実行する際はレコード件数によって実行速度が変わるため、検索によって対象を絞り込みたいところですが、FileMaker では、検索時にも「推測」が働くため、この２つのレコードを検索で区別できません。
従って全レコードを対象に全置換を実行しなければなりません。レコード件数とデータベース構成（全置換の対象レコードの値を基に計算・集計を行う場合など）では飛躍的に処理速度が遅くなりますので、出来る限り早めに入力時の正規化を行うのをおすすめします。
この記事を書いた理由
１万件程度のレコードをExcelで書き出した際に計算値がExcelとFileMakerで違ったので、おかしいなと思ったらこういうことでした。ユーザが皆正しい入力をするとは限らない、ということを知りました。