みんなに優しく、解りやすくをモットーに開設しています。 以下のルールを守りみんなで助け合いましょう。
1.ファイルメーカーで解らない事があればここで質問して下さい。 何方でも、ご質問・ご回答お願いします。 （優しく回答しましょう）
You are not logged in.
Pages: 1
いつもお世話になっております。
日報明細ファイルと工程管理表ファイルがありまして、工程管理表にはプロジェクトIDや名称、担当者ID、開始予定日などを登録しております。
日報明細で担当者が該当プロジェクトの実績を入力すると、工程管理表にあるガントチャートに、日報を登録した日付(繰り返しフィールド使用)を黄色に塗り潰す、
という処理を行いたいのですが、
このガントチャートが1日ごとでなく、ひと月に上旬、中旬、下旬の3マスがあり、実績をあげた日付が該当月の1日～10日なら上旬のマスを黄色に塗り潰す、
11～20日なら中旬、21～月末日なら下旬、というふうに塗り潰しを行いたいのです。
(6ヶ月分表示させるので、繰り返しフィールドの要素数は18になります)
色々な方法を考えた結果、工程管理表を開いたタイミングでスクリプトトリガを仕掛け、
ExecuteSQLを使用して上旬、中旬、下旬ごとにBETWEENを使った日付範囲検索をかけ、
該当レコード件数が1件以上あればフラグフィールド(繰り返しフィールド)に1を立て、
条件付き書式でこのフラグフィールドに1があればそのマスを黄色に塗り潰す
・・・という事を目論んでいたのですが、ExecuteSQL関数の返り値が「?」になってしまいます。
記述の間違っている箇所を教えて頂けないでしょうか？
以下、スクリプトを記述します。
-------------------------------------
変数を設定 [$基準年月日; 値：Get(スクリプト引数)] // このスクリプト引数には、現在年月日のひと月前の1日が渡されます
変数を設定 [$日付列; 値：0]
Loop
変数を設定 [$日付列; 値：$日付列 + 1]
フィールド設定 [工程管理::上中下旬[$日付列]; "上旬"]
変数を設定 [$val; 値：ExecuteSQL(
"SELECT count(*)
FROM \"工程管理\"
WHERE
\"工程管理::プロジェクトID\" = \"日報明細::プロジェクトID\" and
\"工程管理::担当者ID = \"日報明細::社員番号\" and
\"日報明細::日付\" BETWEEN ? and ?" ; "" ; "" ;
Date ( Month ( 工程管理::実績_全体表示用[$日付列] ) ; Day ( 工程管理::実績_全体表示用[$日付列] ) - 9 ; Year ( 工程管理::実績_全体表示用[$日付列] ) );
工程管理::実績_全体表示用[$日付列] )
変数を設定 [$日付列; 値：$日付列 + 1]
フィールド設定 [工程管理::上中下旬[$日付列]; "中旬"]
...
以下、中旬、下旬も同じ処理
...
変数を設定 [$基準年月日; 値：Date(Month($基準年月日) + 1; 1; Year($基準年月日)]
Exit Loop If [$日付列 ≧ 18]
End Loop
-------------------------------------
途中に出てくる「実績_全体表示用」というフィールドは計算フィールドで、計算内容は
Case (
上中下旬[Get ( 計算式繰り返し位置番号 )] = "上旬" ; Date ( Month ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) ; 10 ; Year ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) ) ;
上中下旬[Get ( 計算式繰り返し位置番号 )] = "中旬" ; Date ( Month ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) ; 20 ; Year ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) ) ;
上中下旬[Get ( 計算式繰り返し位置番号 )] = "下旬" ; Date ( Month ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) + 1 ; 0 ; Year ( Extend ( 基準日 ) + Get ( 計算式繰り返し位置番号 ) * 10 - 1 ) ) )
という日付型の繰り返し数18のフィールドで、これが実質的にガントチャートの見栄えに使用しています。
自分自身、もうどうしていいか分からない状態でどんな情報を提供すればいいのかすら分からず、
こんなコネ繰りまくった方法じゃなくもっと簡単な実現方法があるかもしれませんが、
もしよろしければその手段も教えていただければ幸いです。
乱文乱筆申し訳ありませんが、よろしくお願いします。
【環境】
Windows11
FileMaker18
スクリプト11行目「\"工程管理::担当者ID」の末にエスケープ文字を入れるのを忘れていました。
・・・が、$valの結果は変わらず「?」が返ってきてしまいます。
フィールド指定にテーブル名を付けてはいけません。
\"工程管理::プロジェクトID\"
などは
\"プロジェクトID\"
複数テーブルがFROMにあってテーブル指定が必要な場合の区切り文字は、ダブルコロンでなくドットです。
\"工程管理\".\"プロジェクトID\"
himadanee様
> 複数テーブルがFROMにあってテーブル指定が必要な場合の区切り文字は、ダブルコロンでなくドットです。
ありがとうございます。以下のように修正したのですが、やはり実行結果は?を返してしまいます。
---------------------------------------------------
変数を設定 [$val; 値：ExecuteSQL(
"SELECT count(*)
FROM \"工程管理\"
INNER JOIN \"日報明細\" TO
\"工程管理\".\"プロジェクトID\" = \"日報明細\".\"プロジェクト管理ID\" and
\"工程管理\".\"担当者ID\" = \"日報明細\".\"社員番号\"
WHERE
\"日報明細\".\"日付\" BETWEEN ? and ?" ; "" ; "" ;
Date ( Month ( プロジェクト管理::チャート_実績_全体表示用[$日付列] ) ; Day ( プロジェクト管理::チャート_実績_全体表示用[$日付列] ) - 9 ; Year ( プロジェクト管理::チャート_実績_全体表示用[$日付列] ) ); プロジェクト管理::チャート_実績_全体表示用[$日付列] )
---------------------------------------------------
＞INNER JOIN \"日報明細\" TO
TOでなくONでは？
引数の1つ目は、9日引いてるだけですよね。
プロジェクト管理::チャート_実績_全体表示用[$日付列] - 9
でいいのでは。
himadanee様
すいません、ソースに「プロジェクト管理::チャート_実績_全体表示用」って書いてありましたが実際には「工程管理::実績_全体表示用」の間違いでした。
> TOでなくONでは？
> プロジェクト管理::チャート_実績_全体表示用[$日付列] - 9でいいのでは。
修正し、実行してみたのですが、処理結果$valは相変わらず?のままでした。。。
---------------------------------------------------
変数を設定 [$val; 値：ExecuteSQL(
"SELECT count(*)
FROM \"工程管理\"
INNER JOIN \"日報明細\" ON
\"工程管理\".\"プロジェクトID\" = \"日報明細\".\"プロジェクト管理ID\" and
\"工程管理\".\"担当者ID\" = \"日報明細\".\"社員番号\"
WHERE
\"日報明細\".\"日付\" BETWEEN ? and ?" ; "" ; "" ;
工程管理::実績_全体表示用[$日付列] - 9;
工程管理::実績_全体表示用[$日付列] )
---------------------------------------------------
データビューアでその計算式を実行するとエラーメッセージが出るので確認してみてください。
「BETWEENが機能しない」ということですが、WHEREをなくせばエラーにならないのですか？
SQL部分しか読んでませんでしたが、 工程管理::実績_全体表示用[$日付列] は日付型になってますか？
himadanee様
データビューワでWhere句なしで実行したところ、「An expression contains incompatible data types.」というエラーメッセージが返ってきました。
そして、工程管理の担当者IDは数値型で日報明細の社員番号はテキスト型(これは私が関わることになった以前からこうなっていました)だった事を思い出し、
今更日報明細のフィールドの型は変えられないので工程管理の担当者IDをテキスト型に変更しました。
そして再度データビューワでWhere句ありで実行したところ、10秒くらい処理中になった後、0を返しました。
(ちなみにWhere句なしで実行したところ、4～5秒経った後、815を返しました)
Where句には「工程管理::実績_全体表示用[$日付列]」と変数を使っているため、スクリプトステップにWhere句ありでデバッグ実行したところ、
ExecuteSQLの実行で何分待ってもステップの進まないフリーズ状態となってしまいました。
> 工程管理::実績_全体表示用[$日付列] は日付型になってますか？
はい、日付型です。
ちょっと頭を整理してきます。
件数しか必要ないならJOINをやめた方が速いかもしれません。
と思ってSQＬをコピペして直しかけてから気づきましたが、このＳＱＬでは工程管理テーブルが全く必要ないのでは？
工程管理に結びつかない日報明細があるってことですか？
SQLしか見てなかったので最初の投稿を読み直してみたら、JOINでなくWHEREに必要ですよね。
プロジェクトのガントチャートのはずなので担当者は不要な気がしますが...
ExecuteSQL(
"SELECT count(*)
FROM \"日報明細\"
WHERE
\"プロジェクト管理ID\"=?
AND
\"日報明細\".\"社員番号\"=?
AND
\"日報明細\".\"日付\" BETWEEN ? and ?" ; "" ; "" ;
工程管理::プロジェクトID;
工程管理::担当者ID;
工程管理::実績_全体表示用[$日付列] - 9;
工程管理::実績_全体表示用[$日付列] )
もう一度見直したんですが、「実績_全体表示用」というフィールドの計算式も、「繰り返しごとに10日足す」ならもっと簡単にできるはずと思ったら、
そもそも「繰り返し3ごとに1月増える」という計算になってないように見えますね。
うまくできてますか？
「21～月末日なら下旬」の処理が必要なので、SQLも
工程管理::実績_全体表示用[$日付列] - 9;
で検索条件を10日間に固定したらだめですよね。
繰り返しを19にして、次の繰り返しとの間を検索するのが簡単かな。「基準日」があるので上中下旬は計算に必要ないでしょう。
しかし「基準日」を設定している箇所が見当たりませんが、
変数を設定 [$基準年月日; 値：Get(スクリプト引数)] // このスクリプト引数には、現在年月日のひと月前の1日が渡されます
と同じでしょうか？
表示する6か月が常に上旬から始まるなら、「上中下旬」はラベルにしか使わないのでフィールドは必要ないですが
「実績_全体表示用」の計算式は、こんな感じかな。
Let ( n = Get (計算式繰り返し位置番号) - 1 ;
Date ( Month ( 基準日[1] ) + Div ( n ; 3 ) ; Mod ( n ; 3 ) * 10 + 1 ; Year ( 基準日[1] ) )
)
これの2つをSQLのBETWEENに使えばいいですが、BETWEENは遅い原因かもしれません。最近のバージョンではベンチマークしてませんが、前は（FM16頃？）不等号で検索した方が断然速かったです。
2フィールドでのJOINをやめればそれだけで速くなるかもしれませんが、データ量はどのぐらいですか？
himadanee様
レスが遅くなり申し訳ありません。
レス番号9でご記載頂いた構文で、無事件数を取得することができました！
> このＳＱＬでは工程管理テーブルが全く必要ないのでは？
確かにおっしゃる通りでした。
> 工程管理に結びつかない日報明細があるってことですか？
ありますが、それらはプロジェクトIDが割り振られていないので、WHERE文により対象から省けるので問題ないと認識しています。
> もう一度見直したんですが、「実績_全体表示用」というフィールドの計算式も、「繰り返しごとに10日足す」ならもっと簡単にできるはずと思ったら、
> そもそも「繰り返し3ごとに1月増える」という計算になってないように見えますね。
> うまくできてますか？
「実績_全体表示用」に関しては、現状、今の計算式でこちらの意図通りに動作していますが・・・
> 「21～月末日なら下旬」の処理が必要なので、SQLも
> 工程管理::実績_全体表示用[$日付列] - 9;
> で検索条件を10日間に固定したらだめですよね。
これは確かにその通りですね。
> 繰り返しを19にして、次の繰り返しとの間を検索するのが簡単かな。
ここの部分はすいません、よくわかりません・・・
> しかし「基準日」を設定している箇所が見当たりませんが、
最初に記述した構文の外にもう一つLoop処理があり、その外で基準日を設定しています、が、
設定しているのは$基準年月日と同じGet(スクリプト引数)だったりします。
> 表示する6か月が常に上旬から始まるなら、「上中下旬」はラベルにしか使わないのでフィールドは必要ないですが
ここはスピードupにつながりそうなので手を付けたいですが、今は一旦、今出来ている成果物で様子を見たいと思っています。
> データ量はどのぐらいですか？
現在は500件ほどですが、今後100,000件くらいまで増えることを想定しています。
> 繰り返しを19にして、次の繰り返しとの間を検索するのが簡単かな。
ここの部分はすいません、よくわかりません・・・
例えば10月下旬を検索するには
BETWEEN 10/21 AND 10/31
なので、開始の値が10/31ー９ではだめ、ということはわかったんですよね。
10/31から10/21を得るのは例外処理になってしまうので、（切り捨てとか数値演算を色々やってもできるでしょうが）
「実績_全体表示用」の値を1日、11日、21日にしておけば
BETWEEN 実績_全体表示用[1] AND 実績_全体表示用[2]-1
のように検索できて、条件が１８枠分必要なので、[1]～[19]になるということです。
まあその辺はやり方は色々あるでしょうから、お好みで。
この場合ＢＥＴＷＥＥＮを使わないで不等号にすれば、終了の方を＝なしにしてー１もいらなくなってわかりやすいです。
日付>=実績_全体表示用[1]
AND
日付＜実績_全体表示用[2]
himadanee様
またご返答遅くなり申し訳ございません。
下旬の検索ですが、BETWEENの開始日を
Date ( Month ( 工程管理::実績_全体表示用[$日付列] ) ; 21 ; Year ( 工程管理::実績_全体表示用[$日付列] ) );
という姑息な手で乗り切りました。
又、上中下旬はよくよく考えてみたらフィールドにする意味は全くありませんよね(笑)
ラベル化しました。
長きに渡りお付き合い頂き、誠にありがとうございました。
度々申し訳ありません。
利用者から動作が遅いと指摘を受け、BETWEENではなく、日付の不等号にしてみたところ、処理結果$valがまた?を返すようになってしまいました。
データビューワで見てもどこがおかしいのか分からず・・・今一度ご教授頂けないでしょうか？
ExecuteSQL (
"SELECT count(*)
FROM \"日報明細\"
WHERE
\"プロジェクト管理ID\" = ? AND
\"社員番号\" = ? AND
\"日付\" ≥ ? AND
\"日付\" ≤ ? " ; "" ; "" ;
工程管理::プロジェクトID;
工程管理::担当者ID;
工程管理::実績_全体表示用[$日付列] - 9;
工程管理::実績_全体表示用[$日付列] )
SQLなので演算記号はASCII文字なのでは？
≥
でなく
>=
など
himadanee様
不等号表記を「＜＝」にしたら無事計算結果を返すようになりました！ありがとうございます。
Pages: 1
[ Generated in 0.006 seconds, 9 queries executed - Memory usage: 615.33 KiB (Peak: 631.87 KiB) ]