You can add customer-specific fields to the Innosoft standard tables. To do this, you do not add the fields directly to the Innosoft standard table, but to a separate table that ends with the "Ind" suffix and is usually named similarly to the standard table. The "Ind" table contains the same primary key as your main table. For example, you can extend the "header" table for document entry LbKopf with the primary key column BelegNr via the table LbKopfInd. In addition to creating the columns in the database schema, an additional data record is required in the table PlSteuer, which contains metadata for the new database field. Among other things, you use the PlSteuer data record to define the field type, e.g., field with selection values from a lookup table or editing field. You then activate the necessary additional data fields in the Admin Console of the App Server. Only fields with supported field types are available on the page.

Configuration in the Admin Console

Open the page > to activate the desired individual data fields for the Mobile App. Configuration is performed for each object. Use the Select target type field to switch to the field configuration view for the corresponding object. The selectable target object types contain some tables twice for different target types. For example, you can activate different fields for the document header table LbKopf depending on whether it is a weekly report or a service report. Similar to the individual data field configuration in Innosoft Classic, you can group individual data fields into categories with multilingual names. If the field configuration view for a target object is empty, you must first add a category using the action.

To enable an existing custom data field for the Mobile App, assign it to a category using the button. A field can only appear in one category. The "Assign" action automatically removes existing category assignments so that the field is only assigned to a maximum of one category.

You can change the order of the fields within a category by rearranging the fields in the table within the category using drag & drop. The categories are displayed in the Mobile App in the same order as on the administration page. Next to the allocation action within the category, you will find the button, which you can use to rearrange the categories. You can define one category without a display name per target object. Fields in this category are displayed immediately after the last standard field. Therefore, this category cannot be reordered (but the fields within the category can).

Some notes:

Newly defined custom data fields are not automatically assigned to a category. You must assign them manually. The idea behind this is to prevent anyone from accidentally changing the fields in the app. This allows the integration team, for example, to extend custom data fields for interfaces without restrictions and without changing the behavior of the app (the fields would have to be explicitly approved in the Admin Console).
The action allows you to export and import the configuration page. This allows you to exchange the configuration between different systems. The configuration includes the categories, assignment of fields to categories, arrangement of categories and fields, assigned constraints (read-only or mandatory), and text field type (single line/multiple lines, if explicitly set for some fields). Individual data fields are not created during import. However, the import checks whether the fields to be imported are available.
You only configure the individual data fields for the Mobile App. Innosoft Classic and FSM have separate systems for configuring individual data fields. Only the basic individual data fields in the database are the same. The separation is desirable because the different applications offer different functionalities and, for process-related reasons, different fields often have to be configured in the different applications.
You will find an overview of the available individual data fields in a separate info window. To view it, click on the icon next to the heading Custom data.

Supported field types

The supported field types can be divided into three different categories. Edit fields allow easy entry of data and times, numbers, and text. With selection fields, the user selects an entry from a defined set of selection values. The different selection field types differ in how the selection options are stored and whether the selection options are fixed or context-dependent, e.g., assignment dependent selection values. Text fields with suggestions are a mixture of edit fields (text values) and selection fields. The user selects a text from a set of text suggestions or enters their own text.

The following descriptions contain SQL examples showing how you can create the corresponding field types. Alternatively, you can use the individual field editing form in Innosoft FSM to create some field types.

Editing fields

Simple editable field that contains a value. The data type is determined by the underlying SQL column of the individual data field. For example, if the column LBKOPFIND_WHOLENUMBER has the database data type Int, the app displays a form field that accepts integer user entries. An exception is a RichText data field (selection type 170), which contains a reference to the RICH_TEXT.ID table and is therefore of the data type nvarchar(50) (see RichText in the APP). Beyond that, there are few customization options:

