6/19/2025 4:31:46 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.
|
|
|
|
Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 29 Jun 07 12:33 PM
|
Ok here is my code:
----------------------------------
strCustCount2 = "SELECT accountid FROM account WHERE type like '%Customer' and status = 'Active'"
function GetIDs(strSQL)
dim objConn
dim objRS
strAccountID = ""
On Error Resume Next
Set objConn = New DataConnection
objConn.Initialize
Set objRS = objConn.ReturnRecordset(strSQL)
While NOT objRS.EoF
strAccountID = strAccountID & objRS.Fields(0).Value & chr(13) & chr(10)
objRS.MoveNext
Wend
objRS.Close
Set objRS = Nothing
objConn.Terminate
Set objConn = Nothing
end Function
Sub btnCustCountClick(Sender)
GroupCustCount
End Sub
sub GroupCustCount
GetIDs(strCustCount2)
StartingID = ""
Application.BasicFunctions.CreateTempAdHocGroup "ACCOUNT", "Current Customers", strAccountID, StartingID
End Sub
------------------
This works fine when the record count is low, but I have one that hits 3000+ and it blows up. Is there a better way to populate my IDList variable (strAccountID) than this?
While NOT objRS.EoF
strAccountID = strAccountID & objRS.Fields(0).Value & chr(13) & chr(10)
objRS.MoveNext
Wend |
|
|
|
Re: Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 02 Jul 07 8:25 AM
|
I'm guessing my variable: "strAccountID" is getting too big. Can I set the variable to a specific type that might work?
EDIT:
I figured out a different way. I am using the Application.BasicFunctions.CreateTempGroup function instead:
Application.BasicFunctions.CreateTempGroup "ACCOUNT", "Current Cust", "ACCOUNT:TYPE", "LIKE", "'%Customer' AND status = 'Active'", ""
I had to fudge the 'WhereValue' to include my additional AND statement, but it worked. |
|
|
|
Re: Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 02 Jul 07 9:29 AM
|
Hi Marc,
Not sure what you mean by "blows up" but string concatenation in VB Script is not very inefficient so it may just be a performance problem.
You can't set the variable to a different type as everything is a variant.
Rather than your while loop you could try this:
strAccountID = objRS.GetString(adClipString)
That should be a fair bit faster than the string concatenation in the original code.
Hope this helps.
Darron |
|
|
|
Re: Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 02 Jul 07 9:59 AM
|
Darron,
The loop wasn't the problem. The problem is that I'm 'filling up' my variable. I was able to get around a simple select with the CreateTempGroup function, but now I'm faced with a more complex one that has some joins. I'm not sure how to fix this. I don't beleive the CreateTempGroup parameters will be flexible enough for me to create a join. And the CreateTempAdHocGroup ID list is going to be too large again in a couple instances.
So I'm stuck again. Ugh.
Here is the SQL statement that I need to either shoehorn into the CreateTempGroup function or figure out a better way of adding the accountIDs into the ID parameter of CreateTempAdHocGroup:
SELECT * FROM account a, (select ap.accountid, ap.productid from accountproduct ap group by ap.accountid, ap.productid) ap, product p where a.accountID = ap.accountID and ap.productid = p.productid and a.type like '%Customer' and a.status = 'Active' and p.family = 'Web' |
|
|
|
Re: Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 02 Jul 07 10:35 AM
|
Hi Marc,
You aren't filling up the variable. It's using a fair bit of memory but it's certainly not full.
If I understand your SQL correctly you want a list of accounts that have products with the family 'Web'. It may be possible to do this with a subquery in the CreateTempGroup. I haven't tested this but something along the lines of the following should do the trick
Dim sWhere
sWhere = "(SELECT a.accountid FROM account a " & _
"JOIN accountproduct ap ON ap.accountid = ap.productid " & _
"JOIN product p ON p.productid = ap.productid " & _
"WHERE a.type like '%Customer' and a.status = 'Active' and p.family = 'Web')"
Application.BasicFunctions.CreateTempGroup "ACCOUNT", "Current Cust", "ACCOUNT:ACCOUNTID", "IN", sWhere, ""
Darron |
|
|
|
Re: Best way to populate the IDList for CreateTempAdHocGroup???
Posted: 02 Jul 07 1:17 PM
|
Ok THIS works...
Dim sWhere
sWhere = "(SELECT distinct a.accountid FROM account a " & _
"JOIN accountproduct ap ON a.accountid = ap.accountid " & _
"JOIN product p ON p.productid = ap.productid " & _
"WHERE a.type like '%Customer' and a.status = 'Active' and p.family = 'CBS')"
Application.BasicFunctions.CreateTempGroup "ACCOUNT", "CBS Cust", "ACCOUNT:ACCOUNTID", " IN ", sWhere, ""
=============
The IN has to have spaces on either side or it ends up like, "A1.ACCOUNTIDIN(Select..."
Thanks! |
|
|
|
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!
|
|
|
|
|
|
|
|