VbzCart/docs/archive/catalog building 2006

The Process

This was recently revised to use mostly SQL queries to do the necessary data manipulation; it had been done in code before. SQL is much faster, but seems to require larger chunks of thinking in order to maintain successfully, in part because you can't step through it.

As it is now, parts of the building process are broken. Here's what happens:

Phase I

Step 1

A query (qryMake_Items_Avail) builds a (temporary) table of items which are known to be available based on supplier catalog data. qryMake_Items_Avail gets data from qryFig_Items and uses it to create [Calc Items Avail] without further processing.

• qryMake_Items_Avail:

  SELECT * INTO [Calc Items Avail] FROM qryFig_Items;

• qryFig_Items:

  SELECT

  t.ID AS ID_Title,

  NULL AS ID_Item,

  t.CatNum AS CatNum_Title,

  g.Code AS GroupCode,

  gt.GroupCode AS GroupTitleCode,

  o.CatKey AS OptionCode,

  it.Code AS ItemTypeCode,

  g.Sort & it.Sort & o.Sort AS ITypOptSort,

  t.CatNum&BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatNum,

  g.Name AS GroupName,

  gi.Descr AS GrpItemDescr,

  Cost AS PriceCost,

  PriceOurs AS PriceCust,

  PriceList,

  ID_ShipCode,

  ID_ItemType,

  ID_Option,

  gt.Supplier_CatNum AS Supplier_CatNum_Group,

  iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,

  gt.GroupDescr AS GroupTitleDescr,

  o.Descr AS OptionDescr,

  c.Abbr AS CatAbbr,

  c.DateAvail AS CatDateAvail

  FROM ((((

  (qryCatNum_Titles AS t LEFT JOIN [TGroup Titles] AS gt ON gt.ID_Title=t.ID)

  LEFT JOIN TGroups AS g ON g.ID=gt.ID_TGroup)

  LEFT JOIN qryCatalogs_Active AS c ON gt.ID_Catalog=c.ID)

  LEFT JOIN [TGroup Items] AS gi ON gi.ID_TGroup=g.ID)

  LEFT JOIN Options AS o ON gi.ID_Option=o.ID)

  LEFT JOIN [Item Types] AS it ON gi.ID_ItemType=it.ID

  WHERE (c.ID IS NOT NULL) AND g.Active AND gt.Active AND gi.Active AND (o.Active or (nz(ID_Option)=0))

  ORDER BY t.CatNum, g.Sort, o.Sort;

At this point, we have catalog numbers for everything but we don't know which records in Items these should be used to update, nor which ones are new items.

Step 2

qryCkDups_Items_Avail contains any catalog numbers which have been duplicated. If there are any, processing stops at this point because further data massaging/entry is needed.

• qryCkDups_Items_Avail

  SELECT CatNum, Count(ID_Title) AS Count

  FROM [Calc Items Avail]

  GROUP BY CatNum

  HAVING Count(ID_Title)>1;

Step 3

qryItems_Avail_NeedNew contains any catalog numbers which have not already been entered into the Items table. If it is empty, then there are no new items to deal with, so we can skip

• qryItems_Avail_NeedNew

  SELECT ia.CatNum

  FROM [Calc Items Avail] AS ia

  LEFT JOIN Items AS i ON ia.CatNum=i.CatNum

  WHERE i.CatNum IS NULL;

Phase II

Changes are committed in this phase.

Step 4

An unnamed query (run directly from code) prepares the Items table for update:

• UPDATE Items SET Active=False, InPrint=False, Sources=NULL, WhenLastInPrint=NULL WHERE Active

Step 5

qryUpdate_Items_Existing updates records in Items whose catalog numbers match items in [Calc Items Avail]:

• qryUpdate_Items_Existing:

  UPDATE [Calc Items Avail] AS ia

  LEFT JOIN Items AS i ON ia.CatNum=i.CatNum

  SET

  ia.ID_Item = i.ID,

  i.InPrint = TRUE,

  i.Active = TRUE,

  i.WhenLastSourced = Now(),

  i.ID_Option = ia.ID_Option,

  i.OptCode = ia.OptionCode,

  i.OptDescr = ia.GrpItemDescr,

  i.ID_ItemType = ia.ID_ItemType,

  i.ITypDescr = ia.ITypeDescr,

  i.ITypOptCode = OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/"),

  i.ITypOptDescr = OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / "), " / "),

  i.ITypOptSort = ia.ITypOptSort,

  i.ID_ShipCode = ia.ID_ShipCode,

  i.Cost = ia.PriceCost,

  i.PriceOurs = ia.PriceCust,

  i.PriceList = ia.PriceList,

  i.Sources = i.Sources&"."&ia.CatAbbr,

  i.WhenLastInPrint = ia.CatDateAvail,

  i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group

  WHERE i.CatNum IS NOT NULL;

Step 6

qryUpdate_Items_Stock also activates any items not already activated in Step 5, and also updates a few related fields.

• qryUpdate_Items_Stock:

  UPDATE (

  Items AS i

  LEFT JOIN Stock AS s ON s.ID_Item=i.ID)

  LEFT JOIN Locations AS l ON s.ID_Location=l.ID

  SET i.Active = TRUE, i.WhenLastStocked = Now(), i.Sources = i.Sources&".stock"

  WHERE (s.WhenRemoved IS NULL) AND (l.WhenVoided IS NULL) AND l.isSellable;

Step 7

In this step, the list of "new" items in qryItemsCalc_Unassigned is checked against the list of recyclable items in qryItems_Recyclable. Recyclable items are allocated to new items by setting the ID_Item field in qryItemsCalc_Unassigned equal to the ID of each recyclable item allocated. The iteration for this is done in code, as I wasn't able to figure out any way to do it in SQL. After recyclables have been allocated to new items, qryUpdate_Items_Recycled does the full updating of each reallocated recyclable record.

Note: bothering to recycle records in the first place is probably symptomatic of bad design in some way, but it bothers me to just delete records and leave huge holes in the numbering sequence. Huge holes make me think that something has been eating data (which has happened in the past), so I prefer not to have them. Perhaps this obsessive recycling does serve as a kind of minor data-integrity check?

• qryItemsCalc_Unassigned:

  SELECT * FROM [Calc Items Avail] AS i WHERE i.ID_Item=0;

• qryItemsCalc_Unassigned:

  SELECT * FROM Items AS i WHERE i.Pulled AND i.isFree;

• qryUpdate_Items_Recycled:

  UPDATE [Calc Items Avail] AS ia LEFT JOIN

  Items AS i

  ON ia.ID_Item=i.ID

  SET

  ia.ID_Item = i.ID,

  i.WhenRetitled = Now(),

  i.CatNum = ia.CatNum,

  i.Pulled = FALSE,

  i.isFree = FALSE,

  i.ID_Title = ia.ID_Title,

  i.InPrint = TRUE,

  i.Active = TRUE,

  i.WhenFirstInPrint = iif(i.WhenFirstInPrint IS NULL,Now(),i.WhenFirstInPrint),

  i.WhenLastSourced = Now(),

  i.ID_Option = ia.ID_Option,

  i.OptCode = ia.OptionCode,

  i.OptDescr = ia.GrpItemDescr,

  i.ID_ItemType = ia.ID_ItemType,

  i.ITypDescr = ia.ITypeDescr,

  i.ITypOptCode = OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/"),

  i.ITypOptDescr = OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / "), " / "),

  i.ITypOptSort = ia.ITypOptSort,

  i.ID_ShipCode = ia.ID_ShipCode,

  i.Cost = ia.PriceCost,

  i.PriceOurs = ia.PriceCust,

  i.PriceList = ia.PriceList,

  i.Sources = i.Sources&"."&ia.CatAbbr,

  i.WhenLastInPrint = ia.CatDateAvail,

  i.Supplier_CatNum_Group = ia.Supplier_CatNum_Group

  WHERE i.Pulled AND i.isFree AND (NOT i.Active);

Step 8

qryUpdate_Items_New (actually an append query, not an update query) creates new Items records for any new items not yet taken care of.

• qryUpdate_Items_New:

  INSERT INTO Items ( CatNum, ID_Title, WhenFirstInPrint, WhenCreated, Pulled, isFree, InPrint, Active, WhenLastSourced, ID_Option, OptCode, OptDescr, ID_ItemType, ITypDescr, ITypOptCode, ITypOptDescr, ITypOptSort, ID_ShipCode, Cost, PriceOurs, PriceList, Sources, WhenLastInPrint, Supplier_CatNum_Group )

  SELECT

  ia.CatNum,

  ia.ID_Title,

  ia.CatDateAvail AS WhenFirstInPrint,

  Now() AS WhenCreated,

  False AS Pulled,

  False AS isFree,

  True AS InPrint,

  True AS Active,

  Now() AS WhenLastSourced,

  ia.ID_Option,

  ia.OptionCode AS OptCode,

  ia.GrpItemDescr AS OptDescr,

  ia.ID_ItemType,

  ia.ITypeDescr AS ITypDescr,

  OptionJoin(ia.GroupTitleCode,ia.OptionCode,"/") AS ITypOptCode,

  OptionJoin(ia.ITypeDescr,OptionJoin(ia.GroupTitleDescr,ia.OptionDescr," / ")," / ") AS ITypOptDescr,

  ia.ITypOptSort,

  ia.ID_ShipCode,

  ia.PriceCost AS Cost,

  ia.PriceCust AS PriceOurs,

  ia.PriceList,

  "." & ia.CatAbbr AS Sources,

  ia.CatDateAvail AS WhenLastInPrint,

  ia.Supplier_CatNum_Group

  FROM [Calc Items Avail] AS ia

  WHERE ia.ID_Item IS NULL;

Step 9

qryItems_Avail_NeedNew displays any new items still not taken care of, as an error check. We know the process is broken (although it was working earlier) because there are still items left over.