VbzCart/archive/catalog building 2006

Navbar

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.

qryVb_Make_Items_Avail:
SELECT * INTO [Calc Items Avail] FROM qryFig_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;

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?

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
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 7

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

qryVb_Update_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

qryItems_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.

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;

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.