7/8/2025 6:32:39 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.
|
|
|
|
Tree View
Posted: 09 Dec 09 7:28 AM
|
| Is there a way to capture all of the accountid's related to accounts in a tree view in order to use all of them in one sql statement? |
|
|
|
Re: Tree View
Posted: 09 Dec 09 8:21 AM
|
Sure, there are many ways to do so.
Never the less, my question here would be what "related" accounts means to you. Are you going after Parent/Child Accounts or are you looking for Associatied accounts?
Or is this based on some custom Relationship that you have built?
If the SQL Statement is too complex and/or SLX won't parse it, I would suggest that you build it into an SQL View, Enable that view into SLX, and then consume it from within the client.
E.g.
CREATE VIEW v_RelatedAccounts AS
...
...
Then, depending on how your view is built, you could do something as simple as "SELECT * FROM v_RelatedACcounts WHERE ACCOUNTID = 'Current Acct ID'" |
|
|
|
Re: Tree View
Posted: 09 Dec 09 8:36 AM
|
Thanks for your reply Raul, basically we are building a datagrid that will display all products that have been purchased by the accounts. We have replicated the SLX_Manage_Hierarchy form for this and are customising it.
What we want is when an account in the tree view is selected the datagrid will show the total revenue from each product that that account has purchased. But we also want it to sum the products that have been purchased by its child accounts. At the moment we have this working for a one parent --> many children scenario
but not for Main Parent --> Sub Parent --> children. We do this through sql using the accountid of the selected account to find its products and also where is also appears as the parentid in order to get its childrens products.
However, if one of the accounts children is ALSO a parent to another account (Main Parent --> Sub Parent --> children) then our current/main parent accountid will not be the parentid of the end child or children which means its product/s are not added to the datagrid.
This would be solved if we could get all of the accountid's of every account in the tree view.
|
|
|
|
Re: Tree View
Posted: 09 Dec 09 8:54 AM
|
Unfortunately, there are no constraints in place that limit how many levels deep the Parent/Child relationship can be built.
That being said, the best way to achieve this would be through Code:
- On Node Select, get the AcctID, then resolve the relationships by querying each account. (Also, make sure you add some checks to prevent an Infinite Loop just in case you have a Closed Loop on the Relationship).
Best bet would be to create an Array of the AcctIDs and then use that array to go after the products.
Otherwise, you would have to put a limit as to how many levels down you want your code to work for. For instance, the following statement gets up to 3 levels down:
SELECT A.PARENTID, ACCOUNTID AS CHILDID FROM ACCOUNT
UNION
SELECT A.PARENTID, C2.ACCOUNTID AS CHILDID FROM ACCOUNT A
INNER JOIN ACCOUNT C2 ON A.ACCOUNTID = C2.ACCOUNTID
UNION
SELECT A.PARENTID, C2.ACCOUNTID AS CHILDID FROM ACCOUNT A
INNER JOIN ACCOUNT C2 ON A.ACCOUNTID = C2.PARENTID
INNER JOIN ACCOUNT C3 ON C2.ACCOUNTID = C3.PARENTID
ParentID is the Root, and then you get up to 3 levels down from the statement shown above. You could place it on a View, then call it by just passing the ParentID, and it will return you all the IDs that are associated to it downstream.
Bottom line, I believe you should be looking for a Recursive function to build you this relationship. Maybe you can do this within SQL by using a Function to return you a Table and embed that into a View. (off course, then you need to figure out how that would work across remotes. |
|
|
|
Re: Tree View
Posted: 09 Dec 09 12:34 PM
|
here's one way to do it:
/****** Object: UserDefinedFunction [sysdba].[udf_GetRecursiveChildren] Script Date: 12/09/2009 12:33:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [sysdba].[udf_GetRecursiveChildren] (@QryID nvarchar(12))
RETURNS @RtnValue table (
CHILDID nvarchar(12)
, PARENTID nvarchar(12)
, RECURSIONLEVEL int
)
AS
BEGIN
DECLARE @NEXT nvarchar(12)
SELECT @NEXT ='';
WITH MYCTE(CHILDID, PARENTID, RECURSIONLEVEL) AS (
-- GET ANCHOR MEMBERS (TOP-LEVEL)
SELECT
ACCOUNTID AS CHILDID
, PARENTID
, 0 AS RECURSIONLEVEL
FROM sysdba.ACCOUNT (NOLOCK)
WHERE PARENTID =@QryID
--ACCOUNTID =@QryID -- SETTING ANCHOR AS PARENT ELIMINATES INCLUDING "SELF"
-- IN RESULTS, NOT OPTIMAL FOR SITUATION WHERE OPP IS AT SCHOOL SITE.
-- 10/16/2008: REVERTED TO PARENTID =@QryID to solve cyclical problem.
UNION ALL
SELECT
A.ACCOUNTID AS CHILDID
, A.PARENTID
, MYCTE.RECURSIONLEVEL + 1 AS RECURSIONLEVEL
FROM sysdba.ACCOUNT A (NOLOCK)
INNER JOIN MYCTE
ON A.PARENTID = MYCTE.CHILDID
WHERE A.ACCOUNTID <> MYCTE.PARENTID -- DO NOT JOIN TO CHILDREN IF CYCLICAL RELATIONSHIP EXISTS
)
INSERT INTO @RtnValue (
CHILDID
, PARENTID
, RECURSIONLEVEL
)
SELECT
CHILDID
, PARENTID
, RECURSIONLEVEL
FROM MYCTE
OPTION (MAXRECURSION 20)
RETURN
END |
|
|
|
Re: Tree View
Posted: 09 Dec 09 12:35 PM
|
Here's another way to do it.....
FUNCTION BuildParentAccounts_SQL(TID, PID)
'''' Recursively builds the SQL to return the Parents of the Parents of the Parents...... of an Account.
''' TID is This AccountID.
''' PID is the ParentID of this Account.
DIM aSQL
DIM Parent_AccountID
DIM This_AccountID
BuildParentAccounts_SQL = ""
IF TID = "" THEN EXIT FUNCTION
This_AccountID = TID
Parent_AccountID = PID
aSQL = "SELECT DISTINCT "
aSQL = aSQL & " A1.ACCOUNTID, A1.TYPE, A1.ACCOUNT, A1.PID, A1.ENROLLMENT, A1.LOW_GRADE, A1.HIGH_GRADE, "
aSQL = aSQL & " A1.PARENTID, A1.SUBTYPE "
aSQL = aSQL & " FROM ACCOUNT A1 "
aSQL = aSQL & " WHERE (A1.ACCOUNTID = '" & THIS_AccountID & "' ) "
IF Parent_AccountID > "" THEN
aSQL = aSQL & " OR (A1.ACCOUNTID = '" & Parent_AccountID & "' )"
While NOT (Parent_AccountID = "")
Parent_AccountID = "" & GetField("PARENTID", "ACCOUNT", "ACCOUNTID = '" & Parent_AccountID & "' ")
IF Parent_AccountID > "" THEN
aSQL = aSQL & " OR (A1.ACCOUNTID = '" & Parent_AccountID & "' )"
END IF
''' MSGBOX Parent_AccountID
WEND
END IF
aSQL = aSQL & " ORDER BY A1.ENROLLMENT ASC "
APPLICATION.Debug.WriteLine " BuildParentAccounts_SQL: " & aSQL
BuildParentAccounts_SQL = aSQL
END Function
|
|
|
|
Re: Tree View
Posted: 09 Dec 09 12:38 PM
|
| and I usually build the tree nodes as needed once I have the records involved..... |
|
|
| |
|
Re: Tree View
Posted: 18 Mar 10 12:34 PM
|
its been a while since this thread was visited but ive had an error executing the function on a particular account. The error says that the maximum recursion has been exhausted which i guess points to the line:
OPTION (MAXRECURSION 20)
Will there be any negative knock-on effect is this recursive limit is hightenend or optimally removed altogether? |
|
|
|
Re: Tree View
Posted: 24 Mar 10 3:54 PM
|
Infinite loops are entirely possible.....
SQL Timeouts are also possible.....
Bump it up to 100 on a DEV box and see what blows up.....hey, is that the fire engine siren I hear? |
|
|
|
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!
|
|
|
|
|
|
|
|