cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Geert1
Frequent Visitor

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


@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
CarlosFigueira
Power Apps
Power Apps

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!

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
Frequent Visitor

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?


@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!

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (2,207)