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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,132)