Generate sequential number generator using a button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian 33 - 06 Nov 2006 16:34 GMT

Hello all,
I'm new to InfoPath 2003, so any help would be greatly appreciated. I
am using InfoPath 2003 with SP2 and my table is stored in MS SQL 2005
with SP5.

I have created a form that will use a client's phone number as a case
number, which is also set as the primary key in sql. Since some of our
client's do not have a phone\phone number, I would like to have button
on my form that would allow users to click it and generate a sequential
phone number that starts with 555, or at the very least verify the
number is not already saved in my table.

Example:
User clicks "Generate Phone number" button and "Phoneno" text field is
populated with 5550000001. Once this record is saved next time a user
clicks the "Generate Phone number" button the "Phoneno" text field
would be populated with 5550000002 and so on.

I've been playing around with the formulas in InfoPath and done a lot
of research on the web, but haven't found anything yet.

Thanks

Brian

Reply to this Message

John Sivilla - 06 Nov 2006 18:23 GMT

One way is to create a data connection that imports all phone numbers or use
a select statement to include only maximum into your form from your database,
and then have a formula that would increase the maximum number by one.
max(FieldContainingPhoneNumber) + 1. Just make sure the data type of the
phone number is a number. If not create another numeric field to store this
info.

You should do this just before you save the form so that you do not get
duplicates.

hope this helps,

Signature

John Sivilla
Developer IT
Montreal, Canada
Blog: http://spaces.msn.com/members/sivilian/

> Hello all,
> I'm new to InfoPath 2003, so any help would be greatly appreciated. I
[quoted text clipped - 20 lines]
>
> Brian

Reply to this Message

Brian 33 - 07 Nov 2006 17:09 GMT

Thanks John
Although my problem is not quite resolved, I did make progress, so I
wanted to update what I did so far for others that may view these
posts.
I made my phone number column numeric in SQL. I created an additional
data connection to "receive data" from "Database (SQL)". Also when I
went to my button's rules, I made sure to click "Insert field or Group"
and select my new data connection as the data source. I also made the
fomula you suggested:
max(FieldContainingPhoneNumber) + 1

This will work for the maximum phone number. I want users to be able to
enter any phone number that may be correct, but if they do not know the
phone number, the button should always generate starting with
"5550000001" and be sequential after that. RIght now it is picking the
highest number in the DB and adding one

I tried setting a condition for Phoneno > 5550000000 and Phoneno <
5560000000 to no avail.
Is there something else I can add to the formula? I don't think
"substring" = "555" will work on a numeric field? Could I set the data
type in SQL to 'char' and use max and substring together?

Thanks again,

Brian

> One way is to create a data connection that imports all phone numbers or use
> a select statement to include only maximum into your form from your database,
[quoted text clipped - 38 lines]
> >
> > Brian

Reply to this Message

Brian 33 - 07 Nov 2006 19:07 GMT

I figured it out!
I created a new view in SQL to hold only the range of phone numbers I
wanted like this
CREATE VIEW dbo.[555Range]
AS
SELECT Phoneno
FROM dbo.Demographics
WHERE (Phoneno BETWEEN 5000000000 AND 6000000000)

I then ran the data connection wizard and created a new connections to
receive data for my view and the table and view by phoneno. I other
thing I noticed is when you save a record with the new generated number
and attempt to click the generate button again the number will not be
refreshed, so I set my form to close when it is saved and it will be
refreshed when it is reopened

Thanks for your help John!

Brian

> Thanks John
> Although my problem is not quite resolved, I did make progress, so I
[quoted text clipped - 65 lines]
> > >
> > > Brian

Reply to this Message