Qdabra Database Accelerator

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Thu, 23 Dec 2010 02:48:26 GMT

Hi Jimmy,

Yup, I moved on to use a trigger to handle this issue. I suspect my form code wasn't updated as mentioned here: http://www.infopathdev.com/forums/p/17876/62536.aspx#62536. My code mentioned in this link was also a loading event code.

Anyway to answer your question, yes the docID wasn't reset to a new number and the form was saved as version 2 when I submitted the form.

Re: How to submit a form as a new version instead of as a new form in DBXL?

Jimmy — Wed, 22 Dec 2010 09:50:49 GMT

Hello Zhen,

I tried using your modified VB version of the code in a form template, but it worked as expected. When I opened an existing form and submitted it, it was added as a new document. Are you saying that even with that code, Submit overwrites the existing document instead of creating a new one? What approach are you using to submit the forms? Could you try adding this line of code after the Catch... line to see if there's some error to indicate something going wrong (seeing "Object reference not set ..." on new forms is expected):

MessageBox.Show(delDOCID.Message)

An alternate approach you might consider trying is using a separate table and a trigger to keep track of all the activity on your DB table. The steps would be as follows:

Add a column called VersionNum to your original table
Change your DBXL mapping to map DBXL::Version to this column
Create a new table with a schema identical to the original one, but with a new name, like "TableNameVersions"
Add the following trigger to the original table, replacing the underlined parts with the appropriate names. This trigger will record any data changes made on the original table to the new table, and leave them there

CREATE TRIGGER [dbo].[Trigger_Track_Version]
    ON [dbo].[OriginalTable]
AFTER INSERT, UPDATE
AS

BEGIN
    SET NOCOUNT ON;

    INSERT INTO VersionTrackingTable
    SELECT * FROM inserted

END

Might this be closer to what you were originally looking to do?

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Thu, 09 Dec 2010 03:04:36 GMT

Hi Patrick,

Once again I revisited this issue to try and reset my form's docID even if I opened the form from the documents tab in DBXL. I've tried Jimmy's method from his blog you provided but met with no success. I left him a comment (which is pasted below). Shall wait for his reply but decided to update this post for future reference for myself or others.

Thanks for your help so far!

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Hi Jimmy,

I tried using your code method but my forms are still submitted as a new version instead of as a new docid. I placed the code below on my form's loading event and then opened a submitted form from DBXL and resubmitted the form. The end result was the newly submitted form was submitted as a new version of the original docID. Did I miss out anything?

My code:
Dim piXPath As String
Dim pi As XPathNavigator

piXPath = "/processing-instruction()[name() = 'QdabraDBXL']"

pi = Me.MainDataSource.CreateNavigator()

Try
pi.SelectSingleNode(piXPath, Me.NamespaceManager).DeleteSelf()

Catch delDOCID As Exception

Return

End Try

Re: How to submit a form as a new version instead of as a new form in DBXL?

Patrick Halstead — Fri, 15 Oct 2010 06:44:28 GMT

Hi ZhenYuan,

DBXL has a versioning feature and there should be a couple documents in Downloads that describe how to access it. You have to turn on versioning via DAT. Every version of every XML is stored and you can use that version ID to differentiate between different correspondence.

Another idea is to use a section for history and generate a GUID on forum submit. We have a History XTP that you can add to your form to get history (send me private email to get this). You'll need qRules to generate the GUID.

Finally, DBXL comes with Workflow Document Types. You can use qdEmailFlow to track correspondence and assignment. Search for qdFlow. Basically, it submits a separate document on submit and you can use the DocID for that document to track correspondence.

Hope this is semi-helpful,

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Thu, 14 Oct 2010 07:19:15 GMT

Hi Partrick,

Yep, we should be checking out qRules soon. In the meantime, can I check if qRules or any rules can be used to solve my following problem?

I have a form that will have multiple correspondence between parties and I need each "case" to have a RequestID. So on form initiation, a RequestID is generated. It has to be a unique running number which I can use to identify the number of "cases" my department has handled to date. However, I will also need a "version no." to track the correspondence between parties. Based on the same RequestID, each correspondence will have an identifier which should again be a unique running number so as to track which is the latest correspondence.

Any creative ideas to help me here?

Thanks!

Re: How to submit a form as a new version instead of as a new form in DBXL?

Patrick Halstead — Fri, 24 Sep 2010 17:50:19 GMT

Yeah, it's easy to remove the DocID using qRules RemoveDbxlPI command. Do you have InfoPath 2007 or 2010? If so, download a free trial of qRules from our web site, inject it into yoru form and use the RemoveDbxlPI command on open to reset the DocID. If this works, send email to Ted and he can provide info on a full version of qRules (it's dev license based and very cheap but we should be able to give you an additional discount since you bought DBXL). You can also do this without qRules by writing some code.

Here's Jimmy's blog that describes both techniques: http://www.infopathdev.com/blogs/jimmy/archive/2010/06/15/saving-forms-as-new-to-dbxl.aspx

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Fri, 24 Sep 2010 09:02:00 GMT

I see, thanks for the link Patrick.

