Having played with linked tables from another Db into Cherwell you will quickly find out that OneSteps currently don't work well if at all, there's a reported bug waiting to be fixed. Even so wouldn't it be nice to have the full power of T-SQL to assail your third party DB. So having just worked on a small SQL Server based app that used triggers to call the Cherwell API's I wondered why we couldn't do that from Cherwell back to the Apps DB. So I created a small Cherwell BO with one record in it, located the records RecID as it should never change. Wrote a nifty trigger to write an Asset history Record back to the third parties DB for a given Asset number, and simply updated the Asset Number in my Charwell BO from a onestep and boom, it works like a charm, history record written. You could use this to send all manner of Cherwell data back to a remote DB. enjoy.
ALTER TRIGGER [dbo].[CherwellHistory] ON [dbo].[HistoryID]AFTER INSERT,UPDATEAS BEGINdeclare @assetnumber varchar(1024),@assetid int
Select @assetnumber = assetnumber From HistoryID
INSERT INTO AssetWin70.DBO.HISTORY (
(SELECT MAX("HistoryID")+1 from AssetWin70.dbo.History),'P',
WHERE "AssetNumber" = @assetnumber)
"onesteps don't work well if at all, there's a bug waiting to be fixed"
You should really clarify which 'bug' you're talking about, which one-step action type is impacted by it (or what activity you're saying is prevented by this bug), as well as what version it's impacting.
This statement is overly broad and the premise behind this entire post is hard to understand, as what you're describing here can easily be accomplished using an external connection and external business object in Cherwell, at which point the external database can be manipulated just as easily as the data within the Cherwell database, either via onesteps or manual user input.
As a general rule, if you're going to say something doesn't work, you should be more specific about what you're saying doesn't work; this would be like saying "Email in cherwell doesn't work well if at all, so I've built an API for sending email externally" - would that make sense unless I specified what issue I was actually having with sending emails?
Separately - and on a more helpful note - if you did need a trigger, it's useful to add that you could also record this trigger into a Database Server Object definition in Cherwell to ensure it gets re-created if you move your Cherwell database / restore from a CZAR; as otherwise it would be lost on czar restores and need to be manually recreated.
Sorry I wasn't clear about the onestep, but using a onestep for an externally linked table with read/write access does not work in version 8.2.3 and I also saw it fail in a 9.3.X version while at the conference and a bug report was reported, I agree that from a form in the blue pill it is possible to right to an externally linked table, but using a onestep creates a blank record with no saved values from the onestep. If I am mistaken I stand corrected, but I have not been able to get onestep updates to work on external tables. Good point about the CZAR thx.
Thanks for clarifying; that may be a good issue to post here on the forum, as I think it may be something that can be troubleshot / resolved; I've done a bit of work on processes like this on V8.3.1 and 9.1, so it might just be a small workaround that's required.
Feel free to post the details about that issue somewhere on the forum, it would be a good read.
^ Note for creating records in an external table if you try writing to the RecID field directly it won't actually do it and results in a blank recID value being saved (or whatever the recid / unique id value is for that object).