PowerApps と SQL Database を使用したテーブルの操作の基本 at SE の雑記

次の記事等で触れられていますが、Power Apps は SQL Server / SQL Database のコネクターを持っており、データベースと連携することができるようになっています。

単一テーブルではありますが、PowerApps から、SQL Database のテーブルを CRUD する方法をまとめておきたいと思います。

Contents

1 使用するテーブルの構成
2 接続の追加
3 アプリケーションの作成を行う際に意識しておくことがよいこと
4 データの追加
5 データの表示
6 データの削除
7 データの編集

使用するテーブルの構成

今回は、SQL Database にテーブルを作成していますが、構成は次のようなものとなっています。

DROP TABLE IF EXISTS T1;
CREATE TABLE T1
(
	id int IDENTITY PRIMARY KEY,
	Value nvarchar(255),
	CreateDate datetime2 DEFAULT(GETDATE())
)

主キーが設定されていないと、「編集フォーム」を作成した際のフィールドの設定が動的に行われないようなので、主キー付きのテーブルとしています。
本投稿では、このテーブルに対して、データの追加 / 取得 / 更新 / 削除を実施していきます。

接続の追加

SQL Database への接続については、PowerApps から SQL Server に接続するの内容と同じです。
オンプレミスデータゲートウェイ経由で、インターネット経由でアクセスができない、SQL Server にも接続が可能ですが最初は、SQL Database で試すのがよいかと。
SQL Database については、SQL Server のコネクターを使用して「直接接続」の環境として接続をすることができるようになっています。

アプリケーションの作成を行う際に意識しておくことがよいこと

PowerApps の作法になると思うのですが、最初に触る場合、次の 2 点を意識しておくとよい気がしています。

構造化 / 階層化されたコントロール
コントロールの設定の変更は、関数 / 変数を用いて実施する
必要に応じてコントロールのロックを解除して詳細に設定

構造化 / 階層化されたコントロール
PowerApps でアプリケーションを作成する場合、「ギャラリー」「フォーム」あたりを頻繁に使うことになるのではないでしょうか。
これらのコントロールは、ギャラリーやフォームという構造の中に、「テキストボックス」「ラベル」「図形」というようなコントロールが含まれ、グルーピング / オブジェクトとして構造化 / 階層化されているということを覚えておくとよいのではないでしょうか。
データを可視化する際、ギャラリーを使用すると、簡単に構造化されたデータの表現をすることができます。

コントロールを追加すると、ツリービューには次のようなコントロールとして追加されます。
Gallery2 というコントロールの中は、図形 / アイコン / テキストといった複数のコントロールで構成されていることが確認できますね。

デフォルトで足りていない項目があるのであれば、この階層化されたコントロールの中に追加することで、それが一つの行として構成することができるようになります。
初期状態では「編集」を行うようなアイコンは存在していません。

しかし「アイコン」から「編集」のアイコンを追加して、それが「Gallery2」というコントロールの中に含まれている階層となっていれば、それは各行に表示が行われることになります。

この時のコントロールのツリービューが以下の画像になるのですが、「Gallery2」の下に「icon3」(編集アイコン) が追加された階層となっていますね。

アイコンを追加した際に、スクリーンの直下に、追加したアイコンが階層化されている場合は、それは、ギャラリーの各行とは関連付けされていないことになりますので、「Ctrl+x」で切り取って、追加したいギャラリー内のコントロールをどれか選択した状態で「Ctrl+c」で貼り付け、追加したコントロールがどの階層内に関連づいているかを確認すると良いのではないでしょうか。

コントロールの設定の変更は、関数 / 変数を用いて実施する
これもお作法だとは思うのですが、「ボタンをクリックした際にラベルに表示されている内容を変更したい」という動作を考えてみます。
ボタンをクリックしたときに実施される内容については、該当のボタンの詳細設定からアクションの「OnSelect」の内容から設定することができるようになっています。

デフォルトは「false」となっていて、ボタンをクリックしても何も実施されません。

それでは、「label1 の表示内容を変更する」という場合、「label1.Text = "hogehoge"」と書けばよいかというとそうではありません。
アクションに次のように設定して、ボタンをクリックしてみます。

(F5 でアプリのプレビューを表示しなくても Alt を押しながら該当のコントロールをクリックすることで、OnSelect を実行させることができます)

このアクションでは何も行われず、ラベルのテキストは初期状態のままです。
ボタンクリックと連動して、表示内容を変えるためにはどうすればいいかというと、

ラベルのテキストは変数の内容を表示するようにしておく
ボタンをクリックした際には、変数の内容を書き換えるようにする

というのがお作法となるようですね。
PowerApps の変数にもスコープがあるのですが、今回は同一のスクリーン内で使用できるコンテキストとして設定をするため、ボタンのアクションには「UpdateContext({Val:"hogehoge"})」を設定しておきます。

次にラベルの Text には、先ほどのコンテキストの変数名を設定しておきます。

これで、ボタンクリック時にラベルのテキストを変更することができるようになります。

コントロールの状態を変化させる関数などもありますので、コントロールの操作については、「関数」「変数」を介して実施することを意識しておくとよいのかなと思いました。

必要に応じてコントロールのロックを解除して詳細に設定
コントロールの設定を変更しようとした際に「鍵アイコン」が表示されていて値を変更できない場合は、「詳細設定」の「プロパティを変更するためにロックを解除します」をクリックすることで、解除可能です。

設定を変更しながら動作確認したい場合には、ロック解除を覚えておくとよいのかと。

それでは、データベースの操作をするアプリケーションを作っていきます。
スクリーンを広々と使うため、「空のアプリ」を「タブレットレイアウト」で作成します。

