Create a Store global attribute with a SQL migration in Znode 9
Create a Store support-label field, connect its metadata to an existing group and family, and preserve administrator labels and Store values on reruns.
Try the Store-setting experience
Change a fictional Store’s support label and see the display rules in action. This demonstrates the result; SQL and Razor run in your Znode environment.
Open interactive preview
In this guide
- Store entity and configured family
- Configured attribute group
- GcgSupportLabel definition
- Localized Admin label
- Store value set in Admin
One field, several relationships
A support team wants a short label beside its Store contact information. The value belongs to a Store, so this recipe uses global-attribute metadata. A product specification belongs to PIM and follows a different set of tables. Decide that ownership before writing a migration.
The fictional attribute is GcgSupportLabel. Its definition says what the field is. A localized label names the field in Admin. A group places it on a form; a family and entity mapping connect the group to the intended Store configuration. None of those metadata rows is the value entered for a particular Store. This example deliberately leaves Store values under administrative control.
Prepare the target and the runner
Use Admin > Global Attributes > Attribute Groups to create or select the Store group, then associate it with the existing Store family under Global Attribute Families. Znode 9 maintains one family for each Store, User, or Account entity type. This example expects group code GcgContent and family code Store; substitute the existing family code in your installation. Confirm their locale labels before deployment.
Set @AuditUserId to an existing active deployment actor and review the locale code. IDs are resolved from bounded codes for the other prerequisites. The script rejects missing or ambiguous lookups. It also selects the non-PIM Text type explicitly, because a type name alone is not enough context.
Use one dedicated deployment connection. This example owns its transaction and refuses an ambient transaction. If your DbUp runner owns transactions, adapt the transaction boundary as a reviewed change before using it. A transaction-scoped application lock serializes other deployments that use the same lock name. It does not stop Admin or unrelated scripts from writing these tables, so use a controlled deployment window.
Add the metadata migration
Save this as a new numbered SQL migration. Before execution, set @AuditUserId and confirm the locale, group, family, and attribute codes. The script creates the Store field definition and missing relationships; it does not assign a value to any Store.
Read it in three passes: validate the deployment actor and prerequisites, create or verify the field definition, then add missing label and form mappings. Each lookup must resolve one intended row. Existing labels are preserved, while conflicting definitions or duplicate rows stop the migration.
001-add-support-label.sql sql
-- Original GCG example. Znode 9 global-attribute schema pattern.
-- Configure these inputs after checking the target database and patch.
-- Run on a dedicated deployment connection without an outer transaction.
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @AuditUserId int = NULL; -- Required: existing deployment actor.
DECLARE @LocaleCode nvarchar(50) = N'en-US';
DECLARE @GroupCode varchar(200) = 'GcgContent';
DECLARE @FamilyCode varchar(200) = 'Store';
DECLARE @Code nvarchar(300) = N'GcgSupportLabel';
DECLARE @Label nvarchar(300) = N'Support label';
DECLARE @EntityId int, @TypeId int, @LocaleId int,
@GroupId int, @FamilyId int, @AttributeId int,
@LockResult int, @Now datetime = GETDATE();
IF @@TRANCOUNT <> 0
THROW 51000, 'This example owns its transaction. Review runner configuration.', 1;
IF @AuditUserId IS NULL OR NOT EXISTS
(SELECT 1 FROM dbo.ZnodeUser WHERE UserId = @AuditUserId AND IsActive = 1)
THROW 51001, 'Set an existing active deployment actor.', 1;
BEGIN TRY
BEGIN TRANSACTION;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'GCG.StoreAttributeMetadata',
@LockMode = 'Exclusive', @LockOwner = 'Transaction',
@LockTimeout = 10000;
IF @LockResult < 0
THROW 51002, 'Another cooperating attribute deployment is running.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1) <> 1
THROW 51003, 'Expected exactly one active Store entity.', 1;
SELECT @EntityId = GlobalEntityId FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0) <> 1
THROW 51004, 'Expected exactly one non-PIM Text type.', 1;
SELECT @TypeId = AttributeTypeId FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0;
IF (SELECT COUNT(*) FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1) <> 1
THROW 51005, 'Expected exactly one active locale.', 1;
SELECT @LocaleId = LocaleId FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId) <> 1
THROW 51006, 'Create or select one Store group before deployment.', 1;
SELECT @GroupId = GlobalAttributeGroupId FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId) <> 1
THROW 51007, 'Expected exactly one intended Store family.', 1;
SELECT @FamilyId = GlobalAttributeFamilyId FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId;
IF EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code
AND (GlobalEntityId <> @EntityId OR GlobalEntityId IS NULL))
THROW 51014, 'Attribute code is already used by another entity.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId) > 1
THROW 51008, 'Duplicate attribute definitions require investigation.', 1;
SELECT @AttributeId = GlobalAttributeId FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId;
IF @AttributeId IS NOT NULL AND EXISTS
(SELECT 1 FROM dbo.ZnodeGlobalAttribute WHERE GlobalAttributeId = @AttributeId
AND (AttributeTypeId IS NULL OR AttributeTypeId <> @TypeId
OR ISNULL(IsSystemDefined, 1) <> 0 OR ISNULL(IsRequired, 1) <> 0
OR ISNULL(IsLocalizable, 0) <> 1 OR ISNULL(IsActive, 0) <> 1))
THROW 51009, 'Existing definition conflicts with this migration.', 1;
IF @AttributeId IS NULL
BEGIN
INSERT dbo.ZnodeGlobalAttribute
(AttributeTypeId, AttributeCode, IsRequired, IsLocalizable, IsActive,
DisplayOrder, HelpDescription, CreatedBy, CreatedDate,
ModifiedBy, ModifiedDate, IsSystemDefined, GlobalEntityId)
VALUES (@TypeId, @Code, 0, 1, 1, 100, N'Customer-facing support label',
@AuditUserId, @Now, @AuditUserId, @Now, 0, @EntityId);
SET @AttributeId = CONVERT(int, SCOPE_IDENTITY());
END;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId) > 1
THROW 51010, 'Duplicate localized labels require investigation.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId)
INSERT dbo.ZnodeGlobalAttributeLocale
(LocaleId, GlobalAttributeId, AttributeName, Description,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@LocaleId, @AttributeId, @Label, NULL,
@AuditUserId, @Now, @AuditUserId, @Now);
-- An existing translated label belongs to the administrator. Preserve it.
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId) > 1
THROW 51011, 'Duplicate attribute/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId)
INSERT dbo.ZnodeGlobalAttributeGroupMapper
(GlobalAttributeGroupId, GlobalAttributeId, AttributeDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @AttributeId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId) > 1
THROW 51012, 'Duplicate family/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId)
INSERT dbo.ZnodeGlobalFamilyGroupMapper
(GlobalAttributeFamilyId, GlobalAttributeGroupId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@FamilyId, @GroupId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId) > 1
THROW 51013, 'Duplicate group/entity mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId)
INSERT dbo.ZnodeGlobalGroupEntityMapper
(GlobalAttributeGroupId, GlobalEntityId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @EntityId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
COMMIT TRANSACTION;
SELECT @Code AS AttributeCode, @AttributeId AS AttributeId,
@GroupCode AS GroupCode, @FamilyCode AS FamilyCode, @LocaleCode AS LocaleCode;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
On a fresh run, GcgSupportLabel is created as an active, optional, localizable Text field under GcgContent and the Store family. On an unchanged rerun, the same metadata is reused. If an administrator has renamed the displayed label, that label remains. A failed step rolls back this script's transaction; its transaction-scoped application lock coordinates only deployments using the same lock name.
Read back the graph
Run this query with the same codes used by the migration. It checks the exact path from Store entity to attribute, locale label, group, and family. This recipe expects one row with GcgSupportLabel, the selected label, en-US, GcgContent, and Store.
Zero rows means at least one required relationship is absent. More than one row requires investigation of the individual mappings, because joins can multiply duplicate relationships. Use the missing-mapping diagnostic guide to locate an absent relationship before preparing a correction.
verify-support-label.sql sql
SELECT a.AttributeCode, l.AttributeName, loc.Code AS LocaleCode,
g.GroupCode, f.FamilyCode, e.EntityName
FROM dbo.ZnodeGlobalAttribute a
JOIN dbo.ZnodeGlobalEntity e ON e.GlobalEntityId = a.GlobalEntityId
JOIN dbo.ZnodeGlobalAttributeLocale l ON l.GlobalAttributeId = a.GlobalAttributeId
JOIN dbo.ZnodeLocale loc ON loc.LocaleId = l.LocaleId
JOIN dbo.ZnodeGlobalAttributeGroupMapper gm ON gm.GlobalAttributeId = a.GlobalAttributeId
JOIN dbo.ZnodeGlobalAttributeGroup g ON g.GlobalAttributeGroupId = gm.GlobalAttributeGroupId
JOIN dbo.ZnodeGlobalFamilyGroupMapper fm ON fm.GlobalAttributeGroupId = g.GlobalAttributeGroupId
JOIN dbo.ZnodeGlobalAttributeFamily f ON f.GlobalAttributeFamilyId = fm.GlobalAttributeFamilyId
JOIN dbo.ZnodeGlobalGroupEntityMapper em ON em.GlobalAttributeGroupId = g.GlobalAttributeGroupId
AND em.GlobalEntityId = e.GlobalEntityId
WHERE a.AttributeCode = N'GcgSupportLabel' AND e.EntityName = N'Store'
AND g.GroupCode = 'GcgContent' AND f.FamilyCode = 'Store'
AND g.GlobalEntityId = e.GlobalEntityId AND f.GlobalEntityId = e.GlobalEntityId
AND loc.Code = N'en-US';The query verifies metadata, not the value for a particular Store. Finish by entering a sample value in Additional Store Attributes, reopening it in the same locale, and reading it through the application's Store attribute service.
Verify the Store workflow
Open Stores and Reps > Stores > Additional Store Attributes and locate the field for the intended Store. Enter a synthetic support label, save, reload, and verify the same locale. Read the value through the application path used by your customization. Publish Store settings and refresh caches when required by that consuming path and Znode patch.
Exercise fresh creation, an unchanged rerun, repair of a missing mapping, an edited Admin label, a conflicting definition, and duplicate locale rows in a disposable copy of the target schema. Verify the same field through Admin, the application read path, and the deployment runner. These checks connect migration behavior to the actual Store configuration.
- Run against a disposable copy of the target schema before release.
- Record the exact Znode patch, SQL Server version, migration runner, and observed Admin result.
- Test missing group/family prerequisites and a same-code conflict in your target schema.
- Verify no Store values or unrelated metadata changed.
Make the next change a forward migration
Once the file has been recorded in the migration journal, editing that file is not a reliable way to update deployed environments. Add a new migration for a new requirement. Keep deliberate value changes separate from metadata creation so reviewers can see exactly which administrator-owned configuration is being changed.
Avoid deleting the attribute as a routine rollback once users have entered data or customizations depend on it. A forward correction is often safer. If removal is required, first inventory its values and relationships and define a reviewed recovery path. The next recipe explains the difference between journaled migrations and programmability files that run on every deployment.
Inspect exact column types before preparing the migration
Run this read-only SQL Server catalog query in the intended Znode 9 database with metadata visibility. Compare the reported type, size, nullability, identity, and default for each column against the migration inputs. MaximumBytes is storage bytes: a positive nvarchar value uses two bytes per UTF-16 storage unit, while -1 means max. This check describes columns; it does not establish the complete Admin or application behavior.
inspect-global-attribute-columns.sql sql
SELECT s.name AS SchemaName, t.name AS TableName,
c.column_id, c.name AS ColumnName, ty.name AS TypeName,
c.max_length AS MaximumBytes, c.precision, c.scale,
c.is_nullable, c.is_identity, dc.definition AS DefaultExpression
FROM sys.tables AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints AS dc
ON dc.object_id = c.default_object_id
WHERE s.name = N'dbo'
AND t.name IN (
N'ZnodeGlobalAttribute', N'ZnodeGlobalAttributeLocale',
N'ZnodeGlobalAttributeGroupMapper', N'ZnodeGlobalFamilyGroupMapper',
N'ZnodeGlobalGroupEntityMapper')
ORDER BY t.name, c.column_id;
References and further reading
Bring your next engineering question.
GCG helps teams review attribute migrations, deployment behavior, and the application paths that depend on them.
Independent guidance from GCG. Znode is a trademark of its owner. Examples use fictional data and are not official platform documentation. Suggest a correction.