Can not insert data on remote database custom table

[tdowning@solidconcepts.com]

I have added a custom table to hold account Territory assignments.

This is a one to one relationship with the Account Table

In Code I look for an account territory assignment record for the current account

If there is no account territory assignment record I Insert one with default values based on a set of defaults.

I have similiar code on the Add New Account as well.

This works great for all users logging into and using the network installation.

However on remote clients, users can not insert into the new custom table.

Script Error message box is displayed

An error occured executing active form script (Account:TerritoryAssignment).

Error in include script (SYSTEMLX DATABASE SUPPORT):

The statement has been terminated.

Cannot insert duplicate key row in object 'sysdba.ACCOUNTTERRITORYASSIGNMENT' with unique index 'ACCOUNTTERRITORYASSIGNMENT_PRIMARY'. The duplicate key value is (accountid) at line 255, char 10.

As stated this works fine on the normal network install.  Just a remote user(I'm not sure if it is specific to this user or all remote users.

FYI, there are no records for the offending accountid in the child table.

I have gone so far as to drop and recreate the custom table ensuring that the "Enable Field Level security" and Syc this Table flags are set to true and the  Sync option is set to "Either Direction"

I have a copy of the remote database installed locally.  I can duplicate the beahvior.  Stepping thorough the code is a pain as I can't easily set a break point.

Any suggestions on how to proceed?

I'm stumped.

[MikeSpragg]

Can you post the bits of code that actually gets (finds) & sets the new ID. Basically, the index 'ACCOUNTTERRITORYASSIGNMENT_PRIMARY' is set to disallow duplicate primary IDs. This is correct. But, if you are somehow getting an ID that already exists then the statement will fail as expected. Doesn't explain why normal users work - but, until we can see that part - shooting in the dark.

[RJLedger]

Why bother to do a 1:1 - just add the fields to the OOTB Account table and all your problems go away.

This is how most of us have been doing it since the release of Scorpion (v7.0) - avoid 1:1 big time!

[tdowning@solidconcepts.com]

I'll try to post only the relevent code.

ta is a form level variable for my TerritoryAssignment Class

From onchange in ACCOUNT: TerritoryAssignment form

Sub AXFormChange(Sender)
    bIsSaved = True
    ta.AccountID = Application.BasicFunctions.CurrentAccountID
    blnIsOverride = chkIsOverride.Checked
'IF NO ACCOUNT MANAGER IS IS ASSIGNED, THEN SET THE DEFAULTS
    If txtAcctMgrID.Text = "" Then
        'get the default value from the account address
'THIS CALL IS WHERE THE TROUBLE BEGINS
        ta.AssignDefaultValues
        Form.Refresh
    End If

    Dim objRS, strSQL
    Set objRS = objSLXDB.GetNewRecordSet
'set the salesassociate
    If txtSalesAssociateID.Text = "            " Or txtSalesAssociateID.Text = "" Then
        gStrSalesAssocText = ""
        ppeSalesAssoc.Text = gStrSalesAssocText
    Else
        strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'"  'TL Downing
        objRS.Open strSQL, objSLXDB.Connection
        If Not (objRS.BOF And objRS.EOF) Then
           ppeSalesAssoc.Text = objRS.Fields("USERNAME").Value
        Else
           ppeSalesAssoc.Text = ""
        End If
        objRS.Close
        gStrSalesAssocText = ppeSalesAssoc.Text      'The Lookup ID for the Sales Associate
    End If

    strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtAcctMgrID.Text) & "'"  'TL Downing
    objRS.Open strSQL, objSLXDB.Connection
    If Not (objRS.BOF And objRS.EOF) Then
       ppeAcctMgr.Text = objRS.Fields("USERNAME").Value
    Else
        'get the de
       ppeAcctMgr.Text = ""
    End If

    strAcctMgrID = txtAcctMgrID.Text
    strOwnerID = lueOwner.LookupID
    strReason = memOverrideReason.Text
    strSalesAssocID = txtSalesAssociateID.Text

    SetDefaultOverride ()

    'set the warning label visibility property
    ShowHideWarning

End Sub

ta.AssignDefaultValues

    Public Sub AssignDefaultValues
        If blnDefaultValuesSet = False Then
            SetDefaultValues 'this is working
        End If
        ValidateAccountManagerID 'this is working
        If strAccountID = "" Then 'there is nothing to assign yet
            Exit Sub
        End If
        Dim objRS, SQL
'check to see if an account level territory assignment record exists
        SQL = "SELECT * FROM sysdba.ACCOUNTTERRITORYASSIGNMENT WHERE ACCOUNTID = '" & strAccountID &"'"
        Set objRS = objSLXDB.GetNewRecordset
        objRS.Open SQL, objSLXDB.Connection