Edit fields with text data ([n]varchar columns) can be displayed as single-line and multi-line text input fields (regardless of this, the app ensures compliance with the defined text length of the database type).
- Fields for [n]varchar(max) columns are displayed as multi-line text input fields by default, whereas fields for [n]varchar columns with a fixed text length, e.g. [n]varchar(1000), are displayed as single-line text input fields by default.
- You can explicitly define a field as a single-line or multi-line text input field in the additional data management page in the Admin Console by setting the corresponding value in the Text mode column within the configuration table for the associated field.
Edit fields for database columns with the data types SmallDateTime, DateTime, and DateTime2 can be configured as date and time input, time input only, or date input only.
- PlSteuer.Darstellung = 1 oder 3: The app allows you to enter the date and time.
- PlSteuer.Darstellung = 4: The app only allows you to enter the time. The missing date component is filled in with ‘1/1/1970’ before the value is saved in the database column.
- Otherwise, the app only allows you to enter the date. The missing time component is filled in with ‘00:00’ before the value is saved in the database column.
- Alternatively, you can create the database column with the data type Time or Date, as long as you only want to store time or date values.

Database column	Description
PlSteuer.Bezeichnung	Contains the database column name to which the metadata refers. The PlSteuer table does not allow you to specify the table from which the column originates. Therefore, when creating the database column name, you should prefix it with the table name, e.g. LBKOPFIND_WHOLENUMBER
PlSteuer.Auswahltyp	Defines the field type. Always contains the number 2 or 170 (for RichText) for editing fields.
PlSteuer.Darstellung	Only relevant for database columns with date & time data type. See explanations above the table.
Tabelle PlTranslate/PlTranslateLang	Define a multilingual display name for the form field. The name of the database column is displayed if you have not defined a display name.

Example SQLs for creating edit fields

ALTER TABLE LBKOPFIND ADD LBKOPFIND_WHOLENUMBER int;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_FRACTIONAL float;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_DATETIME_DATE DateTime;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_DATETIME_TIME DateTime;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_DATETIME_BOTH DateTime;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_NATIVE_DATE Date;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_NATIVE_TIME Time;
ALTER TABLE LBKOPFIND ADD LBKOPFIND_SHORT_TEXT NVARCHAR(100);
ALTER TABLE LBKOPFIND ADD LBKOPFIND_REMARK_RT NVARCHAR(50);
GO
 
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_WHOLENUMBER', 2);
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_FRACTIONAL', 2);
 
-- varitions of datetime inputs. check documentation for the corresponding values for the column darstellung
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, DARSTELLUNG) VALUES (N'LBKOPFIND_DATETIME_DATE', 2, NULL);
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, DARSTELLUNG) VALUES (N'LBKOPFIND_DATETIME_TIME', 2, 4);
-- setting darstellung to 1 is equivalent to 3
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, DARSTELLUNG) VALUES (N'LBKOPFIND_DATETIME_BOTH', 2, 1);
 
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_NATIVE_DATE', 2);
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_NATIVE_TIME', 2);
 
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_SHORT_TEXT', 2);
 
-- richtext field INSERT INTO (PLSTEUER (BEZEICHNUNG, AUSWAHLTYP) VALUES (N'LBKOPFIND_REMARK_RT', 170); 
-- add english as default field label and german as translation
INSERT INTO PLTRANSLATE (TABELLE, FELD, BEZEICHNUNG) VALUES (N'LBKOPFIND', N'LBKOPFIND_WHOLENUMBER', N'Whole number');
INSERT INTO PLTRANSLATELANG (TABELLE, FELD, SPRACHE, BEZEICHNUNG) VALUES (N'LBKOPFIND', N'LBKOPFIND_WHOLENUMBER', 0, N'Ganze Zahl');
 
INSERT INTO PLTRANSLATE (TABELLE, FELD, BEZEICHNUNG) VALUES (N'LBKOPFIND', N'LBKOPFIND_DATETIME_DATE', N'Date only');
INSERT INTO PLTRANSLATELANG (TABELLE, FELD, SPRACHE, BEZEICHNUNG) VALUES (N'LBKOPFIND', N'LBKOPFIND_DATETIME_DATE', 0, N'Nur Datum');
 
-- add translations as needed. the label will fallback to the column name otherwise
GO
 
-- add example values for the service report with belegnr = 12.
INSERT INTO LBKOPFIND (BELEGNR
    , LBKOPFIND_WHOLENUMBER
    , LBKOPFIND_FRACTIONAL
    , LBKOPFIND_DATETIME_DATE
    , LBKOPFIND_DATETIME_TIME
    , LBKOPFIND_DATETIME_BOTH
    , LBKOPFIND_NATIVE_DATE
    , LBKOPFIND_NATIVE_TIME
    , LBKOPFIND_SHORT_TEXT)
    , LBKOPFIND_REMARK_RT)
