Introduction

mOrders is a simple relational database designed to help you download and organize your orders placed through shopping cart accounts(s) at Mal's e-commerce. Some of the key features are:

  • Multiple user accounts at Mal's e-commerce
  • Download order data directly from Admin without having to login
  • Download payment data too if it's a Premium account
  • Export selected orders in a number of formats including MS Excell, MS Word and HTML.
  • Built-in sophisticated report designer
  • Customize you data views

The primary function of mOrders therefore is to act as secure communications client. What mOrders isn't is a substitute for a sophisticated Customer Relations Manager. If you need a lot of control over your order records then you will need to set-up you own systems. mOrders has good export facilities for this reason.

First you need to go through a few post installation steps. Specifically you need to tell the Admin server about your copy and register to it any usernames/shopping cart accounts you want to let it to have access to.


Post installation

Before you can start to use mOrders you need to register it. This is so that the Admin server at Mal's e-commerce knows about your copy of the program and can verify it's authenticity when your try to download orders. This part of the registration process is free so do it immediately!

  1. Open the Settings window of mOrders.
  2. Click on the Register tab
  3. Click on the Regster hyperlink. This launches your default browser and goes to the registration webpage at Mal's e-commerce. You need to have a connection to the internet...
  4. You will be issued with a registration number, enter it into mOrders and click on Update. You will receive a warning which you can ignore.

Now you can enter one or more shopping account accounts to mOrders.

Note that you cannot create new shopping cart accounts in mOrders. To set-up a new shopping cart account go to the main website at http://www.mals-e.com and select the Join link. Joining is free!


Registering a username

Registering a shopping cart account

You can't create a new shopping cart account in mOrders. You can only register one that you have already set-up at http://www.mals-e.com . Furthermore, to download orders you have to have the Secure Pasword for it. You are invited to create it the very first time you go to Payments in the online Admin.

Registering a shopping cart account is in two parts;

1. Enter your username/account details in mOrders

You can enter as many username/shopping cart accounts into mOrders as you like.

  1. Open the Settings window in mOrders
  2. Click on the + symbol above the database grid.
  3. Enter the username, a description for the account and it's passwords. It doesn't matter what you enter as the store's description, it's just used to help you within mOrders. mOrders needs the Secure password in particular.
  4. Click on the tick symbol to update the record.
2. Register the username to connect to Admin
  1. Open the Settings window in mOrders.
  2. Click on the Register tab at the top
  3. Select the username you wish to register from the drop own list (see above section)
  4. Click on the Register button
  5. Click on the Click here and your default browser will launch. You must be connected to the internet.
  6. Follow the online instructions and you will be issued with a Registration code. It will also be confirmed by email
  7. Enter the code in the Registration code text box and click OK.

You will be asked to enter your credit card details, to use mOrders is not free to use!


mOrders' database structure

The database structure is not complex but really needs to be understood in order to create Master (Orders) / Detail (Items) reports and for exporting.

The Orders table is linked to the Items table on the ShopperID .

  • The Orders table contains all the details about an order including the customer's name address and shipping address. You can view all the fields in the panel on the right hand side of mOrders.
  • The Items table is much smaller contains a record of each of the products the customer has ordered. You can view the fields in the grid at the bottom of mOrders.

One order may have many Item records associated with it (hopefully!)


Grouping orders

You can group on any number of columns by dragging the column header into the panel at the top. In the example below we are grouping by Username and then Date. Grouping by Date and then Username has quite a different effect! Try it.

Click on the + sign to expand a particular group and view the individual records.

Grouping has no impact on the selection and so doesn't effect the records which will used for Export or Reports.

To clear the grouping

  • Simply drag the column name out of the panel at the top and off the screen. It will be automatically restored to the main view.


Selecting orders

You can build up a selection based on multiple columns. If you are searching for an individual record then you can narrow down the number on display very quickly this way. For example; select all the records on a certain date by a certain payment method. Selections are case insensitive.

Both the Reporting and Exporting functions work on the current selection which is displayed at the bottom of the main database desktop view.

is a check box column if you wish to just select a few records for Export or Printing.

To select on a column

  • Click on the arrow [image] symbol in the column name.
  • A drop down list of possible values appears.
    • Select a value or;
    • Click on Custom and define your own selection, Excel style. (See below ).

When a selection is active on a column the arrow by the column name turns blue.

To select on more than one column

  • Simply add further selection criteria in the same way as the first.

To clear a selection

  1. Either select ALL from the drop down selection list
  2. Or click on the X on the left hand side of the of the current selection at the bottom.


Searching for orders

Searching for a record

With Edit mode turned off;

  • Click in the main view on the column on which you wish to search
  • Start typing and mOrders will move to the first record that matches what you have typed so far.

You can cycle through possible matches by holding down [Ctrl] key and hitting the[Enter] key.

Searches are case insensitive; capital and lower case letters are treated the same.


Sorting orders

Sorting records

You can sort records into ascending or descending order on multiple columns.

To sort on a single column

  • Click on the name of the column
  • Click again to change from Ascending to Descending.

To sort on more than one column

  • Click on the next column name while holding down the [shift] key.
  • Continue to hold down the [shift] when changing between Ascending and Descending

To clear a sort on a column

  • Click on the column name while holding down the [Ctrl] key


Editing an order

Editing orders

mOrders has an edit mode for safety! Normally you can't edit records until you select the Edit button in the tool bar. The exception to that are the Flags fields.

The Title bar is dark red when in Edit mode.

When you have finished editing a record click on the tick on the right hand side to post the changes to the database. However changes are also automatically posted when you move to a new record.


Adding an order

Inserting a new order manually

To insert a new order click on the plus buton on the right hand side.

You will need to enter at least the Username and Shopper ID. To do that you must be in Edit mode.

Be carefull when you select a Shopper ID if you make it up. mOrders makes it's choice of what represents a new order for download on the maximum current Shopper ID for that username. It's safer to select a low number, otherwise mOrders may stop downloading for you!


Deleting an order

To delete an order click on the minus button on the right hand side. You will be asked to confirm the action.

Deleted orders cannot be recovered. However, if they are still available in Admin online then you can always re-enter it manually.


Report generator - Introduction

Reports - an overview

mOrders includes a powerful report designer with which you can create you own report templates. But the nature of a relational database means that creating such reports is a little bit more complex. There are three key components in the reporting module:

  • Report Explorer - This is the opening window when you click on the Print button in mOrders. It's where you store your report templates. It has the look and feel of Windows Explorer; you can create, rename and delete folders where you store your templates. Initially there are two Folders; Default reports and My reports (empty).
  • Report Designer - If you double click on a report template it opens in the designer window. This is where you create and change report templates.
  • Report Preview - If you click on the preview button in the Report Explorer you can view the current report and print it.

I've set-up some example reports in the Default Reports folder. I recommend that you use these as the basis of your own reports, at least initially. But "Save as..." something else from within the Report designer BEFORE you make any changes. That way you don't overwrite the original template and loose it.


1. Opening the report designer

1. Creating a new Master/detail report

Sometimes a report needs to include details from both the Master (Orders) table and a Detail (Items) database tables. The classic example of this is an invoice which contains at the top the customer details, in the middle a list of items they have purchased and at the bottom a summary. This type of report is the most complicated to set-up.

Open the Report Designer and save the new template:

  • Click on the Print button in mOrders and open the Report Explorer window
  • Double click on the My reports folder
  • Click on the New report button. The Report Designer will open.
  • Select File then Save as... and give the report a name and save it.

Select the main table for the report; the Master table

  • Select Report from the top menu and click on Data
  • Select Orders from the list of tables.
  • Select File from the top menu and Page Setup
  • Choose your Paper Size and set the Margins
  • Click OK
  • You might want to change the Report >> Units to Millimeters!

Create the Header panel

  • Click on Report , main menu, Groups
  • Select Orders: ShopperID from the drop down list of fields
  • Click on Add
  • Check Start new page and Reset page numbers
  • Click on OK

  • Select Report , main menu, and then Header.
  • Put the mouse somewhere over the grey line with "^ Group Header[0]: ShopperID" written on it and drag it down. Make the header panel a bit bigger.

Layout the Header panel

  • Click on the A button on the Tool bar and drop a label into the Header panel.
  • Change it's name in the text box from Label1 to Invoice
  • Click on B Bold button and change the font size to 12.
  • Select Navy to make it blue.

  • Click on the DBText button and drop a DBText item on the header panel.
  • Select Invoice_name from the drop down list to the right of Orders.
  • Drag the right hand side of the DBText item's box across to make it a bit bigger.
  • Add Invoice_company, Invoice_address1, Invoice_address2, Invoice_state, Invoice_zip and Invoice_country in the same way.
  • Click on each item and drag it over to where you want it to be.
  • Increase the size of some of the fields that need more space by dragging the right hand edge of the field box.
  • You might find that with View >> Grid options >> Snap to grid checked will help you position them.

You should now have something like this:

Add the date and Invoice number

  • Add two more labels; Date and Invoice number on the right hand side.
  • Add two more DBText items beside them; Date and ShopperID
  • That date format doesn't make any sense to you! Right click with the mouse in the date box
  • Select DisplayFormat
  • Select d-mmm-yy from the list:

Your header panel is now finished.

  • Click on the Preview tab at the top and with a bit of luck you will see something like this:

Oh, yes, I made the Date and Invoice labels Bold...

And now to the next lesson.


2. Adding the detail - Items

Adding the detail lines

Each product that the customer orders is held in a separate table (Items). These are details records because there may be more than one of them, hopefully.

The detail panel

Below the top Group panel there is another band called Detail. This is where the products the customer has ordered will go.

  • Click on the [image] Sub Report icon and then inside the detail panel.
  • A box will appear inside the detail band called "SubReport1: No Data Pipeline Assigned"
  • Click on the tab of the same name at the bottom of the Report Designer.

You will now be looking at the Report Designer window for the Sub report.

Layout the Sub Report

  • Remove the footer band, we aren't going to use it:
    • Report >> Footer
  • Revove the Header band:
    • Report >> Header
  • Add a Title band if it isn't already there:
    • Report >> Title
  • Select the Data table it uses:
    • Report >> Data
    • The Data pipeline window opens, select Items.
    • Click OK
    • Note that the tab at the bottom has now changed to "SubReport1: Items"
  • Drop a DBText field into the Detail band.
  • Select Qty from the drop down list in the tool bar
  • Align center
  • Add Fields for Product, Price and Amount in the same way.
  • You should be looking at something like this:

Format the fields

The two fields, Price an Amount need to be formatted correctly.

  • Right click inside the field box
  • Select DisplayFormat
  • Select a suitable format from the list. I prefer not to use a currency format but it's up to you.
  • Allign both fields to the Right: Click on the appropriate tab in the Tool bar.

Add the header to the Sub Report

  • Click in the Line button in the tool bar.
  • Drop the line into the Header panel and expand it, by dragging the edges to cross the entire page.
  • Add for labels above the line for the four columns below it: Quantity, Description, Price and Amount.
  • You should be looking at something like this:

You can now click on the Preview Tab at the top and see something much more interesting! You can cycle though the pages, one page per order/invoice by clicking on the arrow keys in the tool bar.

All we have to do now is finish do the footer... Next lesson.


Finishing - the footer

3. Adding the footer to the invoice

The invoice footer contains the summary of the amounts; Discount, Shipping, Tax and Total. In mOrders these are stored fields, they aren't calculated so it's easy. We place the footer in the "Group Footer[0]: ShopperID" band. You may need to drag it down a bit so that you have a working area. Remove the page footer band too; Report >> Footer.

You should be looking at this:

Adding the summary fields

  • Click on the A button in the Tool bar
  • Click in the Group band
  • Rename it to "Less Discount"
  • Click on the DBText field in the tool bar
  • Click in the Group band
  • Attach it to the Discount field:
    • Select discount from the drop down list of fields in the tool bar
  • Repeat for Tax, Shipping and Total
  • Format the fields:
    • Right click in each one
    • Select DisplayFormat
    • Select a suitable format from the pop-up window
  • Add two lines;
    • One right across the top of the Group Footer panel
    • One above the TOTAL.

Click on the Preview tab at the top. You may find that you need to line up the footer with the Items in the sub Report. Use the ruler along the top to do that as you switch between the two (the tabs at the bottom of the Report Designer window.) You can select a whole group of items in the Designer using the mouse, drag across and down while holding the right mouse button down. Then move them all together.

There is a problem with this report though, we really want the footer to appear in the same place at the bottom of each report.

  • Drag down the Grey Details bar.
  • Drag down the outlined SubReport1: Items box
  • Right click in that box
  • Select Fixed

Finishing off

There is quite a bit more you could do to your report but it's really just a case of dropping more items from the tool bar into either the Header or Footer panels. You can:

  • Add an image, a logo
  • Add more of the database fields; Shipping or the Message field
  • Add a printing date from the System Variable button.


1. Grouping orders

1. Creating a grouped report - Daily summary

In the main data view in mOrders you can group orders by draging the column name into the panel at the top. In this tutorial we will group orders by the date they are placed to create a summary of each days orders and how much they are worth.

Create a new report

  • Open the Report Explorer by clicking on the Print button in mOrders
  • Click on the New report button in the tool bar. The Report Designer windo will open.
  • Click File >> Save and save the report with a new name.

Select the data and group

The report uses just the Orders database table.

  • Click on Report >> Data
  • Select Orders from the Data window

The report will be grouped by the date the order is placed. That is that we want all the orders for each date to appear together.

  • Click on Report , top menu
  • Click on Groups
  • Select Date from the list of fields at the top of the Groups window
  • Click on Add .
  • Click OK .

Layout the Header panel

  • Add a Label by clicking on A tool and drop into the Header panel
  • In the text box in the tool bar change it to Daily orders report
  • Add a second label called Printing date .
  • Select System variable and drop it in beside the Printing date label.
  • Right click inside the variables box and select DisplayFormat
  • Choose a suitable format for it; dd-mmm-yy

Your header panel should look like this:

Note: I've applied aome addition formatting to the reports title; made it Navy and Bold. Do that using the tool bars the same way as you would in a wordproccessor such as MS Word.


Adding the group header

2. Adding the group header

The report has five panels, from top to bottom they are called

  1. Header
  2. Group Header[0]: Date
  3. Detail
  4. Group Footer[0]: Date
  5. Footer

As the template is printed it place the first and the last of these, Header and Footer on each page. Within each group the Group Header is printed, then the Details of each order followed by the summary for the group in the Group Footer. It will look like this:

Layout the Group Header

You may need to drag down the Group Header[0]: Date panel in order to see it's area if it is squashed up against the Header panel. Thene place one label, a line and DBtext field in it:

  • Rename the Label; Orders for
  • Connect the DBText field to the database's Date field

Layout the Detail section

Add five DBText fields into the Detail panel and link them to:

  1. Username
  2. ShopperID
  3. Invoice_name
  4. Payment_method
  5. Total

You will want to set the DisplayFormat, right click inside it, to a sensible currency format for the Total. Also Right align a currency field like this.

At this point you can click on the Preview tab at the top and you should see a pretty sensible report. All we need to do now is add the summary for each days transactions.


3. Calculating the summary totals

We want to display the turnover for each day's sales in the Group Footer panel. This is actually very easy!

  • Drop a [image] DBCalc field into the Group Footer[0]: Date panel.
  • Right click on it and select Calculations
  • Select Sum from the Calc Type list
  • Click OK
  • Link it to Total by selecting that field from the list in the main tool bar

If you like you can also add other DBCalc fields into the summary. Perhaps a count(*) of the number of records (sales) in the group and an average sale.

To finish off, add page numbers

  • Select [image] System Variable from the tool bar
  • Place it in the Footer panel
  • Select PageCount from the drop down list in the tool bar


Restoring the defualt reports

Restoring the Default reports

There are several report templates stored in the Default Reports folder of the Report Explorer. They are not write protected. If you open one in the Designer, make a mess of it and then save without changing the name your mess will be saved! However you can still restore them:

  • Double click on the report in the Report Explorer. The Report Designer will open.
  • Click on File
  • Click on Load From File...
  • Browse to mOrders working directory, normally
    • C:\Program Files\Mals ecommerce\mOrders
  • Select the .rtm file named the same are the report you wish to restore
  • Click Open

To save it:

  • Click on File
  • Click on Save As...
  • Save to the Default Folder with the name of the original template


Adding and removing columns

Adding or Removing columns from the main view

In the default desktop set-up only a selection of the available columns are visible. In other words; only some of the fields you see in the right hand panel of mOrders can e seen as columns in main view. Furthermore there are two extra Bands of columns available. A band is a group of columns and is the row highlighted in green with Edit mode off.

