Solved: Re: Pre populate ascending ref no.

Anonymous · ‎02-13-2020

Hi,

I have a Canvas app form, SPO list as the data source, I have a field which is a single line of text field, i'd like to prepopulate the following ABC001 and make it an ascending ref no.

But as i'm still testing, i'd like to be able to reset this when we go live.

I found info around using entities, but I don't have the correct access to create these, is there any other way I can achieve my requirements?

Thanks

Anonymous · ‎02-13-2020

@Anonymous

Sorry, I should've also mentioned that you need the same code that's in your OnStart to also follow on from your SubmitForm(). ie

SubmitForm(Form1); ResetForm(Form1);

// same code as OnStart

Set(VarNum,First(Sort(ListName,ColumnName,Descending)).ColumnName);
Set(VarMaxNum, Value(Right(VarNum,3)));

Another way to do this is to use the LastSubmit property of your form, like this:

SubmitForm(Form1);

// gets last submit RefId

Set(VarNum, Form1.LastSubmit.ColumnName);

ResetForm(Form1);

Set(VarMaxNum, Value(Right(VarNum,3)));

Either way will work.

View solution in original post

Anonymous · ‎03-06-2020

@Anonymous

Just so I understand the process,

- a user will come into your app and select whether the complete Part A / Part B / Part C / etc of the form

- then Submit

- they only ever complete 1 PART? True?

If so, we could do 2 things to adjust the RefNo.

1. Say they are selecting which part to do via a number of Buttons, add this to each button's existing code:

// for Part A
Set(vFormPart, "-A-");

// for Part B
Set(vFormPart, "-B-")

Note: like I said, if you have many buttons only add the relevant code above for each button, don't add all this code.

Then in the Concatenate formula in datacardvalue3, add this code bit:

Concatenate("ABC", vFormPart, Text(vMaxNum + 1, "000"))

If the above button structure isn't what you are doing please send a screenshot of your process and I'll update my answer. Eg you maybe using a Dropdown then a Button to select the form Part, which would mean I need to adjust the above slightly.

Let me know how you get on.

View solution in original post

Anonymous · ‎02-13-2020

@Anonymous

If your ref number is always going to be in the format ABC###, you could set a variable in App.OnStart

---------------------

// this gets the most recent RefNo

Set( vMaxRefNum, First(Sort(SPList, RefNumColumn, Descending)).RefNumColumn);

// this gets the last three characters of the most recent RefNo and converts to a value

Set( vMaxNum, Value(Right(vMaxRefNum, 3)))

---------------------

Then, the Default property of the RefNum datacard can be set as

Concatenate("ABC", Text(vMaxNum + 1, "###"))

I haven't tested this but should be close.

To be able to give you an idea about resetting after testing I'd need an idea of what state your SP list will be in when you start eg many records but without RefNo, or no records, or some records with RefNo ...

Anonymous · ‎02-13-2020

Hi @Anonymous

I've tried this, but the RefNo field is always set to ABC1 rather than ABC001

Furthermore

On my default property of the RefNum datacard, it seems to add a bit more on the fx not sure where that is coming from:

Concatenate("ABC",Text(VarMaxNum + 1, "[$-en-GB]###"))

Anonymous · ‎02-13-2020

@Anonymous

Try 000 instead of ###. That extra formatting detail is fine, it's something PowerApps does for you.

Anonymous · ‎02-13-2020

Hi @Anonymous

ah that works Thanks, but numbers in the field seems to always stay ABC001 even after submitting my form, my next form is also ABC001

On my Onstart:

Set(VarNum,First(Sort(ListName,ColumnName,Descending)).ColumnName);
Set(VarMaxNum, Value(Right(VarNum,3)));

Anonymous · ‎02-13-2020

@Anonymous

Sorry, I should've also mentioned that you need the same code that's in your OnStart to also follow on from your SubmitForm(). ie

SubmitForm(Form1); ResetForm(Form1);

// same code as OnStart

Set(VarNum,First(Sort(ListName,ColumnName,Descending)).ColumnName);
Set(VarMaxNum, Value(Right(VarNum,3)));

Another way to do this is to use the LastSubmit property of your form, like this:

SubmitForm(Form1);

// gets last submit RefId

Set(VarNum, Form1.LastSubmit.ColumnName);

ResetForm(Form1);

Set(VarMaxNum, Value(Right(VarNum,3)));

Either way will work.

Anonymous · ‎02-18-2020

HI @Anonymous

Sorry for the delayed response, I've been away.

I've tried the first suggestion and copied the same code on AppOnStart to my OnSelect property of my submit button, but still doesn't work.

On my Onselect, I also have Office365Outlook.SendEmailV2 set up, so I tried the 2 lines of code after SubmitForm(Form1_2); but that created an error on the sending of the email, if I set the 2 lines of code after the SendEmail, no errors, but the ref number stays static.

In regards to your 2nd option, I've set my ResetForm(Form1); on the OnSuccess property of the form, so tried the Set(VarMaxNum, Value(Right(VarNum,3))); On the OnSucesss property, but that didn't generate a success message, but the form submitted, but again the ref number stays static at ABC001

Can you see what my be wrong, or what I may have done wrong?

Anonymous · ‎02-18-2020

@Anonymous

Can you share some of your actual code along with screenshots of the errors you are getting. Screen shots of your app would help as well. This will just give me a better idea of the issue/s.