Kitchen Example - Your Catalogue

Transcript

There are a number of different methods you can use for importing data from outside sources into QuSheet. In the case of the kitchen example I lifted my price and parts data straight across from the Excel tables you can see here:

- the worktops are in this matrix of prices here

- as are my sinks

- my cookers are in these two lists

- and so are my fridges

Not a lot of data, I know, but it took literally seconds to import across.

You data doesn�t have to come from Excel, of course � anything where you cut and paste a matrix of values like this, where the first line and first column are headings, will do perfectly well.

If you want to do something more sophisticated and more automated you can go for an XML based solution, like this one here which again I�m demonstrating with Excel but which could be with any sort of XML based system you happen to have. This table is a price table so the value is basically the price and the next column is the name of the appliance with the different components of the name separated by a SPACE COMMA SPACE (you can make this separator whatever you like, by the way). Next to it is the field definition which basically cross-references with the price table so it needs to be the same as this column here.

With Excel you can add more entries at the end like this (remembering your separator, and remembering to put an entry in both the price table and the field).

Then you can do an export, go over to QuSheet, from the File Menu do an Import Replace XML data, and that�s it � if I go over to my Sheet / Table Addition now I�ve got myself a new sink.

I�ll talk a bit more about this XML process later.

First of all let me introduce a couple of QuSheet concepts � Sheets, the Workbook, Fields and Tables.

A QuSheet project � which is basically the file you open from here, and which has the DOT QUS DOT XML format � has one workbook and any number of sheets. The Kitchen example has two sheets, to make it consistent with other examples on the web site, but where the only difference between them is in the setting of this small-screen based generation parameter over here. If you�re interested in small-screen output take a look at the schedule presentation from the Examples web page here as that is much more geared towards this particular feature.

Anyway, QuSheet basically stores data using Tables and Fields. These are defined and populated within the Workbook area, though individual Sheets can add to these using the +Table Values and +Field Values sub-tabs. In order to separate out the user who produces a customer quotation from the administrator who manages your price catalogue I have put all the user�s table additions in the +Table Values page here for the Sheet. This also allows you, if you like, to keep track of more than one quotation within the project by Cloning a Sheet like this and then filling in new values in here. You have to Clone, incidentally, rather than do a New, because you need to keep your Sheet�s Entries if your sheet�s going to show anything at all (you can see the five output areas here). Keeping multiple quotes on one project will make your project file grow and grow, but it is a handy thing to do it you want to re-generate quotations later on when you make a price change. In fact, the Sheet List tab over here provides you with an easy way of doing this.

But anyway, for now I�m going to get rid of my new Sheet, just by using the Undo button a few times, and just work with one Sheet (this Quote one here).

Ok, so like I said, a Workbook defines Tables and Fields, which a Sheet can then add to. Tables and Fields appear in the separate Tables and Fields sub-tabs and you can use this drop down list over here to navigate to the right one. The Tables you are going to want to modify here are the various �Price Table� ones, and the fields will be their corresponding fields.

Fields, as you can see, appear as columns in Tables, and you may be wondering what tells QuSheet what Fields to display for what Tables. Any Table can actually hold a value for any Field, and what QuSheet actually does is display those Fields which have some value defined (either here or as a Sheet addition). Since, for example, the Price Table for Accessories doesn�t have anything defined in the Worktop field (and it shouldn�t), the worktop field doesn�t appear. The View menu, however, lets you change this if you like.

Note that columns are resizeable, and moveable. In fact, as you�ll see in a minute, we�ll need to move the column with the name over to the left hand side. If your table�s get a little scrappy you can right click on a column header and select Fit Width to tidy it up a bit.

Ok, so tables are just matrices of data with at least one column in (the Value of the table) and a number of extra columns depending on the Fields being used, and you will see that the 5 Price tables each use a different field to define the item that you are providing a price for. This item is specified using a line of text which is separated out with this SPACE COMMA SPACE here and you may be wondering whether you need to format up all of your data using SPACE COMMA SPACE or some other separator. In fact, you don�t � at least, you don�t have to. If you remember back here in the Excel spreadsheet I just had tables of data in without any separators � QuSheet does have a facility where it will put in this separator for you.

I will demonstrate this by adding in some dishwashers.

Over here on this Excel sheet I have a table of dishwasher prices. First of all, I will select this table, including the header line and column, and COPY. Note that Hotpoint (in this example, not necessarily in real life), hasn�t got a slimline option.

Next I will go over to QuSheet, go to Tables under Workbook, select the Price Table for Appliances, and move the Appliance field over to the left. You�ll see why I need to do this in a second. I�ll also make it a little bigger and tidy the other one up.

I will then do an Edit / Paste Add lines (and note the ADD) from Matrix. This will pop up quite a chatty little dialog box which tells me exactly what I�m going to do here � but in a nutshell this specialised Paste function will take my matrix of data (with the column and row header) and translate it into a two column matrix (which is suitable for what I want here) with the first column having a name and the second having the value. You�ll see what I mean if I just do it � I�ll put in a prefix of Dishwasher, make it rows then columns and ignore any empty cells, and here we go � our price table now has the 5 Dishwashers in it, with the names separated out with a SPACE COMMA SPACE. The reason they�re in grey is because I haven�t added these new values to my field yet so, without further ado, I�ll go over to the FIELD area and do the same thing (note that with the field, the column of prices is ignored). So now if I go back to my table you can see that the dishwashers are no longer grey, and if I go back to me Sheet / +Table Values I can now add dishwashers using the drop down list.

This SPACE COMMA SPACE separator, by the way, is defined under Preferences / General at the bottom here. Do be warned, though - changing this wont change your data, it�ll just change the way your data is interpreted by the LIST option I�ve just shown you and the way the Matrix Pasting function works. You�ll probably want to decide what you�re going to use here right at the beginning and then stick to it.

Of course, if you want to manage these separators yourself, you don�t have to use this Matrix Pasting function at all. These tables are all editable, like this, and there are some more straight forward copy and paste operations here as well. Copy and Paste works on the basis of what is visible and the order that it�s visible in, by the way � like this, you see.

And finally there�s the XML option � well, two XML options, actually.

QuSheet�s project file is, itself, entirely XML. Not the easiest XML in the world to work with since it has to handle the generic nature of the tool itself (i.e. you wont find tables called Accessory, for example). If you want to look at the Schema Definition file for a QuSheet project there�s an entry under Help over here on the right hand side which will let you do that.

The second XML option is the one I briefly touched upon earlier, and it basically works by QuSheet generating a Schema Definition file which reflects the project file you�ve currently defined. This schema definition file DOES have entries called Accessory or whatever � basically reflecting the tables and fields defined within the project. The Schema Definition AND the Data file are both exported from the File / Menu entry here (and note the little message box telling you the name of the files and where they�ve been put).

Excel can work with this quite well � in fact, I already have the schema definition defined as an XML source over here. Once I have done that and dragged and dropped the tables I�m interested in onto an XML sheet like this � I can just do an import to get the data I want

- and then make changes and re-export like I did before (and I shant do it again cos you�ve seen that).

Ok. Hopefully that�s told you everything you need to know to get your parts and price catalogue into QuSheet. In the next presentation I shall be telling you about how all the remaining simple changes you can make like styling, tax rates and what have you.