Code

Re: automatically set form ID?

fredp613 — Thu, 12 Feb 2009 19:58:25 GMT

Hi Hilary, I understand now. However i'm having a problem with the Create Procedure statement. I created a new test database for this with two columns, 1 is called ID (primary key - autonumber) and the 2nd called TransNumber. The table itself is named "Trans". In order to create the stored procedure, I created a query and selected TransNumber, then opened the SQL expression menu and entered the following:

Create Procedure GenerateTransNumber
As
Begin

Set NoCount On;

Select NewID() as TransNumber;

End

Please let me know where i went wrong with this.

Thank you :)

Fred

Re: automatically set form ID?

fredp613 — Thu, 12 Feb 2009 18:53:17 GMT

Basically, for the query part, I dont really understand what u mean when u say:

"If you are storing your Ids in SharePoint or SQL, you can always run a query to retrieve the Ids and use a formula like Max(Id field) +1"

Thanks again

Fred

Re: automatically set form ID?

Hilary Stoupa — Thu, 12 Feb 2009 18:50:59 GMT

Actually, the blog post uses SQL, not SharePoint.

So you created a new data connection to your database -- does it have an ID column? You should set the data connection to query when the form opens. Set a default value on the field in the main data source where you want your ID. The formula for that value should use the max function to get the max ID from the data returned by your data connection. Then add one to that.

It sounds like you have a rule set on the box where you want to store the value. Rules are triggered when a field changes. So you may want to set the rule on the Vendor Name field and the Before Tax Price field to set the value for the Contract Balance field.

Re: automatically set form ID?

fredp613 — Thu, 12 Feb 2009 18:40:18 GMT

Hi Hilary thanks alot for the quick reply, and I really enjoyed your blog post :)), however sadly I do not have a sharepoint license in my office :((

There will be only one person using this form. Since I do not have a Sharepoint lisence in my office currently, I am using SQL only (Access DB).

I was able to create a data connection with my database. My problem is that I do not know how to perform the query so that when this user opens

a new form, a new referenceID (pulled i guess from my database) will appear automatically on the form.

Secondly, for the "contract balance" text box i created, it is part of a repeating table where this user can add multiple items to the purchase order.

In the text box i inserted a rule (which includes a formula). This rule says "if vendorName = XYZ, then perform this formula: 5000<--Contract total with XYZ minus

sum(beforeTaxPrice)<--this field is part of the repeating table.

Thank you so much ure time.

Re: automatically set form ID?

Hilary Stoupa — Wed, 11 Feb 2009 19:39:46 GMT

Generating Id's can get a bit tricky. You can find different approaches -- a lot of them use code. If you are storing your Ids in SharePoint or SQL, you can always run a query to retrieve the Ids and use a formula like Max(Id field) +1. However, this isn't fool proof -- you can always have an issue of more than one form using the same Id depending on when you call it. I have a blog post here with one approach to Ids.

On your other issues, I think I'd need to know a lot more about what your data source looks like to be able to understand what you are trying to do. What field should have the calucluated value? What fields are being used in the calculation?

Re: automatically set form ID?

fredp613 — Mon, 09 Feb 2009 21:24:18 GMT

Hi, I was hoping to get some help. I'm currently designing a custom purchase order form, and want to do the following:

1. When the user loads the form, I want infopath to generate an ID (i.e TRANS2009001).

2. I created a drop down list that connects to an SQL database (Access) with a list of all our suppliers. I was able to create rules for when, for example, if the user selects Supplier 1, all of Supplier 1's address information automatically populates. However, I also created a field in the Itemized list section called "contract total". So lets say I have 20 suppliers with 20 different contracts, each contract has a different dollar amount. Therefore when the user selects Supplier 1, not only the address will automatically be populated, but when he/she puts in an amount for a service, this "contract total" field will know to deduct the amount from its total original contract total. I've already tried using formulas such as $5000 - sum(totalpretaxprice). Also tried to create rules but nothing works. Please help me with this.

Thank you for ure time

Re: automatically set form ID?

nukkumatti — Mon, 21 Aug 2006 14:41:09 GMT

Hey Hendra,

i would not use the solution with the GUID. I had the same issue once and used the GUID in one case but the second time i used a webservice to generate my ID starting from 1 to ... .
In my opinion this is a better solution as you
1. don't get these really messy 128bit hex numbers
2. you can be absolutely sure that you will never have the same id twice

Check out this link to see what I mean: http://www.infopathdev.com/forums/topic.asp?TOPIC_ID=2102

Watch out, the code is in C#
BTW: From which part of germany are you from ?

Re: automatically set form ID?

hendra.kusuma@gmail.com — Mon, 21 Aug 2006 08:10:15 GMT

i try to generate random ID :

how can i write in jscript?

my code is :
function ID(GUID) {
var productNodes = XDocument.DOM.selectNodes("/dfs:myFields/tns:dataFields/tns:XUmmeldProcessRequest/tns:DocumentPrefs");
productNode.XDocument.DOM.selectSingleNode("tns:GUID").text = Math.random()*1000;
}

and it was not work..

thanks for help......

Re: automatically set form ID?

merddyin — Fri, 09 Jun 2006 04:24:00 GMT

Even though I see you already got an answer, the thing that worked for me was to setup a retrieve data connection from my forms library on my ID field. I then set a text field to do a count on the ID field and add 6000 to it. This gave me a starting number of 6000 and it increments automatically every time. Just an idea of another method.

Re: automatically set form ID?

mary_l — Tue, 30 May 2006 08:01:45 GMT

Thanks Anuma for your help!

It works just fine!

Re: automatically set form ID?

Anuma — Fri, 26 May 2006 10:24:09 GMT

Hi,
You can also generate unique GUID in Load event using "Scriptlet.TypeLib" ActiveX object (if you are in JavaScript):

var guid =
new ActiveXObject("Scriptlet.TypeLib").guid.substring( 0, 38);

or using System.Guid.NewGuid() method (if you are in .NET).

Thanks.

Re: automatically set form ID?

mary_l — Fri, 26 May 2006 09:48:53 GMT

Just realized that i have to use JScript to generate the GUID, but i cant get it to work. The code

var id = System.Guid.NewGuid();

just doesnt work. Is there another solution to this in JScript?

Re: automatically set form ID?

mary_l — Mon, 22 May 2006 14:41:40 GMT

Hi!

Thanks a lot for the tip! I havent tried it yet, but i think it is exactly what im looking for!

Re: automatically set form ID?

Anuma — Mon, 22 May 2006 13:16:33 GMT

Hi,
Check out this link.
http://www.infopathdev.com/forums/topic.asp?TOPIC_ID=448

I hope this will help for you.
Thanks

automatically set form ID?

mary_l — Mon, 22 May 2006 13:00:00 GMT

Hi!

Is there some kind of ID that is automatically created when you create, save or submit an Infopath form? And, in that case, is there a way to get this ID?

I would like to have the filename of my forms set to unique ID's when submitting to a sharepoint library. And i cannot use a database. Is there any easy way to do this?