cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Geert1
Level: Powered On

Create an ID when clickin on a button(save)

Hello,

 

Iam really new with PowerApps, hopefully someone can help me with the follow issue.

 

When I click on a button in PowerApps there is generate a new row in my table in Excel (table name is [table1]). Now I want that each row get an own ID in column name [RN], like ''RN1712001''.

 

Each ID needs to start with ''RN'' then two numbers for the year and two numbers for the month like ''yymm''. and as last 3 digits numbers what per row is +1.

 

Example: (in November 2017)

RN1711001

RN1711002

Example: (in December 2017)

RN1712001

RN1712002

 

Thank you in advance,

1 ACCEPTED SOLUTION

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Create an ID when clickin on a button(save)


@Geert1 wrote:

Where should I etner this formula? I tried to enter the formulla in the button ''save'' and in the text field, withoud a result

 

Since you're using a form, I guess you want this new id to be used whenever you're adding a new item (in a button/icon that calls the NewForm function on its OnSelect property). In this case, you can set this formula on that same control, something along the line of:

Set(Prefix; "RN" & Text(Now(); "[$-en-US]yymm"));;
Set(
    LastIdForMonth;
    First(
        SortByColumns(
            Filter(Table; StartsWith(Id; Prefix));
            "Id";
            Descending)).Id);;
Set(
    NextId;
    If(
        IsBlank(LastIdForMonth);
        Prefix & "001";
        Prefix & Text(Value(Mid(LastIdForMonth; Len(Prefix) + 1) + 1); "000")));;
NewForm(Form1)

After this, you can change the card for the id, to update the Default property (you may need to unlock the card in the advanced pane to change this formula):

Coalesce(ThisItem.Id; NextId)

The Coalesce function will return the first non-blank parameter that is passed to it. In the new form mode, all items are blank, so the text input for the id will display the calculated value for the next id by default.

 

Hope this helps!

View solution in original post

4 REPLIES 4
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Create an ID when clickin on a button(save)

You can use the formula below to accomplish that on the OnSelect property of a button:

Set(Prefix, "RN" & Text(Now(), "yymm"));
Set(
    LastIdForMonth,
    First(
        SortByColumns(
            Filter(Table, StartsWith(Id, Prefix)),
            "Id",
            Descending)).Id);
Set(
    NextId,
    If(
        IsBlank(LastIdForMonth),
        Prefix & "001",
        Prefix & Text(Value(Mid(LastIdForMonth, Len(Prefix) + 1) + 1), "000")))

Let's go through that. The first line stores the prefix for the current month in a variable (Prefix), so that it can be used easily later on.

 

The second expression searches for all items whose Id have the prefix for the current month, and takes the last one (first one in descending order).

 

The third expression will set the NextId variable; if there were no id for the current month (the previous expression evaluated to a blank value), then it uses 001 as the prefix. Otherwise, we extract the counter (via the Mid function), take its Value (to convert it to a number), add 1, and convert it back to Text with the three digit format.

 

Hope this helps!

Geert1
Level: Powered On

Re: Create an ID when clickin on a button(save)

Thank you CarlosFigueira,

Where should I etner this formula? I tried to enter the formulla in the button ''save'' and in the text field, withoud a result

 

Maybe good to know, for the button ''Save'' I have already a formula:

''SubmitForm(EditForm1) And Navigate(BrowseScreen1; Fade)''.

 

And in the text field I tried the formula ''(CountRows(table1)+1)''

It works, but if I change some details in the other text fields this number is increased its value, but it should be the same.

 

The form looks like this:

 

+--------------------------------+

| |X| Damageform  |Save| |

|____________________|

|                                        |

RN                                 |

|  | RN1812001              |  |

|                                        |

+--------------------------------+

Geert1
Level: Powered On

Re: Create an ID when clickin on a button(save)

I tried something with the follow formula:

 

Concatenate("RN";Text(Today();"[$-nl-NL]yymm");Text(CountRows(Tabel2)+1;"[$-nl-NL]000"))

 

Now I get when I create a new form by each a number like RN1712001, RN1712003, etc...

But the issue is now, when I try to change the form with number ''RN1712001'' the field is changing to RN1712002 becouse the formula checks the numbers of row. But I want this value ''RN1712001'' as a fixed value.

does some know how to set a new (calculated value) as a fixed value?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Create an ID when clickin on a button(save)


@Geert1 wrote:

Where should I etner this formula? I tried to enter the formulla in the button ''save'' and in the text field, withoud a result

 

Since you're using a form, I guess you want this new id to be used whenever you're adding a new item (in a button/icon that calls the NewForm function on its OnSelect property). In this case, you can set this formula on that same control, something along the line of:

Set(Prefix; "RN" & Text(Now(); "[$-en-US]yymm"));;
Set(
    LastIdForMonth;
    First(
        SortByColumns(
            Filter(Table; StartsWith(Id; Prefix));
            "Id";
            Descending)).Id);;
Set(
    NextId;
    If(
        IsBlank(LastIdForMonth);
        Prefix & "001";
        Prefix & Text(Value(Mid(LastIdForMonth; Len(Prefix) + 1) + 1); "000")));;
NewForm(Form1)

After this, you can change the card for the id, to update the Default property (you may need to unlock the card in the advanced pane to change this formula):

Coalesce(ThisItem.Id; NextId)

The Coalesce function will return the first non-blank parameter that is passed to it. In the new form mode, all items are blank, so the text input for the id will display the calculated value for the next id by default.

 

Hope this helps!

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 183 members 6,083 guests
Please welcome our newest community members: