Time recording: Customization depending on the Time Type

You can customize the form for time recording within the Mobile App depending on the selected time types. For example, you can customize standard fields LbzList(Ext) and individual data fields LbzListInd/LbzListExtInd depending on the selected time type LbzList(Ext).TimeTypeIsGuid:

  • hide or

  • make a mandatory field

This behavior is configured using the following tables:

  • TimeTypeFieldMapping

  • TimeTypeFieldOptions

Use the TimeTypeFieldMapping table to assign the database field that you want to adjust (e.g. LbzList.MaschNr) to a time type (TimeTypes.IsGuid) for which the adjustment should apply. Use the TimeTypeFieldOptions table to assign the corresponding customizations hide (Visible = 0) or mandatory field (Mandatory = 1) to the time types and database field previously defined in TimeTypeFieldMapping. Visible = null or Mandatory = null means "use the default behavior". For example, an individual data field can be configured as a mandatory field in the Custom Data administration interface of the admin console for the Mobile App. If Mandatory = null, the custom data field is a mandatory field if it has been configured as a mandatory field in the admin interface. If Mandatory = 0, the individual data field for the associated time type is not a mandatory field, even if it has been configured as a mandatory field in the administration interface.

Theoretically, the adjustment can be restricted to an application via the ApplicationId columns and to a time type schema (is assigned to the main group) via the TimeTypeSchemaIsGuid column.

Make field, e.g. LbzList.MaschNr, a mandatory field for all working time types (or other IsTime types)
DECLARE @IsTypes table(IsType int primary key);
DECLARE @Fields table(TableName nvarchar(100), Field nvarchar(100), primary key(TableName, Field));
-- add 3 for preparatory work and 5 for extra work if required
INSERT INTO @IsTypes VALUES (2);
INSERT INTO @Fields VALUES (N'LbzList', N'MaschNr');
 
 
INSERT INTO TimeTypeFieldMapping(Id, TableName, Field, TimeTypeIsGuid)
SELECT
NEWID(), f.TableName, f.Field, t.ISGUID
FROM @Fields as f
CROSS JOIN TIMETYPES t
WHERE t.ISTYPE IN (SELECT IsType FROM @IsTypes)
AND NOT EXISTS (SELECT * FROM TimeTypeFieldMapping m WHERE m.TableName = f.TableName AND m.Field = f.Field AND m.TimeTypeIsGuid = t.ISGUID);
 
INSERT INTO TimeTypeFieldOptions(Id, TimeTypeFieldMappingId, Mandatory)
SELECT
NEWID(), m.Id, 1
FROM TimeTypeFieldMapping m
WHERE
m.TimeTypeIsGuid IN (SELECT t.IsGuid FROM TIMETYPES t WHERE t.ISTYPE IN (SELECT IsType FROM @IsTypes))
AND NOT EXISTS (SELECT * FROM TimeTypeFieldOptions o WHERE o.TimeTypeFieldMappingId = m.Id AND o.ApplicationId IS NULL AND o.TimeTypeSchemaIsGuid IS NULL);


Hide fields for certain time type IDs
DECLARE @TimeTypeIds table(TimeTypeIsGuid nvarchar(50) primary key);
DECLARE @Fields table(TableName nvarchar(100), Field nvarchar(100), primary key(TableName, Field));
 
-- add TIMETYPES.IsGuid as required
INSERT INTO @TimeTypeIds VALUES (N'01fe8b79-674b-44c1-bd3d-6fb7616353d2'), (N'067e4fb8-6421-41bd-8cbf-faa34fcb1abc');
-- add fields as required
INSERT INTO @Fields VALUES (N'LbzListInd', N'Remark');
THROW 51000, N'Not so fast: edit the required @TimeTypeIds and Fields first (the lines above) before you execute this sql and then remove this THROW statement', 5;
 
 
INSERT INTO TimeTypeFieldMapping(Id, TableName, Field, TimeTypeIsGuid)
SELECT
NEWID(), f.TableName, f.Field, t.TimeTypeIsGuid
FROM @Fields as f
CROSS JOIN @TimeTypeIds t
WHERE NOT EXISTS (SELECT * FROM TimeTypeFieldMapping m WHERE m.TableName = f.TableName AND m.Field = f.Field AND m.TimeTypeIsGuid = t.TimeTypeIsGuid);
 
INSERT INTO TimeTypeFieldOptions(Id, TimeTypeFieldMappingId, Visible)
SELECT
NEWID(), m.Id, 0
FROM TimeTypeFieldMapping m
WHERE
m.TimeTypeIsGuid IN (SELECT t.TimeTypeIsGuid FROM @TimeTypeIds t)
AND NOT EXISTS (SELECT * FROM TimeTypeFieldOptions o WHERE o.TimeTypeFieldMappingId = m.Id AND o.ApplicationId IS NULL AND o.TimeTypeSchemaIsGuid IS NULL);