VbzCart/docs/archive/catalog building 2006

Template:Hide

Template:SidebarVbzCart: catalog building

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.

We really just want [Calc Items Avail].ID_Item to be all NULLs, but if you do that it gets created as a text field rather than an integer (text must be a default, when there's no restraint on the type), so we first assign it all zeros and then change the zeros to NULLs with qryVb_ItemsNeeded_PreClean. (There's probably a way to force the type on MySQL, but there doesn't seem to be one in Access.)

• qryVb_Make_Items_Avail:

  SELECT * INTO [Calc Items Avail] FROM qryVb_Fig_Items;

• qryVb_Fig_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;

• qryVb_ItemsNeeded_PreClean:

  UPDATE [Calc Items Avail] SET ID_Item = NULL;

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.

• qryVb_CkDups_Items_Avail

  SELECT CatNum, Count(ID_Title) AS Count

  FROM [Calc Items Avail]

  GROUP BY CatNum

  HAVING Count(ID_Title)>1;

Phase II

Changes are committed in this phase.

Step 3

qryVb_Items_ResetActive prepares the Items table for update:

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

Step 4

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

• qryVb_Update_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 5

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

• qryVb_Update_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 6

In this step, the list of "new" items in qryVb_ItemsCalc_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?

This also seems to be a broken bit, because if you disable creating new items but allow recycling, there are always leftover recyclables and leftover new items to save.

• qryVb_ItemsCalc_Unassigned:

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

• qryVb_ItemsCalc_Unassigned:

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

• qryVb_Update_Items_Recycled:

  UPDATE [Calc Items Avail] AS ia LEFT JOIN

  Items AS i

  ON ia.ID_Item=i.ID

  SET

  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 7

qryVb_Append_Items_New creates new Items records for any new items not yet taken care of.

• qryVb_Append_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;

Phase III

This is just error-checking, to make sure everything got taken care of.

Step 8

qryVb_Items_Avail_NeedNew contains any catalog numbers which do not already exist in the Items table, as an error check. We know something about the process is broken because it is coming up non-empty.

• qryVb_Items_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;

The choice of i.CatNum as a filter is somewhat arbitrary; the point is to find records in [Calc Items Avail] with no matching records in Items.

The resulting recordset does not include items which exist but need updating; it is only items which do not appear to have been created in Items yet, and thus need new or recycled Items records.