VbzCart/pieces/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. On the other hand, it forces the process to be cleaner and simpler (believe it or not).

The process is something like this:

Build a temporary dataset ([Calc Items Avail]) consisting of items we know are currently available.
- The rules for deriving this data are a little complicated; there are suppliers, who have catalogs listing what items they have available and with what options; to simplify data entry, we have "title groups" of common groups of options, and a record of which groups are applicable for each catalog item (called a "title"); and so on...
- Each item is only identified by catalog number in the temporary table; we still need to match up each "available" item with a permanent item record in the Items table, or create a new Items record if the item is new.
- We can only tell an item is new by noting that it doesn't match an existing catalog number; there's probably a better way to do this, but some design work is needed.
Check to make sure there are no duplicate catalog numbers, which would mean that the catalog hasn't been entered quite right.
Clear status fields in the (permanent) Items table; we're about to update it.
Update existing items in the Items table from the temporary table of available items
Update Items table from what's in stock (even if an item isn't available from a current catalog, we may have some in stock, which would make it still available but discontinued). Items in stock will always exist in the Items table, else something is seriously broken. (Should probably add a step to verify that all stock items exist in Items.)
Look for (and mark) any "recyclable" Items records we need to use (done in code), then update them from the temporary table ([Calc Items Avail]).
Having used up all the "recyclables", any temporary items still unassigned are new items and will need new records in Items. Create those records.
Do a little error-checking, and we are done.

Phase I

Step 1

A query (qryMake_Items_Avail) creates a table of items ([Calc Items Avail]) 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.

[Calc Items Avail] issues

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.) Resolved by making the table permanent; see below.
The data is generated from scratch each time, but it is a permanent table so that the CatKey field can be an index; there doesn't seem to be any non-klugey way to do this with a temporary table.
Certain fields needed to be unique indexes, but this was impossible because their values were initially blank. Finally arrived at the solution of creating the initial dataset in a temp table ([Calc Items Avail scratch]), filling in all the blank fields, and then copying everything over to the "designed" table ([Calc Items Avail]).

queries

qryVb_Clear_Items_Avail:
DELETE * FROM [Calc Items Avail];
qryVb_Make_Items_Avail:
INSERT INTO [Calc Items Avail] SELECT * FROM qryVb_Fig_Items;
qryVb_Fig_Items:
SELECT
t.ID AS ID_Title,

NULL AS ID_Item,

OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,

OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,

t.CatNum AS CatNum_Title,

BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatExt,

NULL AS CatKey,

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

NULL AS ITypOptDescr_Calc,

g.Code AS GroupCode,

gt.GroupCode AS GroupTitleCode,

o.CatKey AS OptionCode,

it.Code AS ItemTypeCode,

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

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

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

gt.WhenDiscont IS NULL AND

gi.Active AND

(o.Active or (nz(ID_Option)=0))

ORDER BY t.CatNum, g.Sort, o.Sort;
qryVb_ItemsNeeded_Ops calculates one more field which would be ugly SQL if we tried to do it in the previous step:
UPDATE [Calc Items Avail] SET
ITypOptDescr_Calc = OptionJoin(ITypeDescr,IGrpOptDescr_Calc, " / "),

CatNum = CatNum_Title & CatExt,

CatKey = ID_Title & nz(CatExt);
qryVb_ItemsNeeded_PreClean:
UPDATE [Calc Items Avail] SET ID_Item = NULL;

At this point, we have catalog numbers and other identifying data for everything but we don't know which specific 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_CalcFields figures/updates significant calculated fields in the Items table:

UPDATE Items AS i LEFT JOIN qryCatNum_Titles AS t ON i.ID_Title=t.ID SET i.CatKey = ID_Title&CatExt, i.CatNum = t.CatNum&i.CatExt;

qryVb_Items_ResetActive resets flags and timestamps in the Items table:

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

Step 4

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

qryVb_Items_toUpdate:
SELECT
*

FROM
[Calc Items Avail] AS ia LEFT JOIN

Items AS i
ON ia.CatKey=i.CatKey

WHERE (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL)

ORDER BY ia.CatNum;

The "ID_Item IS NULL" is largely a debugging feature, so you can see which [Calc Items Avail] rows haven't yet been taken care of. It might also be useful in the future if other updating of Items from [Calc Items Avail] takes place before this step.

qryVb_Update_Items_Existing:
UPDATE qryVb_Items_toUpdate
SET
ia.ID_Item = i.ID,

i.CatNum = ia.CatNum,

i.CatExt = ia.CatExt,

i.CatKey = ia.CatKey,

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 & iif(GroupTitleDescr IS NULL,""," - "&GroupTitleDescr),

i.ITypOptCode = ITypOptCode_Calc,

i.ITypOptDescr = ITypOptDescr_Calc,

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 (PrepRecycle()), 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 bit wasn't working at first because (it turned out) the ITypOptCode and ITypOptDescr fields are allowed to be null but not empty strings, and the corresponding functions were sometimes returning empty strings. I changed the functions to return NULL whenever the result was an empty string, and now the query is happy.

PrepRecycle():
- qryVb_ItemsCalc_Unassigned:
  SELECT * FROM [Calc Items Avail] AS i WHERE i.ID_Item=0;
- qryVb_Items_Recyclable:
  SELECT * FROM Items AS i WHERE i.Pulled AND i.isFree AND NOT i.Active;;
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.CatExt = ia.CatExt,

i.CatKey = ia.CatKey,

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_Items_toAppend:
SELECT
ia.CatNum,

ia.CatExt,

ia.CatKey,

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;
qryVb_Append_Items_New:
INSERT INTO Items * FROM qryVb_Items_toAppend;

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.CatKey=i.CatKey

WHERE i.ID IS NULL

ORDER BY ia.CatNum;

The choice of i.ID 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.