We are a trucking company and need a solution to do ticket managment for our drivers. There are some limitations since they are all external but the first roadblock I am running into is managing new comments. The widget won't work because our owner doesn't want the users (drivers) to have to register and log into a helpdesk. He wants them to be able to submit a form and it opens a ticket on our internal helpdesk then they need the ability to add comments and also see comments added on our side to the ticket.
The solution we are trying is to use Plumsail public form to create the ticket using workflow then pass that ticket information into a SQL Server database table and use that to build a web side "My Tickets" grid on the drivers app we supply them. From there they can click the link to display the ticket information and comments as well as add a comment. The issue is the only way I can figure out how to get the comment we add into the SQL database table is through workflow using the Sharepoint List "when changed" trigger. But to match the ticket to the ticket number is a challenge because it doesn't store the ticket number that I see in the sharepoint "ticket" list only the ticket guid. am I missing something? I need to marry the ticket to the guid somehow by capturing the guid into a SQL Table cross reference then I would be good. Any help is appreciated!
Unfortunately, I can't fully understand your approach and the issue so some of my notes can miss the point or be absent.
The Tickets list has two fields for identifying tickets:
ID: it is a usual list item ID, the field contains unique integers
TicketID: it is a field that contains unique values, by default, you see numbers in it but actually, they are strings. This field is used to create custom IDs.
As I understand, you use a flow in Power Automate. If it runs on the change of an item in the Tickets list (the default SharePoint trigger), then you should have a list item ID as an output of the trigger.
There is a HelpDesk connector for Power Automate (currently, please use the custom one). You can use its action to retrieve all comments related to the current ticket, just use the list item ID from output of the trigger:
You will get an array of objects for each comment, each object will contain a body of the comment. Thus, you will be able to process it in your flow and to use it for updating the SQL table.
Unfortunately, I have no idea what is the structure of the DB tables, but each comment has unique IDs as well as tickets so the data can be bound with each other. I hope the information above has a sense for you and can be useful. If you still can't find the solution, please clarify forth your difficulties, perhaps, we can help to vanquish the issue.
Thanks for the information. It is very helpful but I am still stuck. I guess to simplify in order to build the web view of the ticket and all of the comments from the SQL Database tables I have the following...
Tickets table: All fields from when a new ticket is created on helpdesk.
Comments table: 3 columns: Ticket Number, email of commenter, and comment.
When I add a comment to the ticket from internal it creates the comment in the Sharepoint List called Comments. When I try to use that list to populate the SQL Table there is no ticket ID only a ticket guid. Example: I added a comment to ticket ID 3 the ticket guid in the comment sharepoint list is 35577571-a492-49b6-9e08-c3bfb1e1b976. I have no way to cross reference that to the original ticket.
