cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bullman
New Member

Ticket Number Increment

Hi,

 

I am using Excel as my data source, essentially I want to give each row in the excel file a ticket number when a new ticket is raised. I have tried a few things but the information being sent to the table is just in labels and not a form.


I think the only way to do this would be to look up the previous record in Excel and then +1. Can anyone suggest if this is possible?

 

Kind Regards, 

Brett

2 ACCEPTED SOLUTIONS

Accepted Solutions
Drrickryp
Super User
Super User

Hi @Bullman 

Create a form based on the Excel table.  Set the default property of the TextInput control in the TicketNumber card to the following: 

 

If(IsBlank(Parent.Default),First(Sort(DS,Ticketnumber,Descending)).Ticketnumber+1,Parent.Default)

 

This means if the form is in New, the Parent.Default will be blank and the next Ticketnumber in the sequence will be in the control. However, if the form is in Edit mode, ie. there has already been a ticket created, then that Ticketnumber will be listed.  First(Sort(... is preferable to Last() or Max() because these functions are not delegatable and if your table exceeds 2k, you won't get the correct value.  Sort is delegatable and so you can have large numbers of tickets in the datasource and it will correctly assign the next highest number.  It is a good idea to either hide or disable this card so that users can't change the Ticketnumber value.  Also, make sure that the column type is Number in Excel and not Text. 

View solution in original post

Bullman
New Member

I tired both suggestions with no luck so gave up in the end... Instead I created an SQL database which has an ID as primary key which resolved the issue. 

 

Thank you to all the replies, they were very helpful and I am sure the solutions could've worked.

 

 

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @Bullman ,

You could

  • store the number in another table (could be on the same Excel sheet),
  • do a lookup on App OnStart and store it in a global variable
  • add 1 to the variable when a ticket is raised
  • Patch it back to both tables

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Drrickryp
Super User
Super User

Hi @Bullman 

Create a form based on the Excel table.  Set the default property of the TextInput control in the TicketNumber card to the following: 

 

If(IsBlank(Parent.Default),First(Sort(DS,Ticketnumber,Descending)).Ticketnumber+1,Parent.Default)

 

This means if the form is in New, the Parent.Default will be blank and the next Ticketnumber in the sequence will be in the control. However, if the form is in Edit mode, ie. there has already been a ticket created, then that Ticketnumber will be listed.  First(Sort(... is preferable to Last() or Max() because these functions are not delegatable and if your table exceeds 2k, you won't get the correct value.  Sort is delegatable and so you can have large numbers of tickets in the datasource and it will correctly assign the next highest number.  It is a good idea to either hide or disable this card so that users can't change the Ticketnumber value.  Also, make sure that the column type is Number in Excel and not Text. 

View solution in original post

Hi @Drrickryp,

 

Thanks for the help, I have tried this but with no luck at the moment. Attached is a screenshot, the row in excel is set to number, at the moment when a new entry is made it displays nothing in the ID field in excel. 


Is there anything else I need to change maybe in the Item property? 

 

Thanks,

Brett

 

 

Hi @Bullman 

It does not appear that you have an Edit form on your screen.  As an experiment, please allow PowerApps to create the app from your Excel file. If it is in OneDrive for business, choose to create it from Excel OnLine.  PowerApps will create an entire app with three screens.  You can then modify the Edit screen accordingly.

 

Capture.PNG

Bullman
New Member

I tired both suggestions with no luck so gave up in the end... Instead I created an SQL database which has an ID as primary key which resolved the issue. 

 

Thank you to all the replies, they were very helpful and I am sure the solutions could've worked.

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,219)