Answered

Increment reference number

(0) Share

Report

Posted on by Lefty

1,625

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (2)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@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."

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

@mdevaney

like the first suggestion:

REW-B-00001
REW-C-00002
REW-B-00003
REW-B-00004
REW-C-00004

Thanks

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Lefty

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

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like (1)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Lefty
Did my my suggestion work for you? Please let me know.

Was this reply helpful? Yes No