データの追加

データが何も入っていない状態ですと、一覧表示の確認もできませんので、最初はデータ追加の機能を作ってみたいと思います。
「挿入」→「フォーム」から「編集」をクリックして、データ登録用のフォームの追加を行います。

データソースについては、SQL Database の T1 (冒頭のクエリで作成したテーブル) に接続を行います。

今回はテーブルの「Value」という項目のみ、既定値が設定されていない列となっていますので、この項目のみをフィールドに追加しておきます。

項目の追加が終わったら、追加したフォームの「既定モード」を「新規」に設定しておきます。

(フォームのサイズは適宜変更)

これでデータ入力のフォームが完成しましたので、次は登録用のボタンを「コントロール」→「ボタン」から追加します。

ボタンのアクションの設定ですがいくつか方法があるのかと。
ひとつは、フォームの内容を単純にサブミットする方法です。

ボタンの OnSelect に「SubmitForm(Form1)」を設定しておきます。

(追加したフォームがForm1 という名称でない場合は、適宜変更してください)

アクションの入力はインテリセンスが有効であり、コントロール名を入力した場合は、どのコントロールがターゲットになっているかは、スクリーンのコントロールが強調されますので、入力内容とスクリーンを見比べるとどこを走査しているかがわかりやすいかと。
F5 を押してプレビューにし、テキストボックスに値を入力して、ボタンをクリックするとデータベースにデータが登録されます。

もう一つの方法が「Patch 関数」を使用する方法です。

Patch(
    '[dbo].[T1]',
    Defaults('[dbo].[T1]'),
    {
        Value:DataCardValue1.Text
    }
)

ボタンのアクションに次のような設定を行います。
この場合、テーブル登録時に任意のコントロールとのマッピングができるようになりますので、複雑なことができるようになりますが、どちらで書いた方がよいかは、PowerApps の習熟度によるのかと。

(Patch で登録した場合、Submit した後に、FormReset しなくてもデータの連続登録ができたりと、細かな違いもありますが)
単一フォームの内容を単純にサブミットするだけであれば「SubmitForm」で対応できるかと。
これで、データを登録することができましたので、次は、一覧かしてみたいと思います。

データの表示

データの表示については、ギャラリーのコントロールを使ってみましょう。

レイアウトは、「縦」にして、データソースに SQL Database の T1 を選択した、次のようなギャラリーを追加しておきます。

タイトルには「id」、サブタイトルには「CreateDate」を設定して、次のような表示ができるようにしておきます。

これで、一覧表示ができましたね。

次は削除機能を実装してみます。

データの削除

今回は、一覧から削除を実行したいので、一覧に削除ボタンをつけてみます。
「アイコン」から「ゴミ箱」を追加し、

一覧表示で使用しているギャラリーのコントロールの階層の配下になるようにアイコンを設置します。

ギャラリーの配下にきちんと配置できれば、各行に自動的にアイコンが表示されるようになります。

これで、データを削除する際のアクションに使えるコントロールができましたので、追加したアイコンのアクションの「OnSelect」にデータの削除を実行するための、記述を行います。
今回は次のような記述をします。

(今回、追加したギャラリーの名称が「Gallery3」となっている前提での記載となります。ギャラリーの名称がそれ以外の場合は、適宜書き換えてください)

Remove(
    '[dbo].[T1]',
    Filter(
        '[dbo].[T1]',
        id = Gallery3.Selected.id
    )
)

記載している内容は、次のようになります。

SQL Database のテーブルのデータソースを削除対象とする
削除対象のデータは、テーブルのデータソースの id が、ギャラリーで選択したアイテム (行) の id と同一

これで、ゴミ箱アイコンをクリックすることで、該当の行のデータが削除されます。

最後に、編集の機能を追加してみます。

データの編集

本来は新規登録と編集のスクリーンを別にするというような考慮が必要となりますが、今回は一つのスクリーンでまとめています。
「フォーム」→「編集」でテーブルをデータソースとしたフォームを追加し、今回は「既定モード」を「編集」として作成しています。

次のようなスクリーン構成とし、ひとまず、一覧の右側に値を変更するためのフォームを配置しています。

ここから「>」をクリックした際に、編集用のフォームに値を表示し、修正できるようにしてみます。
最初に編集用のフォームの「Item」に次のような記述を行います。

(Gallery3 については、自分の環境に適宜置き換えて下さい)

First(
    Filter(
        '[dbo].[T1]',
        id = Gallery3.Selected.id
    )
)

フォームの Item では、どのアイテムの情報を表示するかを指定できるのですが、そこに、ギャラリーでクリックした行の id の内容というような形で設定を行っています。
これで、一覧の「>」をクリックした際に、値が編集用フォームに表示されるようになります。
編集用フォームを確定するボタンには、「SubmitForm(Form2)」をボタンの「OnSelect」に設定します。

(編集用フォームの名称が Form2 でない場合は適宜変更して下さい)

Patch 関数で設定する場合は、次のような記述でしょうか。

(「Gallery3」と「DataCardValue2」の個所については、適宜変更してください)

Patch(
    '[dbo].[T1]',
    First(
        Filter('[dbo].[T1]',id = Gallery3.Selected.id)
    ),
    {
        Value:DataCardValue2.Text
    }
)

これで、単一テーブルではありますが、作成 / 一覧表示 / 削除 / 変更までの機能を実装することができました。
複数の手ぶる操作や、各タイミングで実行されているクエリの把握などは、これから調べる感じではありますが、PowerApps を使用したテーブル操作の基本形については、このような作業になるのではないでしょうか。

月	火	水	木	金	土	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

SE の雑記