To add a column

  • Click on the customize button
  • The pop up window shows a list Bands and Columns that aren't visible.
  • Drag a Band or Column to the position where you would like it to be in the main view
  • Close the Customize pop-up window by clicking on the X symbol in the top right corner of it.

Note: You can only position a column within it's own band. For example the order's Tax field can only be placed in the Amounts band....

To remove a column or band

  • Simply drag the column or band name off the screen
  • It will be automatically added back into the Customize pop-up list of available Bands and Columns.


Saving your settings

Many settings in mOrders are saved automatically. However, the main database view is very customizable so in that case you may wish to save more than one set-up of it. In mOrders this is called the data Desktop view. You can switch between your own saved settings by selecting a different desktop from the drop down list in the tool bar.

You can save up to four of your own views on the data desktop.

Saving the desktop view

When you have configured the desktop as you like it you can:

  • Select the desktop you want to rename from the drop down list and make it current
  • Click on the desktop button
  • Select Save desktop
  • Your settings will be saved.

Note: you cannot save over or replace the < Default > desktop!

Most settings are saved when you do this; groups, bands and column headers are all retained. Selections are not.

Renaming the saved Desktop

  • Select the desktop you want to rename from the drop down list and make it current
  • Click the desktop button
  • Click on Rename Desktop
  • Type in a new name in the pop-up window
  • Click on OK


Resizing columns

Resizing a column or band

You can resize either a column or a whole band of columns by dragging the edge the box around it's name.

mOrders will resize the other columns in the band in order to fit it's current size. So you may find you need to play around with it a bit...


Using more than one row per order

Using more than one row per column

If you are a bit short real estate on your screen you can use more than one row per record in the main view.

  • Place the cursor of the mouse over the bottom of the column header row (in grey) and drag it down to add one or more rows per record.
  • Move the individual columns by dragging their header to where you want them.


Export Wizard

You can export the currently selected records in the main database using the Export wizard.

Available formats
  • MS Excel - You don't have to have Excel installed on you machine.
  • MS Word - You don't have to have Word installed on you machine.
  • HTML
  • RTF
  • Text file
  • SQL
  • Windows clipboard
  • XML
  • CSV - Comma Seperated Value. A plain text file, one order per line, where each column is delimited by a comma

Proably the most useful of these is the Excel file format. Even if you don't want to use the data in Excell it is a file format that is compatible with many applications.


Export wizard

Export wizard - select the database table

mOrders is a relational database. What that means is that each order is related to many (hopefully) products ordered, the items that people want. This is sometimes referred to as a one to many relationship or Parent > Child.

In practice it means that mOrders has two database tables and they linked to each other by the Order's Shopper or Customer ID. The first question you are asked by the Export Wizard is which if these tables you want to export. To do a "full" export of all the data associated with an order you would need to export both, one at a time.


Changing an existing report

Changing an existing report template

  • In the Report Explorer, double click on the report you want to change. It will open in the Report Designer.
  • Before you make any changes it would be wise to Save as... a different report. You can always delete the old version latter.


Moving the database files

Relocating the database files

By default the database files are installed to a directory called \data immediately below the program files. Normally that would be:

C:\Programs Files\Mals ecommerce\morders\data

I recommend that you don't move them unless you have a very good reason; perhaps you need them on a network drive or you computer has move than one hard disk.

  1. Close mOrders.
  2. Copy the entire \data directory to it's new location. Copy not move!
  3. Open mOrders and go to Settings.
  4. Click on the Settings tab
  5. Open the directory tree dialogue in the database location box by clicking on the folder icon in it. Navigate to the new data directory and click OK.
  6. Close mOrders. The next time you open mOrders it will be using the files located in the new location. You can safely delete the old \data directory now or, better still, back it up to somewhere safe.


Restricting access to Settings

Restricting access to the Setting window

It's inside the Settings window that all your most confidential and secure information is stored. For example, it's the only place where you can see your account's passwords. In addition, some the settings themselves are dangerous, for example moving the database directory. For these reasons you can disable access to the Settings window from within mOrders. Of course, once you've done that you won't be able change anything. However there is a solution to that problem as long as you know your mOrders Registration number.

To disable access to the Settings window: