We have a use case where we want to create a sharepoint form for new tickets in the helpdesk that does some lookups in the SQL server and creates a HTML table from a DataTable control then would insert that into the ticket body along with information from multiple other fields that are not sharepoint list items. Is there any way to put certain list items (people pickers) on the New Item form plus all the other fields, then use a button click to "Submit" the form but not create the ticket (list item) and let Workflow create the ticket after massaging the data?
Hello Joe!
Is there any way to put certain list items (people pickers) on the New Item form?..
Yes, you can edit the New ticket form and add the accordant SharePoint fields. But of course, you should add the Person fields to the Tickets list first.
Is there any way to ... use a button click to "Submit" the form but not create the ticket (list item) and let Workflow create the ticket after massaging the data?
You can use spBeforeSave() event from the Plumsail Forms JS framework and perform all the required actions within it programmatically. Another option is to create a ticket through a Power Automate flow where you will be able to perform all the actions you need before creating a ticket. HelpDesk has own connector for it.
Thanks for the response. The before save event wouldn't work because some of it has to be performed within SQL Server. What I really need it to do is "Submit" the form so the workflow kicks off where I'll create the ticket from during that workflow after getting all the data needed from the SQL Server table. I wasn't sure how to submit the ticket where it does nothing but run the workflow with the form data so I can open the ticket after the fact. It keeps adding a record to the sharepoint list.
To elaborate a little more...
I have fields on the form that are not part of the sharepoint list. They will be used to create Microsoft Planner Tasks during a workflow. My issue is once I submit the form it creates the sharepoint list item. If I create the workflow to trigger off the sharepoint list item on create or modify I have no reference to the form data in the custom fields to do the rest of my tasks.
If there is a way to pass back the list item ID to the form after it saves then I might be able to fill that value into a text field on the form and use it in the workflow that way to update the parts of the sharepoint list item with post processing in SQL Server. If that makes sense...
Why wouldn't you add the custom field to the Tickets list and hide it from view? Thus, after submitting the form, a new item will be created and the required data from the custom field will be saved in the list column. The newly created item will trigger your workflow and it will be able to process all the properties of it, including the one required for creating the Planner task.
The first issue is I have to look up those fields inside on prem SQL server then modify them depending on the results. So it takes post processing before populating the fields.
Second this particular form is customized to a specialized ticket type. This is the start of a help desk for a department that will have many different custom forms with custom fields. Add all those fields to to Tickets list will quickly get very "Wide" and unneeded since the custom fields on the form are simply for looking up other data then concatenating it to the body of the ticket.
In short, you need to run a workflow instead of creating an item on submission of the SharePoint form. The rest should be done in your workflow. Please confirm that I got it right and I will ask developers though I doubt that it is possible. If I missed something, please state your request in short so I could just pass it without distorting it. =)
For now, since you use HelpDesk for SharePoint Online, I would suggest this scenario:
- Create Microsoft Form or use public Plumsail Forms to submit data.
- Create a Power Automate flow that will start on the form submission.
- Use an on-premises gateway to get access to your SQL server.
- Perform other actions that you would do in your workflow, including creating Planner Tasks.
- Then create a ticket using the HelpDesk connector.
Another option is to use another SharePoint list (some Ticket Drafts) and add to it all the required fields you need to submit. On adding a new item to that field, you run your workflow that uses the data from it and then creates a ticket in the normal Tickets list without overloading the latter with some service data.