8/23/2025 1:29:24 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.
|
|
|
|
Calculated fields in slxv6.2.6
Posted: 09 Apr 08 1:52 PM
|
ok.....I am trying to count the number of records in a child table as 1:Many to the account in a calculated expression. Actually I have a few tables linked to the account I need to do this for. Why am I using calculated expressions.....the users would like to be able to see these calculations in the listviews and export to excel as well. I am not a big fan of storing summary values like this due to the volume of changes coming to these child tables.
So I have tried adding the calc field (in the manage calculated fields) to Count(childtableprimarykey) and I get all kinds of error messages when displaying the cal. field in an editbox on the account detail screen.
Can this be don with calculated fields?
Thanks,
Derek |
|
|
|
Re: Calculated fields in slxv6.2.6
Posted: 10 Apr 08 8:48 AM
|
ok I ended up creating a calculated field (manage calculated fields)on the account table with the following syntax.
(select Count(h1.historyid) from history h1 where h1.accountid = a1.accountid)
The a1.accountid is actually the alias that slx uses as a default when referencing the parent (account) table, you can also include other condition test on history type if you want to break it down.
Thanks,
Derek
|
|
|
|
Re: Calculated fields in slxv6.2.6
Posted: 10 Apr 08 10:21 AM
|
Here is some interesting results....
81,400 accounts, 880,000 history records, added 2 calculated fields one counting 262145(Meetings), second counting 262156(db changes) per account record. When you put these in the group layout as opposed to not having them in the group layout the increase in ms is about 17ms. Because the calculated field is in brackets (select Count(*) from history where type = 262145 and accountid = a1.accountid) slx puts this query in the MAIN select statement as a field (sub select) so it's pretty fast especialy with the VSSC.
eg: select a1.accountid, a1.account, a2.city, a2.state, (select count(*) from history where type = 262145 and accountid = a1.accountid) from account a1 inner join address a2 on(a1.addressid = a2.addressid) where a1.accountid = '
Try running this query in sql query analyzer and you will see.
So, go ahead and create queries in the calc field manager to count child records off of account, contact or oportunity to see break downs in the group layouts or on the main A/C/O detail screens and the beauty is what ever is in the group layout gets exported to excel when you export!
Thanks,
Derek
|
|
|
|
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!
|
|
|
|
|
|
|
|