Showing Sections in Orders

[Mark NGN]

Hi,

I have a very serious problem. I have a cleint with 7000 products on their database and we cannot identify which section the products ordered are from.

For example, the site has similar named products in many different sections. When we dowload the orders, it is not clear which section the products relate to.

Does anyone know how to show the section name in the order (packing list)?

Kindest regards

Mark

[NormanRouxel]

Here's one possible way.

If you go to View / Business Settings / Options you can Enable Extended Product Description for Customer Reports. This adds a new tab "Report Description" to each products Advanced settings. Anything you put in there will appear on the printed reports. You can type the section information (or whatever you like) in there but as you've 7000 items that won't be much fun.

You should be able to automate this by using Access to modify ActinicCatalog.mdb. Back it up first. I'm not an Access guru so you'll have to find one (perhaps someone else here can help) but here's what's needed.

For each record in the "Product" table use the value in nParentSectionID to look up the matching field nSectionID in table "Catalog section". From that "Catalog section" record take the value of SectionText and put it into field sReportDescription in the Product table. This will give you the final section name and not the entire list. If you need the entire list you'll have to repeatedly use the field in "Catalog Section" nParentSectionID to lookup the preceding section names (0 means no parent - you're at the top level).

Assuming this is made up as a query all you need to do is run it whenever you add / move products.

Norman

[HowardW]

Hi
This is a simple update query.
Open the Actinic database in Access.
Go to the Query tab and click on "create query in design view".
Select the two tables: Product and catalog section and click OK
find the field nSectionID in each table and link them by dragging from one to the other.
Click on Query in the menu and select update Query.
In the column of the query table select Product in the table cell and sReportDescription in the field cell.
On the update field type in [Catalog section].[section text]
This is all that is required. go to Query in the menu and click on run query and you get the usual warning about the number of lines being changed.
Every product will then have the section name in the approptiate field. - if a section has no products then that section is ignored.
Save the query, as you can use it every time you update your site.
Regards
Howard

[NormanRouxel]

Many thanks for contributing Howard. My brain's full and has no more room for Access.

Is there a way to deal with the parent section problem. The Catalog section has an entry for nParentSectionID which is either 0 (no parent) or refers to another nSectionID entry in the same table. The idea would be to concatenate all such parent entries with the top level one appearing first.

Norman

[HowardW]

Hi Norman
Yes it is possible but the easiest way is as above - making sure that the section name clearly identifies the product as this is a one step procedure.
to do what you want means first of all constructing a table identifying the hierarchy of the site - this would need several queries (and several tables).
Basically if you start by loading the catalog section table and drag the field nParentSectionID to the first column field cell and nSectionID to the second column cell. Set the criteria for nParentSectionID to 0 (for parent) and click on the Sigma button (to group by) and then set query to make table (name table as level 1) and then run query - you end up with a table with all 0's in column 1 and the id's of each child section in column 2.
The next step is to use this table to perform another query - load it and link column 2 to the nParentSectionID field in Catalog Section. Here things get more interesting as you must change the link to to use all the records from table 1 and only those that match from nParentSectionID. you enter the first fields from table 1 in the field cells on columns 1 and 2 and nsectionID from Catalog table in the field cell for column 3 - you need to use the renaming fields facility in Access to ensure that there is no conflict. Hit the Sigma button and make table, run and you have a table with the structure to 3 levels. repeat this procedure until you have n-1 tables which if combined would have the whole structure. - construct an empty table with sufficient columns to hold all the levels+one more column and append each table - but here's the kicker - the last column of each table must go into the last 2 columns of the new table, the penultimate into the next column to the left etc. thus you have the hierarchy plus an ID column.
now you update each column except the last (ID) column with the section name from the catalog table. 0 can be updated to "Home" if necessary. There is a way of joining up the words into a string with separators in Access - and you end up with a table with an ID column and the concatenation you asked for - sound long winded but in fact it can all be automated.
As I said - it is easier if the section Name is OK.
However, Norman - is it possible to use the "breadcrumb trail" to do this? Over to you.

[NormanRouxel]

Norman - is it possible to use the "breadcrumb trail" to do this? Over to you.

Sorry - I don't understand "breadcrumb trail".

Norman

p.s. Mark, Sorry we're having a chat in your topic. Mine and Howards first post here is probably the easiest way to go.

[HowardW]

Hi Norman
I meant the series of links displayed at the top of the page allowing you to navigate back up the levels (parent section links)
Some people refer ot it as the breadcrumb trail!
Howard

[NormanRouxel]

Ahah! I'd seen "breadcrumb trail" in other posts but never knew what it meant.

Hmmm... Getting that onto the Order pages could be done but would involve some Perl patching and that's too much like hard labour for me.

Norman

[Mike Hughes]

Not the answer you're looking for, but most people would use the product code to identify the right product.

If you want to have a section reference in the product code, why not just use a shorthand code. If you have your database in excel it shouldn't be too difficult to automatically add this to whatever product code you already have.

Mike