question

annonnon-4680 avatar image
0 Votes"
annonnon-4680 asked annonnon-4680 commented

How to auto-generate a specific number using information from date column on Sharepoint

Hi,

Apologies if this is more suited to be posted on Power apps community, but I would like some assistance if possible:

I have a sharepoint list with columns, among other columns, titled: Serial Number, Job type, Serial Start Date, and Serial End date.
The ‘Job types’ column includes 5 different choices to choose from.

For the serial numbers, I would like them to be auto filled as Date+Specific 3 digit code of the job type.

Eg. If I select Job Type 1, and the start date is on 29/10/2021 and end date on 29/10/2021 as per the date and time column,

           Serial Number = 29001

If I select Job Type 2 and choose the dates 28/10/2021 to 28/10/2021,

           Serial Number = 28002


Is there a formula to use in Sharepoint to do this, or a code to put into Power Apps form?

Would greatly appreciate any assistance. Thank you.



office-sharepoint-onlinepower-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EmilyDu-MSFT avatar image
0 Votes"
EmilyDu-MSFT answered annonnon-4680 commented

@annonnon-4680

You could create a calculated column named Serial Number, then use following formula in the calculated column.

=IF([Job type]="Type1",DAY([Serial Start Date])&"001", IF([Job type]="Type2",DAY([Serial Start Date])&"002"))

Result:

144880-1.png


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.








1.png (13.4 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@annonnon-4680

I’m checking how the things are going on about this issue. Whether the answer helps you?

If there is any problem with this issue, feel free to let me know.

1 Vote 1 ·

Thank you so much @EmilyDu-MSFT !

It worked great, but I was wondering is there any way I could get the 0 to show up for days where its 1-9?
I.e 1/10/2021 to be 01001 instead of 1001?

Thank you again.

0 Votes 0 ·

@annonnon-4680

You could use below formula in the calculated column.

=IF([Job type]="Type1",TEXT(DAY([Serial Start Date]+1),"dd")&"001",IF([Job type]="Type2",TEXT(DAY([Serial Start Date]+1),"dd")&"002"))

Result:
145762-1.png


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



1 Vote 1 ·
1.png (21.2 KiB)
Show more comments