VALUES (12
    , 512
    , 3.14
    , '24/12/2024 00:00' -- time component is set to midnight for date only input
    , '01/01/1970 13:20' -- date component is set to 1970/01/01 for time only input
    , '24/12/2024 13:20'
    , '24/12/2024'
    , '13:20'
	 , N'No christmas holidays this year :('
    , N'54462285-75FB-4966-9653-9585D6F2D88A'); -- assuming a richtext with id '54462285-75FB-4966-9653-9585D6F2D88A' exists in rich_text

Text field with suggestions

This field is a combination of a text editing field and a selection field with fixed selection values. The technician can select text from a list of suggestions, which may be multilingual, or enter their own text in an editing field (Combobox). Similar to the selection values for selection fields with fixed selection values, a database table or view provides the necessary suggestions. The individual field database column must be of data type [n]varchar. The data type of the (multilingual) suggested text from the self-definable database table or view should correspond to the data type of the individual field database column. The text editing field allows single-line texts. The app ensures compliance with the defined text length of the database type.

Columns of the database table/view, which provides the proposals

Column name (case insensitive)	Mandatory column?	Description
Nr	x	ID of the suggestion. Can be an integer or string. If there is a Language column, the combination of Nr, Language must be unique, as you may return translations in different languages for the same Id. Otherwise, the Nr column must already be unique.
Language		Defines the language of the display name in the Bezeichnung column as Innosoft Language Id (Int). If not available, the display name in the Bezeichnung column is shown for each set user language.
Bezeichnung	x	Contains the display name of the suggestion ([n]varchar) for the returned Language (if available). When the suggestion is selected, this text is transferred to the individual data field.
Inactive		Optional Bit or Int field. If the field is not null and contains a value <> 0, the selection value is no longer selectable/permitted during entry. Alternatively, you can delete the corresponding value from the table/view.

The Nr column, which serves as a key/ID, is necessary for technical, internal reasons. The Nr values are not saved.

Relevant columns for defining text fields with suggestions:

Database column	Description
PlSteuer.Bezeichnung	Contains the database column name to which the metadata refers. The PlSteuer table does not allow you to specify the table from which the column originates. Therefore, when creating the database column name, you should prefix it with the table name, e.g., PLIND_STATE.
PlSteuer.Auswahltyp	Defines the field type. Always contains the number 1 for text fields with suggestions.
PlSteuer.Tabelle	Name of the table/view that provides the suggestions according to the above rules
PlSteuer.OrderByField	Optional sorting of suggestions for the drop-down list. Sorting is a comma-separated sorting of columns followed by the sorting direction (ASC/DESC), e.g., COLUMN_A ASC, COLUMN_B DESC. The columns must be defined in the result set of the view or within the table. The sorting is passed to the app for each language using the following construct: ROW_NUMBER() OVER (PARTITION BY LANGUAGE ORDER BY <OrderByField>) By default, the suggestions are sorted in ascending order according to the NR column.
Tabelle PlTranslate/PlTranslateLang	Define a multilingual display name for the form field. The name of the database column is displayed if you have not defined a display name.

Example SQLs for creating text fields with suggestions

-- create CUS_STATE_SUGGESTIONS table that holds the suggestions for the drop-down list
CREATE TABLE CUS_STATE_SUGGESTIONS(NR int, BEZEICHNUNG NVARCHAR(200) NOT NULL, PRIMARY KEY(NR));
GO
 
-- store accepted (id = 1), rejected (id = 2), resolved (id = 3)
INSERT CUS_STATE_SUGGESTIONS(NR, BEZEICHNUNG) VALUES
  (1, N'Accepted')
, (2, N'Rejected')
, (3, N'Resolved');
GO
 
