Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

InfoPath 2003 - Part 3

By Dinesh Asanka,

InfoPath 2003 and SQL Server 2000 - Part 3

Introduction

Up to now we have discussed Basics of InfoPath 2003 and How to view data from InfoPath 2003 from the previous articles. Now it's time to discuss the basics of saving data from InfoPath 2003.

Implementation and Database Design

I am always lethargic to record my daily expenses. So I decided to go for InfoPath2003 and SQLServer 2000 for this. My main objective is to learn something from real time experiences.

First I decided to have two tables, one for keeping expenses and other one to categorize them. The following is the script of the both tables.

CREATE TABLE [dbo].[Expenses] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [smalldatetime] NULL ,
[Month] [smallint] NULL ,
[Year] [smallint] NULL ,
[Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Amount] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ExpensesCode] (
[Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Expenses] WITH NOCHECK ADD 
CONSTRAINT [PK_Expenses] PRIMARY KEY CLUSTERED 
( [ID] ) ON [PRIMARY] 
GO
ALTER TABLE [dbo].[ExpensesCode] WITH NOCHECK ADD 
CONSTRAINT [PK_ExpensesCode] PRIMARY KEY CLUSTERED 
( [Code] ) ON [PRIMARY] 
GO
CREATE INDEX [IX_Expenses] ON [dbo].[Expenses]([Year], [Month]) ON [PRIMARY]
GO
CREATE INDEX [IX_Expenses_1] ON [dbo].[Expenses]([Code]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Expenses] ADD 
CONSTRAINT [FK_Expenses_ExpensesCode] FOREIGN KEY 
( [Code] )
REFERENCES [dbo].[ExpensesCode] ( [Code] ) ON UPDATE CASCADE 
GO


ExpensesCode table contains following records

TRV Traveling
MED Medicine
STAT Stationery and Books
FOOD Foods
OTHER Other Expenses
DONA Donations

I have added Month and Year fields to the expenses table, for these two reasons.

1. I have not found a method to inquire a whole months worth of data from InfoPath 2003. Once again, if anybody knows you can educate us.

2. If you were to inquire from month and year repeatedly for better performances, you would be better to have two separate columns.

These fields will be updated from a trigger called trgUpdateMonthYear.

create trigger trgUpdateMonthYear on expenses
For insert, update
as
begin
Declare @ExpenseID int
Declare @ExpenseDate smalldatetime
Select @ExpenseID =[id], @ExpenseDate = [date] from inserted
Update expenses Set [Month] = month(@ExpenseDate),[Year] = year(@ExpenseDate)
where [id] = @ExpenseID
end

Every thing is set to start InfoPath2003

InfoPath Design

First step is designing a new form and creating a data source for expenses table as we did in the part 2 of this series.

After the getting the first screen you can do the necessary changes to the caption of the headings. Delete data view as we do not need it.

Figure 1 : First screen after doing some changes to the caption of the heading after the wizard.

Next step is to add the Repeating Table section. Then select Date, Code, Description, and Amount fields from data fields. Make sure that you do not select Year and Month from the list as those fields are updated by the trigger trgUpdateMonthYear. Remove ID, Amount from the latter part, which is the inquiry part. As usual I have done some formatting. Hope you will not find any difficulty in completing this.

Figure 2 : After adding Repeating table

Next step is the adding the combo list for expenses code. First delete the text box of expenses code which is laid in the repeatable table. Then drag an drop the drop-down list box from the controls and bind the ExpensesCode of the data fields. Then right click the drop-down list and select properties. In the data tab, select ExpensesCode as the new data source. Then select the XPath button which is indicated at the Figure 3. In that, select ExpensesCode group from the list. By Selecting other two buttons respectively, you can select code and description for the relevant filed. Why we need two fields? Code is to write to the expenses code and description is to display for convenience of the user. It will be nice to display "Traveling" rather than "TRV". Isn't it?.

Figure 3 : Configuring Drop-Down list box

Like wise carry out the same procedure for the code in the inquiry section. Let me elaborate a little on the use of inquiry fields. Let us say that you want to display expenses which occurred on the date of 1/1/2004. what you have to do is, enter 1/1/2004 at the date an click Run Query. Then you will get all the expenses which happened to be on the date of 1/1/2004. you can do it by combining the two or more fields as well.

Now for the user friendly interface, we can give user to select a month rather than enter 1 for January 2 for February etc. What we can do is, to have a Drop-down list box there. For earlier expensesCode we select database to get data But this time we do not need a database as the values are not changing. so we can select Enter list box entries manually and enter data for all the twelve months. Value is what you have store in the database and Display Name is what you give for the user.

Figure 4 : Entering List Manually

Wouldn't it be nice if you can display the total of the amount which you are currently entering? There is a special control available for that that is Expression Box which you will find at the second last control of the controls list. Drag and drop it to the necessary place of the form. Right click that control you will get the Property Dialog of the Expression Control.

Figure 5 : Property Dialog box of Expression Box

Select XPath and select Amount of data fields. After that type sum to get the total of amount. You will see initially you will get NaN for this field. This is because that there is no default value for the Amount field of the data fields. To avoid this you can set a default value for the amount value. Also you can do some validations like amount cannot be less that 0.

Figure 6 : Validation conditions for the Amount

Then add the submit button( I almost forgot it :-)) . Drag and drop a button from the controls and set the action to Submit

Data Entry and Inquiry

Now everything is set and ready to go. Click Preview Form. Now you can enter your daily expenses.

Figure 7 : Entering Data from Info Path 2003

By right clicking the left hand side of repeatable table, you can get a new line to enter data. If there is any spelling errors you can connect here it self. Always a good feature to me as I am very much weak in my spelling.

After entering if you want to view the expenses of the month of January, Select January from the month list an press Run Query.

That is all about entering data from the InfoPath 2003. InfoPath ability to enter data holds the advantage over the other tools like Crystal Report and Reporting service where they do not allow you to enter data.

InfoPath2003 SP 1

Before winding up, let me make an announcement regarding InfoPath 2003 developments. Now Microsoft Office 2003 Service pack one also released!. You can now download and install the official InfoPath SP1. It is part of the Office 2003 SP1 download.

More details: http://office.microsoft.com/officeupdate/ . English SP1 download is here

Please pay special attention to the following if you have installed the InfoPath 2003 SP1 Preview:

If you previously installed the InfoPath 2003 SP1 Preview, you should uninstall the InfoPath 2003 SP1 Preview prior to installing Office 2003 SP1. You will need your original InfoPath 2003 product CD to upgrade to the final version of InfoPath 2003 SP1. For more information, see the Microsoft Knowledge Base article (873040): Information to consider before you apply Office 2003 Service Pack 1 to a computer that has the InfoPath 2003 Service Pack 1 Preview installed

This Service Pack 1 gives you some additional features. We will discuss those things in next article.

Total article views: 7068 | Views in the last 30 days: 0
 
Related Articles
ARTICLE

InfoPath 2003 - Part 2

Continuing with Dinesh Asanka's series on SQL Server and Infopath 2003, he shows us this time how to...

ARTICLE

An Introduction to InfoPath 2003

Office 2003 has a new tool: InfoPath 2003, which can work with SQL Server along with numerous other ...

FORUM

SOAP Web Services for use in InfoPath

Data Connection for InfoPath

FORUM

Need high resource expensive sql queries

Need high resource expensive sql queries

FORUM

Date field in Select & Group by

having trouble with Start Date field when included in select and group by clause

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones