Help with auto numbering

[JediWookie]

This is a wish list item, I am not sure if it's even possible to do. I'm not very familiar with building out formula's, just know enough to be dangerous lol.

In order to give each item it's own number, but visibly be able to identify it, I take Item Number and manually add MMYY to it. 

Item 215 if submitted in July would read 2150722. Today, I manually input the combined value into my "Tracker Number" column. 

Is there a way to Concat the MMYY value from the Created field and leave out the DD?

[LaurensM]

@JediWookie this is possible with a (1) formula or (2) via a Dataverse autonumber field.

(1) Please try the following Concatenate() function:

Concatenate(Text(ItemNumberField), Text(CreatedOnField, "mmyy"))

(2) Alternatively, you can create a new table field 'Autonumber'. By default the type will be 'String prefixed number', change this to Custom Number. Within the format fill in the following structure:

{SEQNUM:3}{DATETIMEUTC:MMyy}

This would solve it on a database level, but I think you already use an autonumber field for the item number. In addition, this is best created at the initial creation of your table and not afterwards.

I hope this helps!

[Anchov · Accepted solution]

Sure, you could do something like:

If(!IsBlank(ThisItem.ID), Value($"{ThisItem.ID}{Text(ThisItem.Created,"mmyy")}"))

[JediWookie]

Thank you! I just went to go try to put this in and realized, I don't see the ID column listed under all the different columns created for my list. So this may not work after all. 
I'm referring to the auto generated ID number that the list assigns the new entries. 

[JediWookie]

Hello!
When I created the list, it has an ID column that was auto generated onto the list. When I go into my List settings, I do not see a column called ID, so now I'm not so sure this will work after all. 

I'm setting up powerapps for on an already created 2016 SP list, this has been interesting lol.

[Anchov · Accepted solution]

Assuming this is SharePoint, ID is a system generated field, and wont show up in List settings.  You can still use it inside of PowerApps, as you can any other system-generated fields, once you connect it as a data source.  Just note, that the field is read-only, and only gets updated upon the first save.  

So, you will have to generate your user-generated ID only after the first save event, which is why in my code example I verify that it is not blank.  Hope this helps!

[LaurensM]

@JediWookie are you patching data via a form to SharePoint?
If this is the case, an easy way to get the ID after record creation would be the LastSubmit() function.

OnSelect of the form submit button:

//Your code to submit the form
SubmitForm(Form1);

//Updating the Tracker Number
Patch(DataSource, Form1.LastSubmit, {'Tracker Number': Concatenate(Text(Form1.LastSubmit.ID), Text(Form1.LastSubmit.Created, "mmyy"))}) 

 
If you don't use a Form and manually patch the record to SharePoint, try this:

//Your code to patch the record initially (this will generate the ID)
//Patch(... Defaults() ...)

//Your code to update the Tracker Number
UpdateContext({varLastRecord: First(Sort(DataSource, Created, Descending))})

Patch(DataSource, varLastRecord, {'Tracker Number': Concatenate(Text(varLastRecord.ID), Text(varLastRecord.Created, "mmyy"))})

 
Both solutions would update the Tracker Number field instantly after creating the record.

If this solves your question, would you be so kind as to accept it as a solution.
Thanks!

[JediWookie]

This worked! Thank you!!

[JediWookie]

On my sharepoint list itself, where it shows all the unique items. The number is not translating and showing me in the actual list view itself. When I open the form and view the item, it's showing. Do you know how I should have the actual list column configured to show the data on the site?