6/20/2025 6:35:35 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Data in custom table not visible to remote users (even when logged on to network client)
Posted: 24 Aug 07 10:57 AM
|
Hi,
I created a customisation (on SLX V7) to generate a unique quote id as soon as a new opportunity is entered. This unique ID is kept in a new database table with a one-to-one link to the OPPORTUNITY table.
To get the next unique number, I run the following script:
"SELECT (max(convert(int, ISNULL(RIGHT(QUOTEID, 6),0)))+ 1) as NEXT_NUMBER from SYSDBA.ABC_OPPORTUNITY_QUOTE where QUOTEID LIKE '" & lstrSitePrefix & "%'"
When I log on as the Admin user, this script works fine. It also works perfectly if I run it directly on the database. However, when I log onto the SalesLogix client as a remote user, the script returns a null value. Now I do make provision for a null value for in case the specific user (or site) has not generated a quote before. In that case I assign the number 1.
Now for the weird thing: Then next step in the script is to write this new number to the table. After the record for OPPORTUNITY is created, I create a record for
SYSDBA.ABC_OPPORTUNITY_QUOTE, and then update the record with the new sequential number. This code works, which means the remote user do have access to this table. The code is as follows:
'Create a new table entry for opportunity quote
lstrSQL = "INSERT INTO SYSDBA.ABC_OPPORTUNITY_QUOTE (OPPORTUNITYID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE) SELECT OPPORTUNITYID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE FROM sysdba.OPPORTUNITY WHERE OPPORTUNITYID = '" & Application.BasicFunctions.CurrentOpportunityID & "'"
and
'Update system with unique quote ID.
lstrSQL = "UPDATE SYSDBA.ABC_OPPORTUNITY_QUOTE SET QUOTEID = '" & lstrNextNumber & "' WHERE OPPORTUNITYID = '" & Application.BasicFunctions.CurrentOpportunityID & "'"
When I log in as user Admin, the first time a number e.g. Q00001 is allocated, and from then onwards the sequential number gets generated fine. Q00002, Q0003, etc. When I log in as the remote user (even when I log on to the network client as the remote user), I get the number Q00001 inserted into every SYSDBA.ABC_OPPORTUNITY_QUOTE.
What really baffles me is that it seems the remote user does not have access to the SYSDBA.ABC_OPPORTUNITY_QUOTE table when trying to read the max number in order to get the next sequential number. However, the next step of the script writes an insert and an update to that same table, and then it works fine!
Any ideas would really be appreciated.
Thanks very much!
Ina
PS: If it will help, I will publish all the code - it's not huge
|
|
|
|
Re: Data in custom table not visible to remote users (even when logged on to network client)
Posted: 24 Aug 07 11:54 AM
|
Few things....
I don't think creating your own id's this way will work. Because...if the network users are creating quoteid's and the remote user has not synced up the newly created quoteid's then the remote will create quoteid's based on the last id he/she received. If netuser1 creates Q00001 thru Q000010 and the remote users last quoteid is Q00007 then you will have problems because when he/she creates a new quoteid it will be Q00008. So now remuser1 has a Q00008 and netuser1 has Q00008....PROBLEM. You would have to create some kind of who win's rule and then assign a new quoteid for the duplicates when they happen. I would also make your quoteid column unique.
As far are your real problem I think the IstrSitePrefix variable may not pulling the correct id for a remote user. |
|
|
|
Re: Data in custom table not visible to remote users (even when logged on to network client)
Posted: 24 Aug 07 1:02 PM
|
Hi there,
Thanks for your prompt reply. I have thought about the whole duplicate keys thing, and the way I work around that is as follows:
I found that SalesLogix assigns unique opportunity id's based on the site id. You get the site prefix in the case of remote offices on the offices, or in the case of remote users on the user record in SLX Administrator. I then created a custom table called ABC_QUOTEPREFIX_SITEID. This table holds the prefix to be used for the site. I create the unique quote ID after the new opportunity id is created. Character 2-5 of the opportunityid is the site ID. I then check which prefix to use for this opportunity (which will be unique for every remote user or site). In the case that there is no mapping set up, I automatically assign a "Z" as a prefix. The users will be made WELL aware that if they get a Z id it will be VERY possible to have a duplicate, and therefore not to use it. It is at that point that I use the SQL code to find the next sequential number for the specific site ID
"SELECT (max(convert(int, ISNULL(RIGHT(QUOTEID, 6),0)))+ 1) as NEXT_NUMBER from SYSDBA.ABC_OPPORTUNITY_QUOTE where QUOTEID LIKE '" & lstrSitePrefix & "%'"
the lstrSitePrefix is the prefix to be used for the specified site. This then brings me to your suggestion as to the possible problem. lstrSitePrefix is not the problem, because it is determined based on the site. My main site's prefix is mapped up as "Q". So when I am logged in on a network client as Admin, I get the Q prefix, and the sequential numbers gets mapped up correctly for each subsequent opportunity. However, when I log in as the remote user I get a null, which means every time I get Q00001. (Because I am on the network client the same siteID is used and I get the same prefix.)
I must admit, this one really baffles me, as I have the same thing working quite well on another site.
I will just after this, post my full code - just in case it helps to find the problem. This post should then describe the references to the map table
|
|
|
|
Re: Data in custom table not visible to remote users (even when logged on to network client)
Posted: 24 Aug 07 1:08 PM
|
Below is the full code that is added right at the end of sub SaveOpportunity(Sender) of the form System:Insert Opportunity
'###### Custom code added on 22 August 2007 by Ina Nortje - to auto generate a unique Quote ID for the opportunity as soon as it is created ######
Dim lstrSQL
Dim objCon
Dim objRS
Dim lstrNextNumber 'Next unique Quote ID
Dim lstrCurrentSite
Dim lstrSitePrefix
Dim lstrSitePrefixExists
'Get Site Prefix based on opportunityID
lstrCurrentSite = mid(Application.BasicFunctions.CurrentOpportunityID,2,4)
' msgBox "Site ID for this opportunity is: "& lstrCurrentSite, ,"Testing new customisation"
lstrSQL = "SELECT COUNT(QUOTEPREFIX) AS PREFIXEXISTS FROM SYSDBA.ABC_QUOTEPREFIX_SITEID WHERE SITEID = '" & lstrCurrentSite & "'"
' msgBox lstrSQL , ,"Testing new customisation"
set objCon = Application.GetNewConnection
set objRS = objCon.execute(lstrSQL)
lstrSitePrefixExists = objRS.Fields.Item("PREFIXEXISTS")
If lstrSitePrefixExists = 0 Then
lstrSitePrefix = "Z"
Else
lstrSQL = "SELECT QUOTEPREFIX FROM SYSDBA.ABC_QUOTEPREFIX_SITEID WHERE SITEID = '" & lstrCurrentSite & "'"
' msgBox lstrSQL, ,"Testing new customisation"
set objCon = Application.GetNewConnection
set objRS = objCon.execute(lstrSQL)
lstrSitePrefix = objRS.Fields.Item("QUOTEPREFIX")
' msgBox "Prefix is: " & lstrSitePrefix, ,"Testing new customisation"
End if
'Get new unique quote ID
lstrSQL = "SELECT (max(convert(int, ISNULL(RIGHT(QUOTEID, 6),0)))+ 1) as NEXT_NUMBER from SYSDBA.ABC_OPPORTUNITY_QUOTE where QUOTEID LIKE '" & lstrSitePrefix & "%'"
' msgBox "SQL Query for new tripid is: " & lstrSQL, ,"Testing new customisation"
set objCon = Application.GetNewConnection
set objRS = objCon.execute(lstrSQL)
lstrNextNumber = objRS.Fields.Item("NEXT_NUMBER")
if isnull(lstrNextNumber) then lstrNextNumber = "1"
' msgBox "Next number is " & lstrNextNumber, ,"Testing new customisation"
objCon.Close
set objCon = Nothing
select case len(lstrNextNumber)
case 1
lstrNextNumber = lstrSitePrefix & "00000" & lstrNextNumber
case 2
lstrNextNumber = lstrSitePrefix & "0000" & lstrNextNumber
case 3
lstrNextNumber = lstrSitePrefix & "000" & lstrNextNumber
case 4
lstrNextNumber = lstrSitePrefix & "00" & lstrNextNumber
case 5
lstrNextNumber = lstrSitePrefix & "0" & lstrNextNumber
case 6
lstrNextNumber = lstrSitePrefix & lstrNextNumber
end select
' msgBox "Composite next number is " & lstrNextNumber, ,"Testing new customisation"
'Create a new table entry for opportunity quote
lstrSQL = "INSERT INTO SYSDBA.ABC_OPPORTUNITY_QUOTE (OPPORTUNITYID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE) SELECT OPPORTUNITYID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE FROM sysdba.OPPORTUNITY WHERE OPPORTUNITYID = '" & Application.BasicFunctions.CurrentOpportunityID & "'"
set objCon = Application.GetNewConnection
set objRS = objCon.execute(lstrSQL)
objCon.Close
set objCon = Nothing
'Update system with unique quote ID.
lstrSQL = "UPDATE SYSDBA.ABC_OPPORTUNITY_QUOTE SET QUOTEID = '" & lstrNextNumber & "' WHERE OPPORTUNITYID = '" & Application.BasicFunctions.CurrentOpportunityID & "'"
set objCon = Application.GetNewConnection
set objRS = objCon.execute(lstrSQL)
objCon.Close
set objCon = Nothing
'Refresh the Opportunity main view so the new Quote Number displayes
For i = 0 to Application.MainViews.Count - 1
If Application.MainViews.Item(i).BaseTable = "OPPORTUNITY" Then 'DNL
Application.MainViews.Item(i).Refresh
End If
Next
'###### End of custom code added on 22 August 2007 by Ina Nortje - to auto generate a unique Quote Number for the opportunity as soon as it is created ######
|
|
|
|
Re: Data in custom table not visible to remote users (even when logged on to network client)
Posted: 30 Aug 07 3:32 AM
|
I found a way past this problem by adding my custom field directly to the OPPORTUNITY table. It seems that the custom table for some reason isn't visible to all users. I also added the field in that table to the opportunity form, and it also didn't display for any user other than Admin.
So my problem is now solved, but I would still like to know why it may be that this table wasn't visible to any other users. When I created the table, I did check the box that said sync to remote.
Thanks
Ina |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|