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!

Architect Forums - SalesLogix Scripting & Customization
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.

Forums RSS Feed

Back to Forum List | Back to SalesLogix Scripting & Customization | New Thread

View: Search:

Author

Thread: How to use top function while creating groups

mark
Posts: 70

How to use top function while creating groups Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 09 Jul 08 8:45 AM

Hi

I need to create a group with the condition includes only top 5 records to be fetched from the table based on create date. I have tried in number of ways but i failed to find out how to use top function while creating groups. Any body having idea about this.

I also got another question like shall we create a group by passing the sql of the group through code.

Please help

Thanks in advance

Thanks,
Mark

[Reply][Quote]

Phil Parkin
Posts: 819

Re: How to use top function while creating groups Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Jul 08 12:54 AM

Using the IN condition should work - it's the only way that you can actually enter some SQL in your group definition.

But ... I tried it and no records were displayed for me - even though the query executes perfectly in Management Studio.

Here is the group SQL I used, which should retrieve the 5 most recently created accounts:

SELECT A1.ACCOUNTID, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.MAINPHONE, A1.TYPE, A1.SUBTYPE, A1.STATUS, A1.ACCOUNTMANAGERID, A1.SECCODEID, A1.ACCOUNT_UC FROM ACCOUNT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) WHERE (A1.ACCOUNTID IN ((select top 5 ac.accountid from account ac order by ac.createdate desc))) ORDER BY A1.ACCOUNT_UC ASC

So close ... maybe you can work out why there is a problem in SLX.

Phil

[Reply][Quote]

Walter Shpuntoff
Posts: 167

Re: How to use top function while creating groups Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Jul 08 4:25 AM

IIRC - the provider does not support TOP

ws

[Reply][Quote]

Phil Parkin
Posts: 819

Re: How to use top function while creating groups Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 10 Jul 08 7:48 AM

Thanks Walter - that explains it.

So I guess the next thing to look at would be a view - which you could INNER JOIN to - containing the TOP nn clause.

Phil

[Reply][Quote]

Page 1 of 1

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!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2025 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 8/24/2025 11:37:16 AM