However, is it possible to refresh the DocID on a form I sent out as an email? Reason is because my form is using DocID for versioning. I used a generated GUID to identify my forms before they are sent out as emails to recipents. On receipt and opening the form, the recipent now has a form of higher DocID number. I then use this DocID as my version numbering system. As long as there is a higher DocID, that form is recognized as the latest form version. As such, my database looks something like this:

GUID DocID

1234 100

1234 101

3456 102

1234 103 , etc, etc...

When I started using DBXL::Link to update forms, the DocID does not increase but ver no. did. But I still want my database to display as above. Therefore, I'm looking for a way to refresh to a new DocID on open event. Any idea how this can be possible?

Thanks!

Re: How to submit a form as a new version instead of as a new form in DBXL?

Patrick Halstead — Fri, 24 Sep 2010 08:16:37 GMT

Yes, you can receive form data for version 2 even if the latest version is version 5.

Here's a doc that describes the URL format: http://www.infopathdev.com/files/folders/other_topics/entry56287.aspx

There are a couple other docs related to history. Search on DBXL History to find them.

One good technique is to have the history section at the bottom of your form with datetime, user, description, status, and a link to the previous version. The link can be created by following the formatting in the above linked document. In addition, you can use the Web Service methods to get older versions and bind these into a form as secondary data. Then, you could make your view into a repeating section and copy the data into it from previous versions. If you have rich client, you can use taskpane to show links to older forms. If you use the first approach if just adding links to your form, I recommend adding a on load view switch event that checks to see if there is a more recent version and if so switches to a read only view. Otherwise, users could be confused and try to submit an old form even though I think the system prevents this.

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Fri, 24 Sep 2010 07:16:20 GMT

"Even then, the link seems to be only able to point to the latest version. Can't do much version tracking like this. Or did I miss out some auditing function?"

On this note, I found out that if we were to use doclink and send it to users, their updates are registered as new versions of the DocID and the version no. will continue to increase.

Is there a way to commit new versions of a DocID as a new row in SQL DB? Or is it possible to retrieve form data for version 2 if the latest version is version 5?

Thanks!

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Fri, 17 Sep 2010 08:30:48 GMT

Hi,

Yes, the email did submit as new each time. Was wondering how the versioning in DBXL can be made use of? The only way I can think of is to generate emails from SQL agent after the form has been committed to DBXL and paste the DBXL::Link in the email. Even then, the link seems to be only able to point to the latest version. Can't do much version tracking like this. Or did I miss out some auditing function?

We used a workaround. We had a button generate a GUID and plant it in a hidden field on the form. This GUID then becomes our unique identifier for the form and we used DBXL::DocID for our versioning haha. Are you guys flipping yet? We figured that we will obtain the latest version of a particular form by identifying it by GUID then using max(DocID). Not the most pretty solution but its free :)

It would be good if in future DBXL versions, a feature allows us to commit form data to DBXL before generating an email with a link to the specific form. Hopefully, it comes with the main DBXL package and not separately like in qRules. I was refered to it several times but due to time contraints (1.5 months to go from scratch to live production) we were not able to explore qRules nor get a budget for it. Will check out qRules in due time though.

Thanks for the support guys, this forum has been immensely helpful. Cheers!

Re: How to submit a form as a new version instead of as a new form in DBXL?

ErnestoM — Thu, 16 Sep 2010 15:07:49 GMT

Hello,

I'd like to address the first issue you bring up. The problem is that the email you're sending contains an XML generated BEFORE submitting to DBXL. This means that the xml has no information about the DBXL::DocID, so it will always assume it's "new" and submit as new to DBXL.

It sounds like you could benefit from on-assignment notifications, but this feature has not yet been advertised/documented. This would generate assignments in DBXL, and when an XML is assigned to someone DBXL generates an email. The person would receive an email with a link to open the xml.

Another option would be to implement something like what is explained in this blog post, but with DBXL instead of SharePoint.

Do either of those sound like good options for you? let me know your thoughts.

Re: How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Thu, 16 Sep 2010 04:38:08 GMT

I also require new versions of the form to be stored in a new row in my SQL. Right now, as I increase the form's version no. by opening the form from DAT->Documents and altering the form, my SQL no. of rows remained the same. My alteration was update to the database as an update rather than an insertion of a new row with identical formID but higher version no?

Is it possible to update a form and have it pushed into my database with the same FormID but higher version no.?

Thanks

How to submit a form as a new version instead of as a new form in DBXL?

ZhenYuan — Thu, 16 Sep 2010 03:55:04 GMT

Hi,

I have got my form set up and submitted to DBXL and then to my SQL server. The form on user submit first sends an email to the recipent using InfoPath's submit as an email message data connection before getting committed to DBXL using DBXL web service document submit.

The recipent then looks at the form from the email before approving or rejecting and replying to the user. Again there is first an email message to the user followed by a DBXL webservice submit.

The problem is, this results in new formIDs used each time the user or recipent replies. My intention was for this user-recipent transaction to be using the same FormID but increasing the version number during each reply. Can this be done using DBXL? Understand that there is a DBXL token called DBXL::Link. Was thinking this might help but is not sure how I can force recipents to open the form using this link?

It seems that by sending the form as an email message, what happens is that the form template is sent in the email with the form data. Each time a recipent opens the form on the email, it is equivalent with starting a new form just that it contains the data from the previous user.