'if no assignment, then
        If objRS.RecordCount = 0 Then
            'insert the accountterritotyassignment record
            InsertDefaultAccountTerritoryAssignment 'This is the code that is failing
        ElseIf objRS.RecordCount = 1 Then
            'update the accountterritotyassignment record
            UpdateDefaultAccountTerritoryAssignment
        'ElseIf objRS.RecordCount > 1 Then 'we have an error there should be 1 and only 1 account territoryassignment record per account, this is a 1 to 1 relationship
            'update the accountterritotyassignment record
        End If

        Set objRS = Nothing

    End Sub

 InsertDefaultAssignment, this is the code that throws the error

    Public Sub InsertDefaultAccountTerritoryAssignment
        Dim SQL

        SQL = "INSERT INTO sysdba.ACCOUNTTERRITORYASSIGNMENT (ACCOUNTID, ACCTMGRID, OVERRIDEREASON, " & _
        "SECCODEID, ISOVERRIDE, SALESASSOCIATEID" & _
        ") VALUES ('" & strAccountID & "', '" & strDefaultAccountManagerID & "'," & _
        "'','" & strDefaultOwnerId & "','F','" & strSalesAccociateID &"')"

        objSLXDB.ExecuteSQL SQL

    End Sub

[tdowning@solidconcepts.com]

<rant>

Because the powers that be would like to perform the following

Have the ability to override the account manager and owner (And be able to tell when different thatn the defaults"

we also have 2 sales associates 1 at the account level and 1 at the territory level.

so now I have 3 account manager fields, three owner fields and 2 sales associates fileds sometimes with differetn data.  This makes appending the account table rather tedious.  I did give it some thought.

Sometimes we have to do what we are told.....

whether we like it or not.  

Personally, I'd do away with all of the silly territory assignment customizations if I could. Major effort with little return on investment (if any).  But peole that get paid a lot more than I do are making the call here, so I just try to implement as painlessly as possible

<rant/>

all better.

[MikeSpragg]

Ah. You can't use the OnChange for the form - it *will* fire multiple times (always has) - so your insert will repeatedly fire. Also, try to avoid direct SQL inserts.

Use this instead:

===================
Inserting a record 
===================

    Dim objSLXDB, objRS, strSQL
    Set objSLXDB = New SLX_DB

       Set objRS = objSLXDB.GetNewRecordSet
       strSQL = "SELECT * FROM TABLE WHERE 1=0" ' Setup RS

       objRS.Open strSQL, objSLXDB.Connection
       objRS.AddNew

          With objRS
                  .Fields("CONTACTID").Value = ConID
                  .Fields("Q1").Value = "F"
                  .Update
                  If ErrorCheck (Application.Translator.Localize("Error updating...")) > 0 Then exit sub
		  .Close
          End With

       Set objRS = Nothing

 It's easier to read and edit later.

Do you know what index the primary is failing with - as any of the these values could be duplicated.

    Public Sub InsertDefaultAccountTerritoryAssignment
        Dim SQL

        SQL = "INSERT INTO sysdba.ACCOUNTTERRITORYASSIGNMENT (ACCOUNTID, ACCTMGRID, OVERRIDEREASON, " & _
        "SECCODEID, ISOVERRIDE, SALESASSOCIATEID" & _
        ") VALUES ('" & strAccountID & "', '" & strDefaultAccountManagerID & "'," & _
        "'','" & strDefaultOwnerId & "','F','" & strSalesAccociateID &"')"

        objSLXDB.ExecuteSQL SQL

    End Sub

 But, this needs re-wrting anyway - to use above method.

Finally, if you want to continue AxFormChange (you shouldn't) then wrap it all with

If Not frmName.IsReading Then

End If

 This will ensure that the multiple fires will be avoided and your code executes only after everything else is loaded/bound.

[tdowning@solidconcepts.com]

Thanks, 

I'll refactor as you describe.

I understand your caution re: not using OnChange.

But when the user changes to the next record, I need to populate and check things (Like does the Account Territory Assignment match what is actually on the account?).

What should I use?

[MikeSpragg]

Some other hints. Use the SLX Database include - then you can re-write a lot of this code:

 strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'"  'TL Downing

 Becomes:

uName = GetField("IsNull(USERNAME,'')","USERINFO","USERID='" &  txtSalesAssociateID & "'")

 No need to get/set RS - easier to work with.

Also:

    If txtSalesAssociateID.Text = "            " Or txtSalesAssociateID.Text = "" Then

Simpler as:

If trim(txtSalesAssociateID) = ""

It'll be null anyway if you use the other code.

    If txtSalesAssociateID.Text = "            " Or txtSalesAssociateID.Text = "" Then
        gStrSalesAssocText = ""
        ppeSalesAssoc.Text = gStrSalesAssocText
    Else
        strSQL = "Select USERNAME From USERINFO Where USERID = '" & Trim(txtSalesAssociateID.Text) & "'"  'TL Downing

[MikeSpragg]

OnChange is fine for things like that (to setup the form, data etc). Just try to avoid using updates within it. Use BeforeRecordChange or axFormValidate to update it after the event, not during.

[RJLedger]

You wrote "... This makes appending the account table rather tedious  .."

Appending the account table is simply using the SalesLogix DB Manager and adding the fields. The tedious part is having to write special code to handle the 1:1 business.

NOTE: If you use the web app.. or SData for the mobility.. 1:1 complicates things.