-- create custom data column for assignment and add it to plsteuer
ALTER TABLE PLIND ADD PLIND_STATE NVARCHAR(200);
GO
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, TABELLE) VALUES (N'PLIND_STATE', 1, N'CUS_STATE_SUGGESTIONS');
-- add english as default field label
INSERT INTO PLTRANSLATE (TABELLE, FELD, BEZEICHNUNG) VALUES (N'PLIND', N'PLIND_STATE', N'State');
-- add german field label
INSERT INTO PLTRANSLATELANG (TABELLE, FELD, SPRACHE, BEZEICHNUNG) VALUES (N'PLIND', N'PLIND_STATE', 0, N'Status');
GO
 
 
-- store the suggestion 'Accepted' (id = 1) for assignment with projnr = 'projnr42' and pindex = 12
INSERT INTO PLIND (PROJNR, PINDEX, PLIND_STATE) VALUES (N'projnr42', 12, N'Accepted');
-- store a freetext for assignment with projnr = 'projnr42' and pindex = 13
INSERT INTO PLIND (PROJNR, PINDEX, PLIND_STATE) VALUES (N'projnr42', 13, N'Rescheduled because of sudden illness');

Selection fields

Fixed selection values

With this field type, the available selection values are fixed and independent of the context in which the field is located. Each selection value can have multilingual display texts. The list of selection values is returned by a database table or database view. Each selection value contains a unique ID (NR column), which is stored in the corresponding database column of the individual data field when the value is selected. The (multilingual) display name of the selection value is not stored and is determined indirectly via the stored ID and the database table/view that returns the list of selection values. The ID can be either an integer, e.g., from the database data types Int or BigInt, or a character string, e.g., from the database data types [n]varchar(50). It is important that the data type of the NR column of the database table/view that returns the selection values matches the data type of the corresponding database column of the individual data field. Do not rely on implicit conversions, e.g., returning selection values with an Int Nr column and storing them in a SmallInt column, even if the current values are compatible.

Columns of the database table/view, which provides the selection values

Column name (case insensitive)	Mandatory column?	Description
Nr	x	ID of the selection value. Can be an integer or string. If there is a Language column, the combination of Nr and Language must be unique, as you may return translations in different languages for the same ID. Otherwise, the Nr column must already be unique.
Language		Defines the language of the display name in the Bezeichnung column as Innosoft Language Id (Int). If not available, the display name in the Bezeichnung column is shown for each set user language.
Bezeichnung	x	Contains the display name of the selection value ([n]varchar) for the returned language Language (if available).
Inactive		Optional Bit or Int field. If the field is not null and contains a value <> 0, the selection value can no longer be selected/is no longer permitted during entry, but data that has already been entered can still be displayed. Use this field instead of deleting selection values from the table/view so as not to "corrupt" data that has already been entered.

Relevant columns for defining selection fields with fixed selection values:

Database column	Description
PlSteuer.Bezeichnung	Contains the database column name to which the metadata refers. The PlSteuer table does not include an option to specify the table from which the column originates. Therefore, when creating the database column name, you should prefix it with the table name, e.g., PLIND_CHOICES.
PlSteuer.Auswahltyp	Defines the field type. Always contains the number 3 for selection fields with fixed selection values.
PlSteuer.Tabelle	Name of the table/view that provides the selection values according to the above rules
PlSteuer.OrderByField	Optional sorting of the selection values for the drop-down list. The sorting is a comma-separated list of columns followed by the sorting direction (ASC/DESC), e.g., COLUMN_A ASC, COLUMN_B DESC. The columns must be defined in the result set of the view or within the table. The sorting is passed to the app for each language using the following construct: ROW_NUMBER() OVER (PARTITION BY LANGUAGE ORDER BY <OrderByField>) By default, the selection values are sorted in ascending order according to the NR column.
Tabelle PlTranslate/PlTranslateLang	Define a multilingual display name for the form field. The name of the database column is added if you have not defined a display name.

Example SQLs for creating selection fields with fixed selection values

-- create CUS_CHOICES table that holds the options for the drop-down list
CREATE TABLE CUS_CHOICES (NR INT, LANGUAGE INT, BEZEICHNUNG NVARCHAR(200) NOT NULL, INACTIVE Bit, PRIMARY KEY(NR, LANGUAGE));
GO
 
