exelna
asked on
Retrieve identity number from sql db after submitting or submit on open to reserve number
Hello. I have read through some of the articles here about auto generated numbers and got a question.
I have a form setup so when it loads it grabs the max() for our SQL tables Identity field and adds 1 to get what the new number will be.
Problem is if user A opens the form and gets number 10, but then sits on it for a while before submitting it, user B may open that same form and it will also show 10, but whoever actually submits it first will get number 10, and the other would get 11. Which if fine except both users are going to think there form is number 10.
My first thought was to create a rule to submit the form on load so it would reserve that number but that gives me an error saying:
"InfoPath cannot submit the form.
The form does not contain any new data to submit to the data source."
My next thought was to have it so the form doesn't initially show the Identity number and then after submitting it would then then re-query the Identity field so it give them the number. I tried to create a Form Submit Rule that would submit then query for data (the identity #) but I cannot seem to get this to work.
Anyone have some detailed tips to get me on the right path. Thank you in advance.
I have a form setup so when it loads it grabs the max() for our SQL tables Identity field and adds 1 to get what the new number will be.
Problem is if user A opens the form and gets number 10, but then sits on it for a while before submitting it, user B may open that same form and it will also show 10, but whoever actually submits it first will get number 10, and the other would get 11. Which if fine except both users are going to think there form is number 10.
My first thought was to create a rule to submit the form on load so it would reserve that number but that gives me an error saying:
"InfoPath cannot submit the form.
The form does not contain any new data to submit to the data source."
My next thought was to have it so the form doesn't initially show the Identity number and then after submitting it would then then re-query the Identity field so it give them the number. I tried to create a Form Submit Rule that would submit then query for data (the identity #) but I cannot seem to get this to work.
Anyone have some detailed tips to get me on the right path. Thank you in advance.
ASKER
I think the Query for Max number is the part i'm not getting.
I created a Form Submit Rule that has 2 steps so far
1. Submit using data connection
2. Query using a data connection
When I do the Query for data connection it asks me which connection but I don't see any options on what to Query.
I tried adding a "Set a field's value" between steps 1 and 2 and pointed it to the identity field and then did the max () but that didn't seem to help.
As for my fields...the SQL Identity field uses a queryFields at the moment and the rest of the fields in the form are dataFields. Is this the right way?
Also, is there a way to make it so the Identity field stay blank until after the submission and query?
I'm trying to get this working so I can teach other people at my company as we have lots of forms we would like to make.
We are using Form Filler 2007 format and not a web form. No sharepoint, only SQL and published to network.
Thank you for the help.
I created a Form Submit Rule that has 2 steps so far
1. Submit using data connection
2. Query using a data connection
When I do the Query for data connection it asks me which connection but I don't see any options on what to Query.
I tried adding a "Set a field's value" between steps 1 and 2 and pointed it to the identity field and then did the max () but that didn't seem to help.
As for my fields...the SQL Identity field uses a queryFields at the moment and the rest of the fields in the form are dataFields. Is this the right way?
Also, is there a way to make it so the Identity field stay blank until after the submission and query?
I'm trying to get this working so I can teach other people at my company as we have lots of forms we would like to make.
We are using Form Filler 2007 format and not a web form. No sharepoint, only SQL and published to network.
Thank you for the help.
Yah, it sounds like you need to play alot. I would start with the default form and do some queries and updates and inserts until you understand everything.
when you create a new database type form, it does some stuff for you, it creates a main data connection that is both a receive and submit.
how it works is that the query fields are what it uses in the query parameters.
meaning you set the id field in the query fields to 1, it will return record id 1 in the data fields. If you edit a value in record 1 and then submit it will update the record on SQL. If you insert a new record it will create it on SQL
when you create a new database type form, it does some stuff for you, it creates a main data connection that is both a receive and submit.
how it works is that the query fields are what it uses in the query parameters.
meaning you set the id field in the query fields to 1, it will return record id 1 in the data fields. If you edit a value in record 1 and then submit it will update the record on SQL. If you insert a new record it will create it on SQL
ASKER
My Id field is in the default Query section as a queryFeild with the Run Query button . If I type a number in an Id number, like 22 and hit run query I get that record.
If I type in new information in the data fields and hit submit it saves them to the SQL server like it should.
I just need to figure out how to get it to submit, then automatically fill in my Id field and query for me. That's where i'm breaking down at.
Edit: Added attachment
TRS.jpg
If I type in new information in the data fields and hit submit it saves them to the SQL server like it should.
I just need to figure out how to get it to submit, then automatically fill in my Id field and query for me. That's where i'm breaking down at.
Edit: Added attachment
TRS.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! Got it. That is what I needed.
Thank you for all your help today clay *thumbs up*
Thank you for all your help today clay *thumbs up*
You just need to query after you submit.
That way you will not get that information has not changed.
Can be behind the scenes.
1. Insert Record
2. Query for Max number
3. Query with number and display to user
4. User Edits or whatever