PowerApps│テキスト混在主キーを自動連番で作成する方法

こんにちは、あんこ先生です。

アプリからSharePointリストにアイテムを登録する際、一意の主キーを含めると検索しやすくて便利ですよね。

単なる数字の羅列ではなく識別用のテキストを加えることで、さらに分かりやすくなります。

主キーは重複しないよう連番を付けますが、テキストが混在すると管理が難しくなります。

毎回手動で番号を調べて追加するのも面倒ですよね。

とはいえ適当に付番すると管理しにくくなるし、重複するリスクもあります。

そこで今回はテキスト混在主キーを自動連番で作成する方法を紹介します。

これで面倒な作業からは解放されますよ。

テキスト混在主キーを自動連番で作成する方法の事前準備

まずはじめに、主キーのルールを定義します。

テキスト混在のため、主キーのデータ型はテキストになります。

主キーは一般的に固定長とする運用がされています。

その理由は、テキスト型では桁数が異なると正しく並び替えができない可能性があるからです。

例えば、数値部分が「1, 2, 10, 20」と並ぶ場合、「1, 10, 2, 20」と表示されることがあります。

この問題を避けるために、桁数を揃えて「01, 02, 10, 20」といった形にしましょう。

数値部分の桁数の目安としては、1日当たりの件数×365×保管年数にもう一桁増やしておけば安心です。

これに識別子のテキスト部分を合わせて設定します。

今回は次のルールで作成された主キーを想定して紹介しますね。

• 主キーの桁数は8桁固定
• 識別子はテキストでANKの3桁
• 数値は5桁
• 他の識別子は存在しない
• 数値部分は連番

おおまかな流れ

使用するデータソース

自動連番主キーという名前のSharePointリストを使います。

テキスト型の列をひとつ用意し、 ANK01234 のアイテムを作成しておきます。

主キーなので次の設定も行っておきましょう。

最大文字数を設定しておくとそれ以上の値は保存できなくなるので便利です。

主キーを降順で並び替え、最大値を抜き取る

主キー降順で並び替えて最大値を抜き取るための手順を解説します。

まず、一番大きい値の主キーを特定する必要があります。

最大値を求めるためのMax関数がありますが、残念ながらテキスト型には対応していません。

また、委任やデータ転送数の制限があるため、直接特定するのは難しいです。

このような場合は、委任可能なSort関数を使って主キーを降順で並び替えます。

Sort(自動連番主キー,主キー,SortOrder.Descending)

これにより、最も大きい主キーはSharePointリストの先頭にくるため、データ転送数の影響を無視できます。

あとはFirst関数を使用して先頭のアイテムを抜き取ります。

First(Sort(自動連番主キー,主キー,SortOrder.Descending))

主キーの数値部分を切り出して+1する

先ほどの手順で、主キーの最大値が含まれるアイテムを抜き取りました。

主キーが格納されているのは列なので、次のコードで主キーを抜き出しましょう。

どんな値が格納されているのか可視化したい場合は、ラベルコントロールに書き出してみましょう。

First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー

これで主キーの抜き取りはバッチリです。

この値はこのあと複数回参照するため、With関数で 最大値 という名づけをしておきましょう。

With({最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    最大値
)

それでは主キーのテキスト部分を除外して、数値部分を切り出してみましょう。

主キーの構成は前方3桁がテキスト、後方5桁が数値です。

ひとまず前方のテキストを外して数値のみにしてみます。

テキストの外し方はいくつかありますが、シンプルなMid関数を使った方法を紹介しますね。

識別子の桁数が完全に固定されている場合はこれに限ります。

Mid関数で4文字目から開始して5文字分を抜き取ると数値部分のみになります。

その結果に+１するだけというお手軽さです。

With({最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    Mid(最大値,4,5)+1
)

これをラベルコントロールなどに書き出すと 1235 になります。

計算したことで 01234 の先頭にある 0 が消え、1234+1 が返ってきます。

これでは桁数がズレるのでよろしくないですね。

次のステップで是正しましょう。

テキスト部分と数値部分をつなげる

数値部分は作成できたため、あとは識別子をつなげて桁を揃えたら完成です。

識別子は直接入力してもよいですが、Mid関数で1文字目から開始の3文字分を抜き取ってみましょう。

また数値部分の桁揃えはText関数が使えます。

書式として 00000 を与えると5桁未満の場合に足らない桁分の 0 を追加してくれます。

123 なら 00123 といった具合です。

次のコードをラベルコントロールなどで書き出せば理解が捗りますよ。

With({最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    Text(Mid(最大値,4,5)+1,"00000")
)

このコードに手を加えて、Mid関数で抜き取った識別子も繋げてみます。

With({最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    Mid(最大値,1,3) & Text(Mid(最大値,4,5)+1,"00000")
)

これで自動連番になりましたね。

あとは適当なフォームコントロールやボタンコントロールなどでリストに書き込んでみましょう。

明日のための予備知識

応用　Substitute関数を使ってみる

With関数で識別子も名づけすれば、Substitute関数を使う方法も有効です。

Substitute関数は特定のテキストを指定したテキストに置き換えて返します。

名づけした識別子を主キーから取り除けば数値部分になるといった具合です。

With({識別子:"ANK",最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    Substitute(最大値,識別子,"")+1
)

こちらも計算することで数値部分の先頭 0 が消えてしまいます。

識別子とともに、Text関数で桁を合わせてあげましょう。

With({識別子:"ANK",最大値:First(Sort(自動連番主キー,主キー,SortOrder.Descending)).主キー},
    識別子 & Text(Substitute(最大値,識別子,"")+1,"00000")
)

異なる関数を使用してもおなじ結果になるのは面白いですね。

やらかし　複数の識別子が混在しているとうまくいかない

当たり前ですが、複数の識別子が混在していると狙ったものが先頭にこないことがあります。

そんなときはSort関数の前にFilter関数でひとつの識別子に絞り込みましょう。

StartsWith関数を組み合わせれば指定したテキストで始まるものを抽出できますよ。

First(Sort(
    Filter(自動連番主キー,StartsWith(主キー,"ANK")),主キー,
SortOrder.Descending)
).主キー

テキスト混在主キーを自動連番で作成する方法のまとめ

今回はテキスト混在主キーを自動連番で作成する方法を紹介しました。

主キーの構成ルールが明確で規則性がある前提ですが、自動連番もかんたんに行えますよね。

今回は主キーを想定して紹介しましたが、社員番号や固定資産番号でも活用できますね。

工夫してめんどくさい作業を自動化してみましょう！。

テキスト混在主キーを自動連番で作成する方法でした。