同僚から棚卸仕訳帳のEdit in Excelが上手く動かないという相談を受けました。調査の過程で品目仕訳帳や一般仕訳帳などを調べたところ、仕訳帳のEdit in Excelについていくつか気を付ける点があることが分かりましたので解説します。棚卸仕訳帳は問題があるようなので次回説明することとし、今回は品目仕訳帳を例にとり、正しい挙動と注意する点を説明します。
目次
1. 前提
2. 品目仕訳帳をEdit in Excelで編集する
3. バッチを増やす
4. バッチ名のフィルタを解除する
5. バッチ名でフィルタする
6. Edit in Excelでフィルターを使う際の注意事項
7. 仕訳帳バッチテンプレートのフィルタ
8. 仕訳帳のEdit in Excelを安全に使うために
9. まとめ
1. 前提
バージョンによって挙動が異なる（挙動が今後改善される可能性がある）ため、バージョン情報を記載しておきます。また、検証に使用した環境はUSリージョンに作成したフリートライアルテナントで、サンプルデータ入りのカンパニー（いわゆるCronusデータ）を使用します。BCフリートライアル環境の作成方法についてこちらの記事やこちらの記事を見てください。
2. 品目仕訳帳をEdit in Excelで編集する
まずは普通に品目仕訳帳をEdit in Excelで編集してみます。知っている人は読み飛ばしてください。
品目仕訳帳を開きます。仕訳帳ラインに明細をいくつか入力しておきましょう。
標準のサンプルデータカンパニーでは”Default”バッチのみ用意されています。
Edit in Excelボタンをクリックすると、Excelがダウンロードされます。
Excelを開きます。”Enable Editing”をクリックします。
Excelが開きました。説明しやすくするため、いくつかの列幅を調整しましたが気にしないでください。仕訳帳画面のデータが表示されています。
数量を編集してPublishボタンをクリックします。（日本語だと「公開」ボタンです。）
仕訳帳画面に戻りF5ボタンで画面をリフレッシュするとExcel上で入力した数量が反映されています。
Edit in Excelの基本的な使い方の説明は以上です。ここまでは一般的なリストページ（得意先一覧、品目一覧、決済条件一覧など）と全く同じです。
3. バッチを増やす
仕訳帳画面と一般的なリストページの最大の違いは”バッチ”です。仕訳帳画面でのEdit in Excelの挙動を理解するため、バッチを追加します。”MyBatch”というバッチを追加して説明します。
追加した”MyBatch”で仕訳帳のラインを1行入力し、Edit in Excelボタンをクリックしました。先ほどとは別のファイルがダウンロードされます。
新しいExcelファイルを開きます。”MyBatch”のデータのみが含まれています。
試しに数量を変更してPublishしてみましょう。
問題なくBCに反映されます。
4. バッチ名のフィルタを解除する
バッチ名が”Default”の画面からダウンロードしたExcelでは”Default”バッチのデータが取得/更新され、バッチ名が”MyBatch”の画面からダウンロードしたExcelでは”MyBatch”のデータのみが取得/更新されます。これがどのように実現されているかを解析し、バッチ名のフィルタを解除にトライします。
Excelの右端から2番目の列に”Journal_Batch_Name”という列が存在します。ここにバッチ名が記述されています。”MyBatch”画面からダウンロードしたExcelには”MyBatch”でフィルタされています。Excelのフィルタを外すと分かりますが、Excelでフィルタされているのではなく、フィルタ済のデータがExcelに格納されています。この制御がどこで実行されているか確認します。まずは”Design”ボタンをクリックします。日本語だと”デザイン”ボタンです。
”Item_Journal”の行の編集アイコンをクリックします。
ここにはデータソースと取得可能な列、表示する列が定義されているだけのようです。フィルタ条件は記述されていません。戻るボタンで戻ります。
次にフィルターボタンをクリックします。ボタンの名称から推測するに、ここでフィルターを設定していることが期待できます。
フィルター項目と値が設定されていると期待していたのですが、、少し違いました。
”The filter currently applied to the data source is not supported by this filter dialog clear the current filter to modify”
と表示されています。フィルターは設定されているものの、別の場所で設定されているようです。”+ Add a filter field”ボタンは非活性になっており、この画面でフィルター機能を使うには最初に別の場所で設定されているフィルターをクリアする必要があるようです。”x Clear filter”ボタンをクリックします。
”+ Add a filter field”ボタン がクリックできるようになりました。しかし、いまは設定せず、まずはこの状態でフィルターが解除されたかどうかを確認します。”Done”ボタンをクリックします。
”Refresh”ボタンをクリックします。
するとバッチ名”Default”と”MyBatch”の両方のデータが表示されました。フィルター解除が成功しました。
両方のバッチのデータを更新できるでしょうか？
試しに数量を変更して数量を変更して”Publish”ボタンをクリックします。
”Default”バッチのデータは更新されました。
”MyBatch”バッチのデータも更新されました。
つまり、BCの仕訳帳ページではで1つのバッチのみ編集可能ですが、Edit in Excelではフィルターを解除することで複数のバッチのデータを照会/更新可能という事が分かりました。
5. バッチ名でフィルタする
バッチ名のフィルタ解除に成功しましたが、フィルター無しだと実際の運用で使いにくいくのも事実です。フィルター機能で適切にバッチ名のフィルタを掛けられることをお見せします。
フィルタ設定画面で”+ Add filter field”ボタンをクリックします。
このようにフィルターを掛ける対象の項目と値、演算子（=とか<>とか）を指定できます。
以下の2つのフィルターを設定します。
”Journal_Template_Name” = ”ITEM”
”Jounal_Batch_Name” = ”Default”
設定完了後、”Done”をクリックします。
フィルタの設定は完了しましたが、表示されるデータにはまだ反映されていません。”Refresh”をクリックします。
バッチ名が”Default”のデータのみ表示されるようになりました。
数量を変更して反映されるか試してみましょう。”Quantity”列の数量を変更し、”Publish”ボタンをクリックします。
品目仕訳帳の画面に戻ってF5ボタンをクリックしてリフレッシュすると数量が更新されていることが分かります。
同じExcelファイルでバッチ名のフィルタを”MyBatch”に変更してみます。
先ほどと同様に数量を変更して”Publish”ボタンをクリックします。
品目仕訳帳画面で”MyBatch”を確認すると数量変更が反映されました。
つまり、Edit in Excelのフィルタ機能を使えば、特定の仕訳帳バッチのデータに絞って値の照会/更新が可能です。BCの仕訳帳画面でEdit in Excelボタンを押したときにはこのフィルタ機能とは別の場所にフィルターが設定されているようですが、Edit in Excelのフィルタ機能を使用することで同等の事ができるということです。
6. Edit in Excelでフィルターを使う際の注意事項
フィルタ機能を使う場合に一つ注意すべきことがあります。フィルタを正しく指定していない場合に誤ってデータを削除する可能性があるということです。具体例で説明しましょう。
バッチ名のフィルタ条件を削除した状態にします。Excelにはすべてのバッチのデータが表示されています。
この状態で”MyBatch”のデータを行削除します。
そして”Publish”ボタンをクリックします。
すると、ワーニングが表示されます。”1行削除する”と言っています。”Yes”をクリックしてみましょう。
BCの品目仕訳帳の画面でMyBatchのデータを表示します。すると”MyBach”のデータが削除されたことが分かります。
当然と言えば当然の挙動です。今の説明であれば特に問題を感じないと思いますが、バッチ名のフィルターが解除されている状態にもかかわらず、フィルターが設定されていると誤解してEdit in Excelからデータを更新した場合、変更する意図のないバッチのデータを誤って削除する可能性があることに注意が必要です。
7. 仕訳帳バッチテンプレートのフィルタ
先ほど、フィルタを掛ける際に特に理由を説明せず”Journal_Template_Name = ITEM”というフィルタを掛けました。この理由について説明します。
フィルタ設定で”Journal_Template_Batch”のフィルタを削除します。
挙動を理解するには”Physical Inventory Journal”(棚卸仕訳帳)を開いてデータを入力する必要があります。
”Prepare”タブから”Calculate Inventory”ボタンをクリックします。
”Document No.”に何らか適当な値を指定します。”Posting Date”は初期値のままで大丈夫でしょう。そして”OK”をクリックします。
すると明細データが作成されました。これは棚卸仕訳帳の機能で、品目別場所別の在庫が計算されて明細データが初期提案されます。サンプルデータ会社だと以下のようになるはずです。そしてここでバッチ名は”Default”です。
準備は完了です。Excelに戻り、フィルタ条件で”Journal_Batch＿Name = Default”を設定します。なお、”Journal_Template_Name”のフィルタ条件は削除されていることに注意してください。”Done”をクリックしてフィルタ設定を完了します。
”Refresh”ボタンをクリックします。
するとデータが20件近く追加されました。AC列の”Journal_Tempalate_Name”と”Journal_Batch_Name”を見ると明白ですが、これは棚卸仕訳帳のデータです。
棚卸仕訳帳のデータも更新できるのでしょうか？
全ての行の数量を変更して”Publish”ボタンをクリックします。
エラーになりました。。エラーメッセージを確認するため、”Open Message Center”をクリックします。
メッセージには”棚卸仕訳帳フラグがYesだと品目仕訳帳でデータを更新できない”と書かれています。ロジックは確認していませんが、これはおそらく品目仕訳帳画面のロジックなのだと思います。Edit in Excelでは品目仕訳帳のページをODataで公開してデータを入出力しているからです。
棚卸仕訳帳のデータが更新できないのであれば、Edit in Excelのフィルタ機能で”Journal_Tempalte_Name”を”ITEM”に限定しなくても大丈夫でしょうか？ 答えはNoです。それはデータの削除が可能だからです。試しに棚卸仕訳帳のデータをExcel上で削除して”Publish”ボタンをクリックしてみてください。
16行のデータが削除される、と警告が表示されました。”Yes”をクリックします。
データ更新が成功した、とメッセージが表示されます。
BCの棚卸仕訳帳画面に戻って”Default”バッチのデータを確認すると削除されたことが分かります。
ということで、仕訳帳データをEdit in Excelで編集するにはフィルタを適切に設定する必要があります。
8. 仕訳帳のEdit in Excelを安全に使うために
”フィルタを適切に設定する必要がある”と書きましたが、必要十分な設定は何でしょうか？
適切に設定するには仕訳帳テーブルのキーを正しく理解れば良いです。
棚卸仕訳帳の画面でCtrl+Alt+F1をクリックして”Page Inspection”情報を表示します。棚卸仕訳帳で照会/更新しているテーブルは”Item Journal Line(Table ID:83)”です。
同様に品目仕訳帳の画面で”Page Inspection”の情報を表示します。すると同じテーブルであることが分かります。ここで”PK”でキーワード検索し、テーブルのキーを調べます。すると以下の3項目だと分かります
Journal Template Name
Journal Batch Name
Line No.
Line No.は行追加する毎に追加採番されますので、フィルタを掛けるべきキーは” Journal Template Name ”と” Journal Batch Name ”であることが分かります。
ということで、品目仕訳帳のEdit in Excelを安全に使用するには以下のフィルタを設定すれば良いと言えます。
9. まとめ
今回は品目仕訳帳を例に説明しましたが、私が確認した限り一般仕訳帳も同様の挙動でした。他の仕訳帳についてもおそらく同様だろうと思います。仕訳帳画面でEdit in Excel ボタンを押してダウンロードされたExcelに設定されているフィルタは設定箇所を確認できませんでしたが、今回説明したフィルタ機能と同じだと考えます。
次回は棚卸仕訳帳で起きている奇妙な挙動について説明します。