Tag: schemaeditor

How to Include Calculated Data in Query Results

September 19, 2017 I've blogged before about the Filter Config and Filter Overrides within Dovetail Agent. I totally dig this feature, as it makes it super easy to customize the queries within the app without having to write code. We know it's easy to show data that comes from a database column within the query results - such as a contact's name or phone number. But what if we wanted to show something that is more dynamic? i.e. something that needs to be calculated?   For example, in the result set for contact queries, what if we wanted to show the number of open cases that a customer has? There's not an existing field in the database that contains this data. The number of open cases needs to be summed up. So how can we do this in a query? SQL Views to…

SchemaEditor improvements when dropping columns on Oracle

July 8, 2013   I’ve been recently working with a couple of customers who were dropping database columns from their schema on Oracle, and the drops were taking a long time to complete. In one instance, the customer estimated it would take 50 hours to drop a bunch of columns from their contact table. In another, a customer observed that dropping two columns from table_site_part took over two hours. Dropping 3 columns from table_contact took over 1 hour. These long execution times can disrupt normal operations – especially those environments with limited maintenance windows. Dovetail’s schema editing tool (Schema Editor) and Amdocs schema editing tools (ddcomp, SchemaManager) all do the same basic operations when it comes to dropping columns. Basically they all do this: ALTER TABLE table_name DROP COLUMN column_name; There’s a better way. Set Unused On large tables the process of physically…

SchemaEditor and OneToOne relations : clearing up a bit of ambiguity

October 22, 2012 Using Dovetail SchemaEditor to add a new relation to your schema is super easy.   For example, here’s my schema script for adding a new one-to-one relation between the modem table and the user table:   What’s nice is that I only have to define the relation once in the schema file, as opposed to other tools (ddcomp, DD Editor, SchemaManager) where I would have to add the relation to the file in two places (as a relation under both the modem table and as a relation under the user table).   But, there is a bit of ambiguity here – which side is the primary side (OTOP) and which side is the foreign side (OTOF)? Answer   The foreign side will be on the table/name, and the primary side will be inverseTable/inverseRelationName.   So in the example above, the modem2user is the OTOF, and…

Automating Deployment For Clairfy Using Rake

February 6, 2012 Gary posted recently on Automating Deployment For Clarify. A customer recently had an automation need that I answered in a similar way but rather than using NAnt I pulled from our fancy favorite new build automation tool Rake. In this post we’ll take a look at automating common database setup tasks like applying schema scripts and executing arbitrary SQL using Rake. I hope you find it useful. Where is the Code? I put together a Dovetail Automation gist with everything you need to solve two problems. Applying  Dovetail Schema Editor schema scripts. Applying all SQL scripts in a directory. Check out the readme for setup and execution instructions. Next I’ll talk a little about what each task does. Applying Schema Scripts (apply_schemascripts) This task looks for a child directory named schema below where the rakefile.rb is located. Every file in…

Automated Deployment for Clarify Example

January 11, 2012 Last year I posted about automated deployments for Clarify. From that post:   Doing automatic deployments of Clarify changes is pretty straightforward. If I was starting off, I would use a contemporary build script tool such as nant or rake, and simply have it call out to your schema editing tool (Dovetail SchemaEditor, or ddcomp) to perform schema changes, use an import tool (such as Dovetail ArchiveManager, or dataex) for importing forms, and then compile your Clearbasic code using CBEX, or just import it as a DAT file using Dovetail ArchiveManager. File copying, registering components, configuring web servers, and setting permissions are all common tasks that are available in these build tools as well.   Should you also need to do additional stuff in your database, some code that uses the Dovetail SDK can be useful.   You could tie this…

Case Aging, including “pauses”

October 7, 2011 Over the years of working with Clarify, I’ve seen a request come up a few times for calculating case aging, including “pauses”.   It’s easy to calculate a total case age. For open cases, it’s the difference between now and the time the case was opened. For closed cases, it’s the difference between case open time and case close time.   But what about the workflows where a case can be paused? For example, the customer may go on vacation, or any other myriad of reasons where we want to pause the clock on the case.   The common approach to this from an application standpoint is to have a new case Status value of Paused (or something similar), so that we simply change the status to Paused to pause the case, and then change it to something else to…

Schema 201 Webinar Follow-up

August 24, 2011   Big Thanks! to all those that attended today’s Schema 201 webinar. We had a great turnout!     Slides   The slides from today’s presentation are available on Slideshare.   Recording The webinar recording is available for playback.   Links Schema 101:   SQL View example:   SQL View that includes case data, plus a column that indicates “how many open subcases does this case have”   More Info on Dovetail SchemaEditor:   More Info (and free download) for Dovetail BOLT:   Future Webinars Have an idea for a future webinar? Let’s hear it!   You can leave a comment here, or drop me an email at

Webinar: Clarify / Dovetail Schema 201

August 1, 2011 In this complimentary webinar, we'll build on the previous Schema 101 session, and dive into more advanced schema topics. We had a great turnout in the first session, and a lot of requests for part 2. Topics SQL Views Many-To-Many Tables Role Objects Pseudo Relations Exclusive Relations Change Fields Closer look at the ADP tables Those are the topics I have planned to cover. If there’s anything else you’d be interested in, please let me know, and I’ll see what I can do. When Wednesday, August 24, 2011 11:00 AM - 12:00 PM (Central Time) Register Register at:

Schema 101 Webinar Follow-up

May 17, 2011 Big Thanks! to all those that attended today’s webinar on Schema 101. Slides The slides from today’s presentation are available on Slideshare.   Recording The webinar recording is available for playback. Links More Info on Dovetail SchemaEditor:   More Info (and free download) for Dovetail BOLT: Schema 201   Interested in a more advanced session? I’m considering doing a Schema 201 session, including items such as SQL Views, Database Flags, Change Fields, Pseudo Relations, MTM tables, ADP in more detail, etc.   If this is something you’re interested in – please let me know! I’ll probably only do this if there’s enough interest up front.   You can leave a comment here, or drop me an email at

Reminder: Schema 101 webinar this week

May 16, 2011 Just a little reminder that I will be hosting a webinar on Tuesday of this week covering the basics of your Clarify/Dovetail schema. This will be a good overview for those who are new to a Clarify/Dovetail system, and a good refresher for those who have been around for a while. Topics Schema and database concepts Viewing your schema Editing your schema Schema tools A walkthrough of customizing your schema using SchemaScript Assorted tips and tricks for working with your schema When Thursday, May 17, 2011 11:00 AM - 12:00 PM (Central Time) Register Register at: