Greetings, I am creating a form and workflow and am to the point where I want to start to work on datalinks. What are the best practices for creating a table capable of holding the data that I want from my form? Is there a way to create a table from inside ECM or do I want to create the table using SSMS. Any information would be greatly appreciated. Thank you.
@ZacharyLT Welcome to EpiUsers!!!
Zach - forms are actually considered documents inside ECM. When you link the form fields to content type data fields, those fields are automatically associated with the Form. There is no separate table for form data.
If you are looking to use that data elsewhere, like a datalink, and to push that data into ERP/Kinetic, then my understanding is that you simply ‘Attach to Epicor’ in your workflow for the form. It will be represented as a PDF form the Kinetic side.
If you want the data in a table, separate from the form itself, then you’ll need to create a SQL view whose structure may be something like this, depending on your setup. (This is just an exploratory query I made a while ago, so it’ll need to be tweaked for your setup)
SELECT *
FROM ContentType
INNER JOIN dbo.[ContentTypeCustomFields] ON dbo.ContentType.Id = dbo.ContentTypeCustomFields].ContentTypeID
INNER JOIN dbo.CustomFieldMeta ON dbo.[ContentTypeCustomFields].CustomFieldMetaID = dbo.CustomFieldMeta.Id
INNER JOIN dbo.CustomFieldValue ON dbo.CustomFieldMeta.Id = dbo.CustomFieldValue.CustomFieldMetaId
INNER JOIN dbo.[Document] ON dbo.ContentType.Id = dbo.[Document].ContentTypeId
INNER JOIN dbo.DocumentVersion
2 Likes
Thanks for your reply! In our custom developed workflow we used a write datalink to write specific data to a SQL table then we are creating a specific document number for instance starting at 3000 and incrementing by 1 every time a document gets completed. Is there any good way to do this other than the way that I described in your experience?
There are multiple ways to increment a value once you are in SQL (triggers, functions, etc.) so there might be a better way to do that part, but a custom DL to write data is the only way I can see to store data outside of the ECM field tables.
However, you could still write a SQL view using the query snippet - and then there would be no secondary copy of the data, the form would remain intact and a single version of the data, and the form/document has it’s own unique ID# via standard ECM processing. And the View would act as a table for whatever process you needed to create the table for in the first place.
Without knowing the whole picture, answering ‘is there a better way’ type questions is impossible. To me, the first question is why would you need a second copy of the data? You should never want a second copy of data for any reason. And given that ECM handles all the other data administration once you enter data into a form, the best possible solution is to use that data - hence the idea of a view which can use that data but preset it in a way to be consumed elsewhere.
Now if your big plan requires that data to be somewhere else (SQL allows for cross server/cross database queries so consider that as well) then you may need to stay with the custom DL.