-- store yes (id = 1), no (id = 2), maybe (id = 3) with english (language id = 1) and german (language id = 0) translation
INSERT CUS_CHOICES (NR, LANGUAGE, BEZEICHNUNG) VALUES
(1, 1, N'Yes'), (1, 0, N'Ja')
, (2, 1, N'No'), (2, 0, N'Nein')
, (3, 1, N'Maybe'), (3, 0, N'Vielleicht');
GO
 
-- create custom data column for assignment and add it to plsteuer
ALTER TABLE PLIND ADD PLIND_CHOICES int;
GO
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, TABELLE) VALUES (N'PLIND_CHOICES', 3, N'CUS_CHOICES');
-- add english as default field label
INSERT INTO PLTRANSLATE (TABELLE, FELD, BEZEICHNUNG) VALUES (N'PLIND', N'PLIND_CHOICES', N'Choices');
-- add german field label
INSERT INTO PLTRANSLATELANG (TABELLE, FELD, SPRACHE, BEZEICHNUNG) VALUES (N'PLIND', N'PLIND_CHOICES', 0, N'Auswahl');
GO
 
 
-- store the option 'yes' (id = 1) for assignment with projnr = 'projnr42' and pindex = 12
INSERT INTO PLIND (PROJNR, PINDEX, PLIND_CHOICES) VALUES (N'projnr42', 12, 1);
-- deactivate option 'yes' (id = 1) for further use
UPDATE CUS_CHOICES SET INACTIVE = 1 WHERE NR = 1;

Dynamic selection values

With this field type, the available selection values may depend on the context in which the field is located. Each selection value can have multilingual display texts. The list of selection values is determined by a user-definable SQL query that receives certain parameters. The parameter values are context-dependent. Similar to the fixed selection values, each selection value contains a unique ID (NR column), which is stored in the corresponding database column of the individual data field when the value is selected. The (multilingual) display name of the selection value is not stored and is determined indirectly via the stored ID and the user-definable SQL query that returns the list of selection values. The ID can be either an integer, e.g., from the database data types Int or BigInt, or a character string, e.g., from the database data types [n]varchar(50). It is important that the data type of the NR column of the SQL, which returns the selection values, matches the data type of the corresponding database column of the individual data field. Do not rely on implicit conversions, e.g., returning selection values with an Int Nr column and storing them in a SmallInt column, even if the current values are compatible.

There are three different ways to define SQL queries for selection value lists. The first option is currently only available in the Mobile App and is recommended when the selection values are determined dynamically, either directly or indirectly, via the deployment. This option can be combined with the second option, meaning you get the advantages of the first option for the Mobile App and can use the second option to provide a path for another application. Both options allow you to define the selection values depending on the assignment. However, the first option is adapted to the needs of Mobile App synchronization and differs slightly in the available parameters and in the columns of the SQL result set. The third option is a fallback mechanism for emergency assignments . It works like the cross-application variant, with the difference that the :assignmentId parameter is not allowed. If the assignment is only created in the app by the technician, the parameter cannot be resolved in advance by the server. See PlSteuer.Tabelle_MobileAppCreatedAssignment in the database columns listed below.

Regardless of the variant, the app receives the sorting of the result set per language. You define the order of the selection values in the drop-down list by using a corresponding ORDER BY in the SQL query. You should use ":" as the parameter character, i.e., for the parameter name ressnr, use the notation :ressnr in your SQL texts. The app also supports the native notation with @, but this is not compatible with other Innosoft applications.

App-specific variant (SQL batch)

The SQL has access to the following parameters:

Parameters (case insensitive)	Description
ressnr	Reference to RessList.RessNr. Contains the ID of the technician for whom the selection values are loaded.
login	Reference to PwUser.Name. Contains the technician's login name.
assignmentIds	Table variable with a single nvarchar(50) column isguid. Contains unique references to Planung.IsGuid. These are all IDs for assignments that are currently on the Mobile App for the technician. Use these IDs to restrict the result set accordingly. Assignment-dependent SQLs are currently possible for the following individual data tables: PLIND, LBKOPFIND, LBZLISTIND, LBZLISTEXTIND, LBBARIND and LBMATIND

The result set of the SQL should consist of the following columns:

Column name (case insensitive)	Mandatory column?	Description
Nr	x	ID of the selection value. Can be an integer or string. If there is an AssignmentId and/or Ressnr column, the Nr column must be unique for each AssignmentId and/or Ressnr, in the sense that there is only one unique selection value with the same ID per context. The SQL may return the same ID for different languages in order to provide translations in different languages.
Language		Defines the language of the display name in the Bezeichnung column as Innosoft Language Id (Int). If not available, the display name in the Bezeichnung column is shown for each set user language.
Bezeichnung	x	Contains the display name of the selection value ([n]varchar) for the returned language Language (if available).
Inactive		Optional Bit or Int field. If the field is not null and contains a value <> 0, the selection value can no longer be selected/is no longer permitted during entry. Data that has already been entered can still be displayed. Use this field instead of deleting selection values from the table/view so as not to "corrupt" data that has already been entered.
AssignmentId		Optional nvarchar column containing a reference to Planung.IsGuid. If present, the corresponding selection values can only be selected in the context of the corresponding assignment, e.g., for a service report individual data field, the selection values are restricted by the parent assignment. You can obtain the necessary assignment IDs via the table variable assignmentIds in the isguid column. You should only return values that are contained in assignmentIds.isguid.
Ressnr		Optional nvarchar column containing a reference to RessList.RessNr. Only available for fields for time recording of external resources (LBZLISTEXTIND). If available, the corresponding selection values are only available in the context of the corresponding Innosoft external resource for which the technician is recording time. The available Innosoft external resources for which the technician can record times are assigned in the KURESS table depending on the project. If the technician records times for external resources that are not maintained as Innosoft resources (e.g., temporary workers), the app uses the selection values that were returned for the technician's RessNr. Therefore, you should ensure that the result set contains selection values for the technician's RessNr (ressnr parameter).

Example SQL with assignment dependency

-- assuming our options are project-specific in CUS_DYN_CHOICES table that holds the options for the drop-down list for each project
SELECT p.ISGUID as ASSIGNMENTID, c.NR, c.LANGUAGE, c.BEZEICHNUNG, c.INACTIVE
FROM CUS_DYN_CHOICES c
JOIN PLANUNG p ON c.PROJNR = p.PROJNR
WHERE p.ISGUID IN (SELECT a.isguid FROM :assignmentIds a) 
ORDER BY c.LANGUAGE, c.BEZEICHNUNG;

Complex example SQL with assignment and resource dependency for LBZLISTEXTIND

-- assuming our options are assignment-specific in CUS_PLIND_TASKS(SAPUI = unique option id, Id = Assignment ID, TASK_DESCRIPTION = option text) table that holds the options for the drop-down list for each assignment
SELECT p.IsGuid AssignmentId, t.RessNr, t.NR, t.Bezeichnung
FROM PLANUNG p
CROSS APPLY (
    SELECT r.RessNr, c.SAPUI, c.TASK_DESCRIPTION
    FROM (
        SELECT p.PROJNR, k.RESSNR FROM KURESS k WHERE k.PROJNR = p.PROJNR AND k.INTERN = 1 
        -- ensure we return the ressnr of the technician. look up values for the technician are required for non innosoft resources (kuress.intern = 0)
        UNION
        SELECT p.PROJNR, p.RESSNR
    ) r (PROJNR, RESSNR)
    JOIN PLANUNG p_ext ON r.RESSNR = p_ext.RESSNR AND r.PROJNR = p_ext.PROJNR
    JOIN CUS_PLIND_TASKS c ON p_ext.ISGUID = c.ID
) t (RessNr, NR, Bezeichnung)
WHERE p.IsGuid IN (SELECT IsGuid FROM :assignmentIds)

Cross-application variant (not recommended)

This variant is not recommended when using the assignmentId parameter. The problem is that synchronization must execute the SQL multiple times (for each use in the app). This can significantly increase synchronization time and the load on the SQL server. Instead, use the app's own variant, which covers all usage variations in a single SQL and is therefore only executed once, regardless of the number of uses in the app.

The SQL has access to the following parameters:

