VbzCart/pieces/catalog/building/2006 - Revision history

Woozle: Woozle moved page VbzCart catalog building 2006 to VbzCart/pieces/catalog/building/2006 without leaving a redirect: this is where it goes in the archive

2016-02-01T20:48:20Z

Woozle moved page VbzCart catalog building 2006 to VbzCart/pieces/catalog/building/2006 without leaving a redirect: this is where it goes in the archive

← Older revision	Revision as of 20:48, 1 February 2016
(No difference)

Woozle: 61 revisions imported: moving page from VbzWiki

2016-02-01T20:47:24Z

61 revisions imported: moving page from VbzWiki

← Older revision	Revision as of 20:47, 1 February 2016
(No difference)

Woozle: VbzCart catalog building moved to VbzCart catalog building 2006: writing new version

2007-07-20T01:56:16Z

VbzCart catalog building moved to VbzCart catalog building 2006: writing new version

← Older revision	Revision as of 01:56, 20 July 2007
(No difference)

Woozle: /* [Calc Items Avail] issues */ two-stage process now

2006-11-10T00:39:20Z

[Calc Items Avail] issues: two-stage process now

← Older revision		Revision as of 00:39, 10 November 2006
Line 24:		Line 24:
	* <s>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.)</s> Resolved by making the table permanent; see below.		* <s>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.)</s> 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.		* 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=====		=====queries=====

Woozle: /* queries */ fixed order of fields

2006-11-09T23:02:48Z

queries: fixed order of fields

← Older revision		Revision as of 23:02, 9 November 2006
Line 36:		Line 36:
	*:: OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,		*:: OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,
	*:: OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,		*:: OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,
			*:: t.CatNum AS CatNum_Title,
	*:: BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatExt,		*:: BuildOption(g.Code, gt.GroupCode, o.CatKey) AS CatExt,
	*:: NULL AS CatKey,		*:: NULL AS CatKey,
	*:: iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,		*:: iif(g.Descr IS NULL,iif(it.Descr IS NULL,it.NameSingular,it.Descr),g.Descr) AS ITypeDescr,
	*:: NULL AS ITypOptDescr_Calc,		*:: NULL AS ITypOptDescr_Calc,
	*:: t.CatNum AS CatNum_Title,
	*:: g.Code AS GroupCode,		*:: g.Code AS GroupCode,
	*:: gt.GroupCode AS GroupTitleCode,		*:: gt.GroupCode AS GroupTitleCode,

Woozle: /* Step 3 */ mods to first query

2006-11-09T19:28:23Z

Step 3: mods to first query

← Older revision		Revision as of 19:28, 9 November 2006
Line 97:		Line 97:
	Changes are committed in this phase.		Changes are committed in this phase.
	====Step 3====		====Step 3====
	'''qryVb_Items_CalcFields''' figures calculated fields ~~(currently just one)~~ in the '''Items''' table:		'''qryVb_Items_CalcFields''' figures/updates significant calculated fields in the '''Items''' table:
	* '''UPDATE''' Items '''SET''' CatKey=ID_Title&CatExt;		* '''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:		'''qryVb_Items_ResetActive''' resets flags and timestamps in the '''Items''' table:
	* '''UPDATE''' Items '''SET''' Active=False, InPrint=False, Sources=NULL '''WHERE''' Active		* '''UPDATE''' Items '''SET''' Active=False, InPrint=False, Sources=NULL '''WHERE''' Active

Woozle: /* Step 4 */ outdent

2006-11-09T19:17:10Z

Step 4: outdent

← Older revision		Revision as of 19:17, 9 November 2006
Line 112:		Line 112:
	*::: '''ON''' ia.CatKey=i.CatKey		*::: '''ON''' ia.CatKey=i.CatKey
	*::: '''WHERE''' (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL)		*::: '''WHERE''' (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL)
	*:: '''ORDER BY''' ia.CatNum;		*: '''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.		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.

Woozle: /* Step 4 */ sort existing items to update

2006-11-09T19:16:24Z

Step 4: sort existing items to update

← Older revision		Revision as of 19:16, 9 November 2006
Line 111:		Line 111:
	*:: Items AS i		*:: Items AS i
	*::: '''ON''' ia.CatKey=i.CatKey		*::: '''ON''' ia.CatKey=i.CatKey
	*::: '''WHERE''' (i.CatNum IS NOT NULL) AND (ia.ID_Item IS NULL);		*::: '''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.		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.

Woozle: /* [Calc Items Avail] issues */ redundant punctuation

2006-11-09T19:03:16Z

[Calc Items Avail] issues: redundant punctuation

← Older revision		Revision as of 19:03, 9 November 2006
Line 23:		Line 23:
	=====[Calc Items Avail] issues=====		=====[Calc Items Avail] issues=====
	* <s>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.)</s> Resolved by making the table permanent; see below.		* <s>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.)</s> 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.)		* 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.

	=====queries=====		=====queries=====
	* '''qryVb_Clear_Items_Avail''':		* '''qryVb_Clear_Items_Avail''':

Woozle: /* Step 1 */ issues

2006-11-09T19:02:37Z

Step 1: issues

← Older revision		Revision as of 19:02, 9 November 2006
Line 19:		Line 19:
	===Phase I===		===Phase I===
	====Step 1====		====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. ~~(This table 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.)~~ '''qryMake_Items_Avail''' gets data from '''qryFig_Items''' and uses it to create '''[Calc Items Avail]''' without further processing.		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.

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

			=====[Calc Items Avail] issues=====
			* <s>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.)</s> 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.)
			=====queries=====
	* '''qryVb_Clear_Items_Avail''':		* '''qryVb_Clear_Items_Avail''':
	: '''DELETE''' '''FROM''' [Calc Items Avail];		: '''DELETE''' '''FROM''' [Calc Items Avail];
Line 30:		Line 32:
	*: '''SELECT'''		*: '''SELECT'''
	*:: t.ID AS ID_Title,		*:: t.ID AS ID_Title,
	*:: 0 AS ID_Item,		*:: NULL AS ID_Item,
	*:: OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,		*:: OptionJoin(gt.GroupCode,o.CatKey,"/") AS ITypOptCode_Calc,
	*:: OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,		*:: OptionJoin(gt.GroupDescr,o.Descr," / ") AS IGrpOptDescr_Calc,