Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011

Re: Can not insert data on remote database custom table

RJ Ledger wrote

 

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

 

Actually

 

I found the 1:1 relationship rather simple in the web side of things

 

Custom business rules are firing exactly as expected no errors or exceptions.  Working beautifully, couldn't be happier.

 

Only issue right now is the remote user not being able to insert the way they should.

 

Besides, what's done is done.  Undoing involves way too much extra for me right now.  BTW I have 3 other custom applications using SData so refactoring the data isn't going to happen anytime soon.

 

I really don't want to enjoin in "Religious" debate where technology is concerned.  You have your way, I have my way, both are valid and both have issues.

 

Thanks for your input.

 

Highlighted
Bronze Super Contributor
Posts: 129
Registered: ‎06-09-2009

Re: Can not insert data on remote database custom table

Going back to the Original error, this may be caused as well if you have a SECCODEID column on the Related Table and if the SeccodeID on the Account Table is different than the one on the Related record AND....  The remote user doesn't have access to data owned by the SeccodeID on the Related Table.

 

For Example, Account ABC and its related Record are owned by a Team called "Restricted".

You realign Account ABC  (this typically doesn't realigned the Related Record).

 

User 1 gains access to Account ABC due to the Realignment.

 

When he logs in, SalesLogix reads the data. SalesLogix applies Security, so it gets a Record Count of Zero for the Related Record.

At some point, the code attempts to do an INSERT. Now, for all SalesLogix Reports there are no records, so this is the "correct" thing to do, however, there is an actual record on the Database that matches the ID. This is why you would receive a PK error.

 

As you can tell, the PK error comes straight from the DB. SalesLogix security prevented your Code from knowing that this record existsed, and it even allows the "Insert" to go through the PRovider. It is the Database Engine that rejects it.

 

Raul A. Chavez
http://raul.chavez.com
http://www.crmbi.com
Highlighted
Nickel Elite Contributor
Posts: 107
Registered: ‎03-09-2011

Re: Can not insert data on remote database custom table

[ Edited ]

Ok, I figured out what was wrong here.

 

Raul is partly correct.

 

I have a routine that looks up a value from the USEROPTIONS Table 'InsertSeccodeID' for an assigned Account Manager.

 

Unfortunately when the USEROPTIONS table is replicated to a remote the remote database it ONLY has the records for the user that the remote database is created for.

 

This lack of data was causing the problems when attempting to get a "Default Owner" for an Account Manager that is NOT the logged in user.

 

The lack of data was driving an owner to be set for the account that the remote user did not have access to so it would disappear from the users view.

 

I changed the strategy to get the default ownership from a different table.  All is working fine now.

 

Here is how I figured out the issue. I created versions of the plugin with stop statements in the code and released this to our staging environment which has a sync server that can be turned on to create remote databases from our staging environment.

 

I Created a remote database with the plugins with stop statements, copied it to my local computer and attached it to my SQL server. I then created a connection to the remote database and was able to log in as the remote user.  Set the user debug options and trigger the offending plugin, break into a debugger when the stop staement was encountered and walk through the code.

 

Once I determined what was going on and that the USEROPTIONS table was incomplete on remote databases finding the fix was relatively easy.