6/18/2025 8:28:14 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.
|
|
|
|
Custom Association Table
Posted: 08 May 07 6:49 PM
|
fiogf49gjkf0d Hello All,
I have a Custom table called Relationship simmilar to the association table.. I have 2 fields on that table P_ACCTNUM and C_ACCTNUM; the key field on the account table is the INTERNALACCOUNTNO field.
when I create a data grid and display these two fields using INTERNALACCOUNTNO as the bindID it works fine.. now, I want to add a 3rd feild to the data grid "child Acccount name" , I create a local join to the Account table and link the C_ACCTNUM back to the account table (INTERNALACCOUNTNO ) to display the account name.. sounds simple , however it does not work for .. it gives me an error saying invalid join..
Any help is appricicated
|
|
|
|
Re: Custom Association Table
Posted: 08 May 07 8:03 PM
|
fiogf49gjkf0d Although InternalAccountNo may be your primary key, it is not recognised as such by SLX.
I would recommend storing the standard SLX AccountID field as your FK in all other tables that link to account. That's the way SLX was designed to work.
|
|
|
|
Re: Custom Association Table
Posted: 09 May 07 10:02 AM
|
fiogf49gjkf0d Maybe it was designed that way, but shouldn't it still work as a SQL statement? David, could you post the SQL statement from the grid after adding the faulty join? |
|
|
|
Re: Custom Association Table
Posted: 09 May 07 6:07 PM
|
fiogf49gjkf0d In my reply, I was making the assumption that, as neither of the join fields was recognised by SLX as PK or FK, the grid was throwing the error. However, I've just tried joining a couple of very unlikely fields and had no problems.
That's another way of saying that I haven't got a clue  |
|
|
|
Re: Custom Association Table
Posted: 10 May 07 4:38 PM
|
fiogf49gjkf0d below is the Query ; The first Query work.. it displays all the child account no but when I join the C_ACCTNUM back to the Account table it retuns no records
P.S. I use the INTERNALACCOUNTNO as the bind variable
SELECT A1.FOCI_RELATIONSHIPID, A1.C_ACCTNUM, A1.CREDITLIMIT FROM FOCI_RELATIONSHIP A1 WHERE A1.P_ACCTNUM = :BindID
SELECT A1.FOCI_RELATIONSHIPID, A1.C_ACCTNUM, A2.ACCOUNT A2_ACCOUNT, A1.CREDITLIMIT FROM FOCI_RELATIONSHIP A1 LEFT OUTER JOIN ACCOUNT A2 ON (A1.C_ACCTNUM=A2.INTERNALACCOUNTNO) WHERE A1.P_ACCTNUM = :BindID |
|
|
|
Re: Custom Association Table
Posted: 10 May 07 4:58 PM
|
fiogf49gjkf0d I have an idea of what the problem might be - your SQL statement looks good, except for one possibility. To perform the join, I think SQL server needs the fields being joined to be the same data type. In your statement, if A1.C_ACCTNUM is a numeric type field like an int, then that might be the reason for the failure, as I checked, and INTERNALACCOUNTNO is a varchar. I tried a join between an int field and a varchar, and I got the following error: "Conversion failed when converting the varchar value 'A6UJ9A00CIJ4' to data type int." You'd think it would convert the other way around, converting the int field to a varchar, but it didn't. If you haven't already, try running the statement in SQL Query Analyzer or Management Studio. I'm using SQL Server 2005, so if you are using 2000, you might get a different error message.
Just for kicks, I decided to convert the int field being joined to see if it would work correctly, and it did. Here's the statement I used first:
SELECT Count(*) FROM Account A JOIN Activity C ON A.AccountID = C.Type (Didn't work)
And the statement with the conversion:
SELECT Count(*) FROM Account A JOIN Activity C ON A.AccountID = Cast(C.Type As VarChar) (Worked correctly)
Of course, you wouldn't be able to do that in a datagrid in SalesLogix. As far as a solution is concerned (if what I mentioned above is the cause of the error), you may need to create or find a field of type int, and use that to store the external ID instead of using INTERNALACCOUNTNO. That could solve the problem, I think. Let us know how it goes. |
|
|
|
Re: Custom Association Table
Posted: 10 May 07 6:24 PM
|
fiogf49gjkf0d both the fields are of the same datatype; and my joins works corectly on SQL server.. its just on the SLX client I am having issues. I have come up with a working workaround. but, I would like to find out if I have a simple solution
My workaround for this issues
1) I created a new table in Archiet called Relationship_V with the same fields as the relationship table
2) I went to enterprise manager and droped that table
3) I created a view with the same name Relationship_V that has the join buit in it
4) now I use the Relationship_V in SLX and it works fine
|
|
|
|
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!
|
|
|
|
|
|
|
|