Parameters (case insensitive)	Description
ressnr	Reference to RessList.RessNr. Contains the ID of the technician for whom the selection values are loaded.
login	Reference to PwUser.Name. Contains the technician's login name.
assignmentId	Reference to Planning.IsGuid. Assignment ID of an assignment for which the selection values are loaded depending on the assignment. The SQL is executed for each assignment that is currently on the Mobile App for the technician. Use this ID to restrict the result set accordingly. Assignment-dependent SQLs are currently possible for the following individual data tables: PLIND, LBKOPFIND, LBZLISTIND, LBZLISTEXTIND, LBBARIND und LBMATIND

The result set of the SQL should consist of the following columns:

Column name (case insensitive)	Mandatory column?	Description
Nr	x	ID of the selection value. Can be an integer or string. If there is an AssignmentId and/or Ressnr column, the Nr column must be unique for each AssignmentId and/or Ressnr, in the sense that there is only one unique selection value with the same ID per context. The SQL may return the same ID for different languages in order to provide translations in different languages.
Language		Defines the language of the display name in the Bezeichnung column as Innosoft Language Id (Int). If not available, the display name in the Bezeichnung column is shown for each set user language.
Bezeichnung	x	Contains the display name of the selection value ([n]varchar) for the returned language Language (if available).
Inactive		Optional Bit or int field. If the field is not null and contains a value <> 0, the selection value can no longer be selected/is no longer permitted during entry, but data that has already been entered can still be displayed. Use this field instead of deleting selection values from the table/view so as not to "corrupt" data that has already been entered.

Example SQL with dependency on assignment

-- assuming our options are project-specific in CUS_DYN_CHOICES table that holds the options for the drop-down list for each project
SELECT
	c.NR, c.LANGUAGE, c.BEZEICHNUNG
FROM CUS_DYN_CHOICES c
JOIN PLANUNG p ON c.PROJNR = p.PROJNR
WHERE p.ISGUID = :assignmentId
ORDER BY c.LANGUAGE, c.BEZEICHNUNG;

Relevant columns for defining selection fields with dynamic selection values:

Database column	Description
PlSteuer.Bezeichnung	Contains the database column name to which the metadata refers. The PlSteuer table does not include an option for specifying the table from which the column originates. Therefore, when creating the database column name, you should prefix it with the table name, e.g., LBKOPFIND_DYN_CHOICES.
PlSteuer.Auswahltyp	Defines the field type. Always contains the number 5 for selection fields with dynamic selection values.
PlSteuer.Tabelle	Used for the cross-application SQL variant. Contains a reference to the table FeldInhaltM, in which the SQL is stored (FeldInhaltM.Langtext). The reference key consisting of FeldInhaltM.Gruppe and FeldInhaltM.Zaehler is encoded as <Group>;<Counter> in this column, e.g., 1;10. This column is ignored if Table_Batch contains a value.
PlSteuer.Tabelle_Batch	Used for the app's own SQL variant. Contains a reference to the table FeldInhaltM, in which the SQL is stored (FeldInhaltM.Langtext). The reference key consists of FeldInhaltM.Gruppe and FeldInhaltM.Zaehler, is encoded as <Group>;<Counter> in this column, e.g., 1;10. This column takes precedence over the Tabelle column. This allows you to store a compatible SQL for other applications via the Tabelle column and use the app's own SQL variant at the same time.
PlSteuer.Tabelle_MobileAppCreatedAssignment	Fallback mechanism for emergency assignments, as the server cannot resolve :assignmentId parameters for assignments that have not yet been created. Without this fallback, selection type 5 drop-down lists within emergency assignments will not contain any selection values. Only the parameters :ressnr and :login may be used in the referenced custom sql (FeldinhaltM). If you do not use emergency assignments, you can ignore this section.
PlSteuer.OrderByField	Ignored. Sorting is performed directly via SQL.
Tabelle PlTranslate/PlTranslateLang	Define a multilingual display name for the form field. The name of the database column is added if you have not defined a display name.

Example SQLs for creating selection fields with dynamic selection values

-- assuming our options are project-specific
-- create CUS_DYN_CHOICES table that holds the options for the drop-down list for each project
CREATE TABLE CUS_DYN_CHOICES (PROJNR NVARCHAR(20), NR INT, LANGUAGE INT, BEZEICHNUNG NVARCHAR(200) NOT NULL,
    PRIMARY KEY(PROJNR, NR, LANGUAGE));
