.frm file

The user forms .frm file will be converted to Single DB with the original file being backed up, per Backups

Permission

The creation of tables should not require any SQL permissions granted

Example form

Sample Table:

CREATE TABLE [finance].[uUserForm](
[seq] [int] IDENTITY(1,1) NOT NULL,
[DebtorID] [int] NULL,
[Details] [varchar](50) NULL
) ON [MAIN]

Sample Lookup Table:

CREATE TABLE [finance].[uluFinanceLookup](
[Code] [varchar](50) NULL,
[Description] [varchar](50) NULL
) ON [MAIN]

Sample User Form code:

[FORM]
Query="uUserForm"
QueryLink="DebtorID"
QueryLinkInSynergy="DebtorID"
Caption="v68 User Form Test"
ShowButtonApply

[Fields]
GroupBox, Width="420", Height="140", Caption="Deposit Details", Level="1"
wwDBLookupCombo, DataField="Details", Left="110", TopRel="25", Width="200", LabelCaption="Code:", LookupQuery="[FINANCE]SELECT Code;Description FROM finance.uluFinanceLookup", LookupField="Code", Selected="Code|15",Selected="Description|50",Level="2"

Additional Finance Databases

Any additional finance databases will convert any finance schema prefixes, as per example form

User Logins

dbo.SynergeticCurrentUser

Some user tables had used default values (constraints) set up against tables.

This is not a supported approach in v68 and will result in the user zSynergetic_main_dbo having updated or modified a record/table.

SYSTEM_USER

SYSTEM_USER has historically been used for user forms to identify users who may have created or modified records in a table.

However, Synergetic now connects to the database as a named user rather than as the actual user.

As a result, SYSTEM_USER would return the same result across all users when logged in to Synergetic.

Rather than SYSTEM_USER, clients should use the new function named dbo.fnConstraintGetLoginName() which will give the required user name.

dbo.fnConstraintGetLoginName()

dbo.fnConstraintGetLoginName()  is simply a wrapper for dbo.fnGetLoginName() and is the recommended function for use within the system to return a user login.

When something is a default constraint, it can't be modified, and Synergetic may need to modifiy this in the future. 

DDL triggers are in place to avoid dbo.fnGetLoginName() being used directly.

ALTER TABLE [dbo].[uExampleTable] ADD  CONSTRAINT [DF_uExampleTable_LoginName]  DEFAULT ([dbo].[fnConstraintGetLoginName]()) FOR [LoginName]
GO

Best Practice in v68

The best practice for having 'Modified By' and 'Modified Date' fields populated is using triggers.

A default value constraint will simply populate a record at the point of entry and will require an additional trigger for any other logic, such as recording updates.

An example of this is as follows:

DECLARE
  @Username VARCHAR(100) = dbo.fnConstraintGetLoginName(),
  @ID INTEGER = dbo.fnGetConfigUserID(''), --Used when you want to save the ID rather than the login name
  @Date DATETIME = GETDATE()

UPDATE
  t
SET
  t.ModifiedDate = @Date
, t.ModifiedBy   = @Username
, t.ModifiedByID = @ID
FROM
  uExampleTable t
INNER JOIN inserted i ON
  i.uExampleTableSeq = t.uExampleTableSeq