Office 2000 Integration and FP 2000

Using Office 2000 a range of features from Office products can be added to web pages. These include Outlook calendars, PowerPoint presentations, Active data pages (from Access), Spreadsheet models (Excel).

However there is a catch – these features require Office 2000 on the client computer and typically IE5 also. As a result these features are suited to Intranets meeting the Office 2000/IE5 standard. In later tutorials we will look at the Office 2000 integration features. 

Database backed pages and FP 2000

We are going to examine an approach that does not require Office 2000 on the client, but does require FP 2000 extensions on the server. This approach uses a "DSN less" connection to the database. All features of this exercise except the data input page work on QHiN. All features can be demonstrated using Personal Web Server.

Search a Database and Display Results

Preparation

	We are going to build a small on line phone book using an Access 97 database and FP 2000.
	This will include a full listing, a search page (by surname), and an input page.
	The database contains two tables and a query. The main data table will contain and ID number (as auto number format), name and location number, phone and e-mail data. The location table will list locations. The query provides a listing of all staff.
	Click here to download the zip file with the exercises in it - expand the zipped files and save these files into an "Exercises" folder on your computer
	In FP 2000 create a new EMPTY web called PhoneWeb

Importing the Database

	Minimise FP 2000
	Using My Computer open the Exercises/database folder
	Right click on phone.mdb
	Choose copy
	Maximise FP 2000
	Right Click in the Folders View pane
	Choose Paste
	The file import process begins
	When the Add Database connection dialog appears – enter the name phonelist
	Click Yes
	When asked to create an fpdb folder – click Yes (This is a special folder whose default settings prevent "browsing" of the database. – A global.asa file is also created)
	That’s it for this bit!

A bit more preparation

	Choose the folders view
	Click on the new page button
	Press enter to accept default.htm as the name of the new page
	Double click on default.htm
	In the Editor window enter the following text:

Phone Book Menu

Full Listing Search Add an Entry

Click on the Save button

Full Listing Page

	Click the new page button again
	Save the new page as listing.asp
	Click Yes to change file extension
	Double Click on listing.asp
	In the Editor window, type the heading " Full listing of staff directory"
	Press Enter to start a new line
	From the Insert menu choose Database, Results
	Choose Use an Existing connection
	Choose Phonelist from the drop down
	Click Next
	Choose Staff Listing View as the record source (this is a query combining the main data and location tables)
	Click Next twice
	From Step 4 of 5 choose the List formatting option
	From the list options – choose line breaks
	Click Next
	From Step 5 choose split records into groups – specify group size of 5
	Click Finish
	Save the page
	Create link to this page in the default page
	Preview the default page in the browser – follow the link and test.

Search Page

	Create a blank new page called results.asp
	From the Insert menu choose Database, Results
	Choose Use an Existing connection
	Choose Phonelist from the drop down
	Click Next
	Choose Staff Listing View as the record source (this is a query combining the main data and location tables)
	Click on More Options
	Click on Criteria
	In the Criteria dialog box, click Add
	In the Add Criteria Dialog box choose Lastname as the Field name
	Choose contains as the comparison
	Leave the value as Lastname
	Leave the Use this search form field check box ticked
	If you were using multiple search criteria you can set the relationship between search fields to AND/OR – we will leave this setting as is since we only have one criteria
	Click OK three times
	Click Next
	In Step 4 of 5 choose Table as the formatting option
	Leave other options set to defaults
	Click Next
	Choose split records into groups
	Set group size to 5
	Ensure add search form is ticked
	Click Finish
	Save the resulting page
	Add a link in default.htm to this page
	Preview and test in browser

Making a Separate Search page

	Create a page called search.asp
	Swap to results.asp
	Change to HTML view
	Cut all text from <form> to </form> including the form tags
	Swap to search.asp
	Change to HTML view
	Right click after the <body> tag
	Choose paste
	Change to Normal view
	Save page
	Revise link in default.htm so that search link refers to this page
	Save all pages and preview in Browser
	Test

Making E-Mail link "live"

	Open results.asp
	You may need to adjust the panes so that the editor window is larger
	Click once (ONCE ONLY!!!!) on <<Email>> in the Database Results Area
	Click on the create hyperlink tool
	Click Parameters
	In the Path box type Mailto:
	Click on Insert Field Value
	Choose Email
	Click OK twice
	Save
	Preview and test

Adding Records to the Database via a web page

Input Page

	Create a page called input.asp
	Choose Insert, Form field, Form (to create an empty form)
	Press Enter Twice
	Press Up Arrow key twice
	Insert a Table (Two Columns, Four Rows, Border = 0)
	In the left hand column enter the following prompts:

Surname

First name

Office Phone

Email Address

Location

	Adjust the Column widths
	Click in the right hand column opposite Surname
	Choose Insert, Form Field, One line text box
	Double click on the text box
	Rename field to Surname
	Click Validate
	Set data length to required and max length to 50
	Click OK twice
	Repeat for First name, Office Phone and Email fields using the following names

First

Office

Email

	Save the page
	Click in the right hand column for Location
	Choose Insert, Database, Results
	Set database connection to phonelist
	Click Next
	Choose locations as the record source
	Click Next Twice
	In step 4 of 5 choose Drop down List as the format for the query
	For display values choose district
	For submit value choose location ID
	Click Next
	Click Finish
	Save the page

Setting up the Form

	Right Click within the form and choose Form Properties
	Choose Send to Database
	Click Options
	Click on the database results tab
	For Database Connection to use choose Phonelist
	For Table to hold form results choose staff
	Click on the Saved fields tab
	Choose first then click modify
	For Save to database column choose Firstname
	Click OK
	Repeat for each field
	If a field called T1 is present in addition to the expected fields – this represents the submit button. We don’t need to store this in the database so choose T1 and click remove
	Click additional Fields tab
	Choose each one and click remove (you could use these fields to keep an simple audit trail if we had matching fields in the database to store the data)
	Click OK twice
	Save the page
	Add a link to this page in the default.htm page and preview and test

The End!