The first field in each table is a numeric field named OID and this is used only for synchronizing the Pocket PC based applet. This applet was created with Visual CE from http://www.syware.com and if you want to modify the PPC applet, or create similar applications of your own you may want to visit Syware's web site.
In the screen shot above you see the Mcompanies table in design view. As mentioned the first field, OID, is used by the Pocket PC applet. The next field is a simple auto numbering field and the last field is the Company Name field. The OID field is not displayed in the windows application, the PPC applet, or the web page interface. However, that field defaults to 0 when adding a new record. Visual CE uses this field to determine whether a record has been synced to the Pocket PC - or not, and if it has been synced the OID field is used to keep the table synced between the desktop database and the Pocket PC applet.
Let's next look at the Mcontacts table. This table is used by the program to record contacts with customers, or requests by customers.
As with the other tables, the first field is the OID field used for Pocket PC synchronization. Next is an ID field, a unique, automatically generated number incremented by a value of 1 each time you add a record. If you add a contact on the Pocket PC rather than the windows or web application, it is possible that auto number generated on the Pocket PC will conflict with an auto number generated in the windows or web application. If this happens you will need to change the ID to make sure it is unique.
The Contact Date field defaults to the current date. The LoginID number relates to the People table. This ID number is filled in for you by the windows application whenever you select a customer name. In the case of the web interface, if you have customers submitting support requests via web browser, this Login ID field is filled in automatically as well. However, if you use the Pocket PC applet to add a new contact record, you will need to select the Login ID - it will be displayed from a drop down list as the customer's name. Also, if you use the web application to add a contact record on behalf of a customer you will be able to select their Login ID from a drop down list which displays their name.
The Customer name field is a required field and is looked up from the Mpeople table. The Status field will default to New and is populated from the MStatus table. The Contact Details field is a variable length field and is where you record the contact information or the request for support. The Response field is also a variable length field and is where you record the action you (or your staff) took or plan in response to the person's contact.
The Taken By, Assigned To, and Completed By fields are all looked up from the MPeople table, use the WebLogin field. The People table has a field called PersonType which will be either "Customer" or "Staff". A SQL query is used to filter the table to only those people who are Staff members. The reason for using the Web Login field is so that organizations using the web interface have a URL to limit requests to the person presently logged in. Again, some organization may not use the web interface or the Pocket PC applet, but I have don't certain things in designing the database to accommodate those who will use all 3 components. I chose not to have a separate table for Customers and a separate table for Staff since I only use one table to hold the fields for logging in to the web interface. More on that later.
The last two fields in the Mcontacts table is the Completed Date for the data the contact or request has been finished, and a Notes field for any additional comments you want to record about a Contact with a Person.
The screen above shows the MPeople table and as with the other tables the first field is the OID field used for Pocket PC synchronization. Next is an auto generated number field. This field is related to Contacts for the web interface and matches up with the LoginID field. The PersonType field I mentioned earlier is used to for filtering either Customers or Staff. The default value is Customer. The Full Name field is required and must be unique for each person record. The optional Company field is looked up from the MCompany table. You may also want to use the email address, phone number, fax, mailing address, city, state, and zip code fields, but these are also optional. If you will be using the web interface you will want to set up each staff member and customer with a Web Login, Web Password and Security level field value. The web login must be unique. This field is important because it is used to populate drop down list boxes for both the windows and web application for the Taken By, Assigned To and Completed By fields. When the people table is used to select a staff web login name, a sql statement filters the table to show only people with a person type of "Staff". When the people table is used to select a customer, the sql statement filters the table to show only those with a person type of "Customer". The last field in this table is to let you record any notes about a person.
I have also included an MEvents table. Some may find this useful.
The web page interface includes a Calendar page. This may be useful if you want to display events such as holidays or days when your office may be close, or when a server is being maintained and therefore unavailable and so on. As with other tables, the first field is the OID field used for syncing with a Pocket PC. The ID field is just an auto numbering field. The Event Date is the data of the event and the Event Details field is a text field 75 characters long.