7/8/2025 2:34:22 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.
|
|
|
|
Building groups/etc. using external SQL data
Posted: 08 Jan 10 6:34 PM
|
We have an extensive set of external data which we regularly link with SLX from our reporting system. This data contains information such as customer purchase history, customer product data, estimates, etc, which all comes from our financial back end.
This external data is hosted on SQL and includes an AccountID column, so joining to SLX data from our reporting system is a breeze.
Having said that, my team needs to be able to build groups, run queries, etc, from WITHIN the SLX UI. (an example query is below)
What is the best way to make this data accessible to these users?
Thank you for ideas you might have...
Tony
Example query that we can run in SQL management studio - but instead, our sales reps need to build and run from within SLX:
select a.account, a.type, h.purchaseamt, h.lastpurchasedate
from saleslogix.sysdba.account a
inner join financials.dbo.customer_history h
on a.accountid = h.accountid
where a.type = 'Customer'
and h.purchaseamt > 10000
|
|
|
|
Re: Building groups/etc. using external SQL data
Posted: 09 Jan 10 1:11 AM
|
Build a suitable view in your SLX database (owner sysdba):
create view sysdba.Financials
as
select a.account, a.type, h.purchaseamt, h.lastpurchasedate
from saleslogix.sysdba.account a
inner join financials.dbo.customer_history h
on a.accountid = h.accountid
where a.type = 'Customer'
and h.purchaseamt > 10000
Now go into DB Manager and you will see the new 'Financials' view. 'Enable' it and it's ready to go. Won't work for remotes, of course.
|
|
|
|
Re: Building groups/etc. using external SQL data
Posted: 27 Jan 10 6:56 PM
|
All I can say is spec*TAC*ular!
it worked just great (finally got around to doing it - amazing how today's crisis can be forgotten tomorrow).
in any case, I noticed that if i modify the view, (adding a column for example), i had to recreate the whole thing in slx (drop the table in db manager, re-add it, enable it, recreate the join to my account table). Is this expected, or is there a way to "refresh"? |
|
|
|
Re: Building groups/etc. using external SQL data
Posted: 28 Jan 10 2:21 AM
|
When you do the 'Enable' bit, it seems that SLX writes some meta data about the attached view into its internal tables. This meta data includes field names, number of fields etc. If you want to change any of that stuff in the view, you have to go through the whole refresh process (as far as I know).
Otherwise (for example if you are just changing the WHERE clause in the view), you can just run an ALTER VIEW statement and SLX will pick up the mods without the refresh being necessary.
Phil |
|
|
|
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!
|
|
|
|
|
|
|
|