Susan, Since you are on Oracle there are a couple of ways to get the Org data. (MS SQL and others have similar abilities, just different names) You can link across a dbLink (I personally prefer to make a local Oracle view that access the data across the dbLink. This way the Remedy View form is against nothing but the local database). You can use a Materialized View to pull the Org data into the Remedy database (A Materialized view can be told to auto refresh every X interval behind the scene). You can use scripts to update the data in Remedy from the "master". With any of these you can create a View form in Remedy against the Org data (I have used all 3 and they all have their place where they work the best). With the last 2 you have the advantage that you can add whatever indexes you need without affecting the master copy. The View form can be created with every field's change flag disabled so no one can mess with the data in Remedy, they would have to update the master.
Next thing to look at is normalization of data. Do you really need 30 fields from Org into Site? Personally I only store a key of the parent "Org" into the child "Site" (unless you are in a many to many situation, then you need an intermediate "mapping" form, but I don't want to get into that here). i.e. In Site you would have an Org_ID field that holds a unique key for the Org data. (This is the way I have always designed my Remedy forms). If you want to view the combined data exactly like they see now for Site you can create a Join form (While the Admin tool would not let you use a View form in a Join, Developer Studio 7.6 does). Hope this helps Fred From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Susan Palmer Sent: Thursday, January 19, 2012 4:32 PM To: arslist@ARSLIST.ORG Subject: Suggestions requested for bringing in data from a separate database ** Hi Everyone, Currently we house all data necessary for various Remedy operations in Remedy forms. It has a 'flow-down' mentality so top feeds to next level and that level feeds to next level etc. All other systems we have access the necessary data from Remedy. Of course there is always the issue of data correctness, which really has nothing to do with Remedy specifically. But it's been decided that some specific data will be stored in another separate database and all systems will access it from there (much like they do now accessing it from Remedy). The new database will be much more accurate (tongue in cheek) and will not have data correctness issues. That's the basic background. So I'm looking for solutions that provide me the easiest way to access that data stored in the separate database seamlessly. I don't want to bring it all into a Remedy form and then access it from there since basically that takes me to where I currently am (which works just fine but is being discouraged). For instance, all information about an Organization is in the Organization Information form and when a 'Site' belonging to that Organization is created much of the Org info is automatically set into the new Site record. So instead of starting at the Org form to create a Site I need to start at the Site form and pull in the Org data (sql set fields). Or maybe I do a display form for the Org data (view form with data displayed) and still create the Site from that display record via set fields. Either way how do I get that Org info without using a menu selection (600 choices) or needing to know exact verbiage. We need to have the Org information stored in the Site record for easy reporting and querying, although maybe that's just my mindset. I would think it would be less efficient to always be calling to another database. We're talking about approximately 30 fields from the Org record. I'm just having a little trouble putting my arms around this and shifting my Remedy centric thinking. We're talking about 600 Org and 50,000 Sites but that number will double in 1-2 years and continue at that growth. Relatively small in comparison to many systems but needs to be scalable. I'd appreciate any help triggering my thought process towards the most correct path which may be one that hasn't even crossed that mind. Thanks, Susan Palmer ShopperTrak 200 W Monroe St 11th Fl Chicago, IL 60606 312-529-5325 spal...@shoppertrak.com<mailto:spal...@shoppertrak.com> ARS v7.5P4 Oracle 10.2 We currently use v7.5P7 client tool but will be moving to web (although many postings question that decision) Our prod server services US, CH, UK, Middle East _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
