cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lefty
Post Prodigy
Post Prodigy

Increment reference number

Hi

I need help with being able to tell PowerApps to create and increment my reference number, this is a canvas app form and SharePoint Online is my data source

 

User1 will submit a form, but they will select from 1 of 3 screens only. 

If User1 selects Screen1 I'd like to have my reference number display something like this

 

REW-B-00001

 

If User1 completes Screen2 it would be REW-C-00001

 

If the reference number is already REW-C-00001, Then ideally the next number depending on selection could be REW-B-00002

 

Is this possible,  and how can i do this please?

2 ACCEPTED SOLUTIONS

Accepted Solutions

@Lefty 
Suggest you create a table in SharePoint with at least 2 columns:

 

ReferenceNumber SequenceNumber
REW-B-00001 1
REW-C-00002 2
REW-B-00003 3
REW-B-00004 4
REW-C-00005 5

 

Put this code in the OnSelect property of the button when the user submits from Screen1

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending);
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-B-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: nextSequenceNumber
    }
);

 

Simply change the reference number prefix for Screen2.

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending);
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-C-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: nextSequenceNumber
    }
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

@Lefty 

Very good question!  The data will be populated by PowerApps.

 

However, I had not considered what would happen if there was no data to start.  The code I provided should be modified like this...

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending).SequenceNumber;
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-B-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: If(!IsBlank(nextSequenceNumber),nextSequenceNumber, 0)
    }
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

15 REPLIES 15
mdevaney
Super User III
Super User III

@Lefty 
Question:

Is your reference number working like this?


REW-B-00001

REW-C-00002

REW-B-00003

REW-B-00004

REW-C-00005

Or is your reference number working like this?

REW-B-00001

REW-B-00002

REW-C-00001

REW-B-00003

REW-C-00002

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

@mdevaney 

 

like the first suggestion:

 

REW-B-00001

REW-C-00002

REW-B-00003

REW-B-00004

REW-C-00004

 

Thanks

@Lefty 
Suggest you create a table in SharePoint with at least 2 columns:

 

ReferenceNumber SequenceNumber
REW-B-00001 1
REW-C-00002 2
REW-B-00003 3
REW-B-00004 4
REW-C-00005 5

 

Put this code in the OnSelect property of the button when the user submits from Screen1

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending);
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-B-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: nextSequenceNumber
    }
);

 

Simply change the reference number prefix for Screen2.

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending);
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-C-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: nextSequenceNumber
    }
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

@mdevaney 

 

When you say create a table, did you mean just that, or did you mean create a list, last i checked the only table which can be created is on a page in SPO?

@Lefty 

Yes, I meant create a SharePoint list, not a table 😄

@mdevaney 

Thanks.

 

In both the columns (SequenceNumber and ReferenceNumber), the data will be populated by PowerApps right?

I dont need any data in either of the 2 columns, just 2 empty columns in SPO?

@Lefty 

Very good question!  The data will be populated by PowerApps.

 

However, I had not considered what would happen if there was no data to start.  The code I provided should be modified like this...

Set(nextSequenceNumber, First(Sort(your_datasource_name, SequenceNumber, Descending).SequenceNumber;
Patch(
    your_datasource_name,
    Defaults(your_datsource_name),
    {
        ReferenceNumber: "REW-B-"&Text(nextSequenceNumber, "0000"),
        SequenceNumber: If(!IsBlank(nextSequenceNumber),nextSequenceNumber, 0)
    }
);

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

@mdevaney 

Thank you very much, I will try this out, either today or after the Easter break and report back, but your instructions are very precise, so I expect this will work, I will mark it answered once I've tested, if that's okay?

@Lefty 
Yeah, sure thing.  Just let me know if it works or not 🙂

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (10,768)