GO
 
-- store yes (id = 1), no (id = 2), maybe (id = 3) with english (language id = 1) and german (language id = 0) translation
INSERT CUS_DYN_CHOICES (PROJNR, NR, LANGUAGE, BEZEICHNUNG) VALUES
-- projnr42
  ('projnr42', 1, 1, N'Yes'), ('projnr42',1, 0, N'Ja')
, ('projnr42', 2, 1, N'No'), ('projnr42', 2, 0, N'Nein')
, ('projnr42', 3, 1, N'Maybe'), ('projnr42', 3, 0, N'Vielleicht')
-- projnr12
,  ('projnr12', 1, 1, N'Sure'), ('projnr12', 1, 0, N'Sicher')
, ('projnr12', 2, 1, N'No'), ('projnr12', 2, 0, N'Nein');
GO
 
-- create custom data column for service report and add it to plsteuer
ALTER TABLE LBKOPFIND ADD LBKOPF_DYN_CHOICES int;
GO
 
-- this example includes both a batch sql and the equivalent sql with :assignmentIds parameterization
-- define batch sql
DECLARE @gruppeBatch INT = 7000;
DECLARE @zaehlerBatch INT = 42;
DECLARE @lookUpSqlBatch NVARCHAR(2000) = N'SELECT p.ISGUID as ASSIGNMENTID, c.NR, c.LANGUAGE, c.BEZEICHNUNG FROM CUS_DYN_CHOICES c JOIN PLANUNG p ON c.PROJNR = p.PROJNR WHERE p.ISGUID IN (SELECT a.isguid FROM :assignmentIds a) ORDER BY c.LANGUAGE, c.BEZEICHNUNG';
 
INSERT INTO FELDINHALTM (GRUPPE, ZAEHLER, LANGIDX, LANGTEXT) VALUES (@zaehlerBatch, @zaehlerBatch, 0, @lookUpSqlBatch);
 
-- define sql with :assignmentId parameterization
DECLARE @gruppe INT = 7000;
DECLARE @zaehler INT = 43;
-- order options by label. for compatibility with innosoft classic we are using ':' as parameter token instead of '@'
DECLARE @lookUpSql NVARCHAR(2000) = N'SELECT c.NR, c.LANGUAGE, c.BEZEICHNUNG FROM CUS_DYN_CHOICES c JOIN PLANUNG p ON c.PROJNR = p.PROJNR WHERE p.ISGUID = :assignmentId ORDER BY c.LANGUAGE, c.BEZEICHNUNG';
 
INSERT INTO FELDINHALTM (GRUPPE, ZAEHLER, LANGIDX, LANGTEXT) VALUES (@gruppe, @zaehler, 0, @lookUpSql);
 
-- define custom data field
INSERT INTO PLSTEUER (BEZEICHNUNG, AUSWAHLTYP, TABELLE, TABELLE_BATCH) VALUES (N'LBKOPF_DYN_CHOICES', 5
	, CONVERT(NVARCHAR(20), @gruppe) + N';' + CONVERT(NVARCHAR(20), @zaehler)
	, CONVERT(NVARCHAR(20), @gruppeBatch) + N';' + CONVERT(NVARCHAR(20), @zaehlerBatch));
-- add english as default field label
INSERT INTO PLTRANSLATE (TABELLE, FELD, BEZEICHNUNG) VALUES (N'LBKOPF', N'LBKOPF_DYN_CHOICES', N'Choices');
-- add german field label
INSERT INTO PLTRANSLATELANG (TABELLE, FELD, SPRACHE, BEZEICHNUNG) VALUES (N'LBKOPF', N'LBKOPF_DYN_CHOICES', 0, N'Auswahl');
GO
 
-- store the option 'yes' (id = 1) for service repoot with belegnr = 12
-- assuming the service report was created on assignment that is linked to a project that exists inside CUS_DYN_CHOICES
INSERT INTO LBKOPFIND (BELEGNR, LBKOPF_DYN_CHOICES) VALUES (12, 1);