11/26/2024 6:21:29 PM
|
| |
| 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.
|
|
|
|
Sequence Number field
Posted: 22 Sep 06 9:34 AM
|
fiogf49gjkf0d I am developing a streamline process for my Customer Information department. Part of this process will be when they change the Account Stage to 'Merged' I will (via script) move the branch records from the now 'Merge'd account to the new account the branch records are to be associated with by running an update statement to set the AccountID from the previous account to the new accountid. The Branch table is a one-to-many with the account table. Currently, in the branch table is a field called Branch Seq Number. Basically this is a numeric value that is manually updated (sequentially) for internal purposes. The sequence starts at 0 with that being the Main account branch record. When I make this branch 'move' I need to find the max sequence number of the new account and renumber the branches from the merged account sequentially.
So, my Merged account has 3 branch records with the sequence numbers of 0,1,2.
The account that these branches are moving to currently has branch sequence numbers 0-25. I need to somehow change the 0,1,2 values to 26,27,28 for the new account.
Here is what I currently have for the code that moves the branches, etc. NOTE: The Update SQL Statement is not complete yet as I am still trying to figure out how to update the sequence Number!! Thanks....and hope I didn't confuse anyone with my question/explanation!!
Sub pklstageChange(Sender)
Dim strStageMsgBox
Dim strResult
Dim strNewAcctID, strNewAcct
Dim objBranches
if strStage <> "Merged" and pklStage.Text = "Merged" then
strStageMsgBox = Msgbox("You have changed the Stage of this account from '" & strStage & "' to 'Merged'. " & _
"Do you wish to move the associated branches to the new account?",52,"Move Branches Confirmation")
If strStageMsgBox = 6 then
strResult = Application.BasicFunctions.DoInvoke("Form","System:MoveBranches")
strResult = application.basicfunctions.InvokeResult
if strResult = "mrOK" then
strNewAcctID = getfield("AccountID","C_Account","Customer_ID = '" & Application.GlobalInfo.Item("BranchMoveCustID") & "'")
strNewAcct = getfield("Account","Account","AccountID = '" & strNewAcctID & "'")
set objBranches = CreateObject("ADODB.Recordset")
set objBranches.ActiveConnection = Application.GetNewConnection
objBranches.Open "Update C_Branch set AccountID = '" & strNewAcctID & "', Branch_Name = '" & strNewAcct & "', MB_Unique_ID = " & _
NULL & ", CPU_Here = 'F', Branch_Seq =
else
msgbox "CANCEL"
end if
end if
' else
' Msgbox "Here2"
end if
End Sub |
|
|
|
Re: Sequence Number field
Posted: 23 Sep 06 6:34 AM
|
fiogf49gjkf0d Hi Sarah,
Hoping I understand your question correctly.
It seems you just need to make a call to your C_Branch table first to determine the existing MAX Branch_Seq value, something like (note not all code is shown):
SELECT MAX(Branch_Seq) AS 'MaxBranchSeq' FROM C_Branch WHERE AccountID = " & strNewAcctID
Execute this SQL, then assign a variable intNextBranchSeq to the next value, e.g.
intNextBranchSeq = objMaxBranchSeqRS("MaxBranchSeq") + 1
As you loop through the objBranches recordset to perform the update, you'll increment the counter each time through the loop, then use this value in your update statement.
Do While Not objBranches.EOF
intNextBranchSeq = intNextBranchSeq + 1
...
Loop
|
|
|
|
Re: Sequence Number field
Posted: 24 Sep 06 10:55 PM
|
fiogf49gjkf0d Hi Sarah,
While merging records in new table who have to first find out max no. by writing SQL statement.
eg. Select max(no) as no from table_name. and stored this value in a variable. Then fetch all records for selected criteria and create a recordset. Increament by this variable by one while move this recordset in loop. You have to increment this variable before move to next record.
Eg.
Select * from table_name where condition
set obj = CreateObject("ADODB.Recordset")
set obj.ActiveConnection = Application.GetNewConnection
variable = variable + 1
Do until obj.Eof
insert statement to save new records
obj.movenext
variable = variable + 1
loop
set obj = nothing
i think you get your solution. If not just let me know.
|
|
|
|
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!
|
|
|
|
|
|
|
|