CapeSoft Bulk Dictionary Editor
Documentation

Please Note that the Bulk Dictionary Editor (BDE) is not freeware, it is included in FM3, Replicate and Multi-Proj.
See License and Copyright for more details.

Before running this utility you must have a good understanding of the product (FM3 and/or Replicate) for which you are making these changes.

• Introduction
• Features
• A brief Run-through
• License and Copyright
• Support
• Version History

CapeSoft's Bulk Dictionary Editor enables you to convert your dictionary quickly and easily to support some of our products. On top of this, this useful tool will give you the power to do bulk general maintenance on your files. Originally written as an add-on to Replicate, it grew into a much larger project and now ships as part of Multi-Proj and FM3 as well.

• Adds the necessary fields to support Replicate - GUID fields and keys to each file, a Site field (where necessary), a Site File and a LogHistory file.
• Enables Site field addition to selected keys and converts your relationships to reflect these key changes.
• Makes conversion to SQL easy - converts arrays to a SQL friendly format, converts data types (like dates to longs, etc.), and converts DATE,TIME pairs to an acceptable standard supported by SQL while allowing you to use your existing variable.
• Makes all fields, files and keys have descriptions with the click of one checkbox.
• Makes bulk driver and other file property changes easily.
• Increments your FM3 file version number.

To run the utility - you will need to:

1. Export your dictionary to a TXD file. (Note: In Clarion 7/8, you need to select export, change to "All files" and enter the file selected with TXD as the file extension for the file to receive the export. Open the file in the text editor and remove the first line '[VERSION2]')
2. Run the Bulk Dictionary Editor (in your Clarion IDE - in the Accessories menu, click the Bulk Dictionary Editor option).
3. Import your converted TXD file to a new dictionary - and then overwrite the old dictionary with the converted one.

Running the Bulk Dictionary Editor

1. Select the TXD file that you have exported from your current dictionary and select a file to contain your converted dictionary (a TXD file).
   
   
   
   You can make use of the file select buttons to bring up a File select box to aid you in selecting the files.
2. Click the Next button to proceed to the Task Selection window.
   
   
3. Check the Convert Dictionary To Support Replicate checkbox if you are wanting to convert your dictionary to support Replicate (add GUID fields and keys, add site fields and keys and/or add the Site file and the LogHistory file)
4. Check the General Dictionary Maintenance checkbox to enable you to do things like change drivers, and file properties.
5. Check the Convert Dictionary to SQL checkbox if you have arrays (Dimensioned fields) in your dictionary. This function will add the necessary OVER fields to allow FM3 to perform it's magic.
6. Check the Make All Fields, Keys and Files have descriptions if you require a description for each field, key and file. Note: New fields, keys and files will have descriptions when added regardless of whether this is checked or not (although where necessary you can specify if you don't want descriptions for your fields).
7. Check the Force File Version insert if non-Existent checkbox if you are adding FM3 to your application and have not yet added Version numbers to your files in the dictionary.
   
   You must select at least one task in order to be able to proceed. Click the Next button to proceed to the Next Screen.
8. If you checked the Convert Dictionary to SQL checkbox, then the SQL Conversion Options window will appear (else proceed to Step 5).
   
   
9. If you would like to convert the file drivers as well, you can check the Convert File Drivers to SQL, which will bring up a screen later to enable you to do a bulk driver change (based on the existing filedrivers). Note: You will almost certainly need to do this (if converting from a TPS app to a MSSQL app).
10. Check the Set Old Name Attribute to set the oldname attribute to contain the default file name of old TPS/DAT files.
11. Check the Set External NAME for keys checkbox to add the NAME attribute to each key to ensure that each key has a globally unique NAME. This is the external NAME that will be used by SQL drivers.
12. Check the Set External NAME for Fields checkbox to add the NAME attribute to each key to ensure that each field has a globally unique NAME. This is the external NAME that will be used by SQL drivers.
13. If you check the Convert Arrays checkbox, then when BDE does your dictionary conversion it will:
    • Create a group with a similar name as your existing array field.
    • Create the same amount of fields (of the array type) as in the original Dimension.
    • Set the OldName attribute (if you check the Set Old Name Attribute) of the array field.
    • Place the old dimensioned field over the created group
14. You can use the File Driver list box to select FileDriver types to exclude from the array conversion (like DOS, BASIC and ASCII). To exclude, click the checkbox to the right of the FileDriver (which must be excluded) and the checkbox will be cleared. You can use the Select All and Select None buttons instead of checking/clearing all the check boxes.
    
    Click the Next button to proceed to the next screen.
15. The SQL Field Conversion Options screen appears, which will allow you to make bulk changes to fields.
    
    
16. If you have used the variable types: DATE and TIME in pairs in that sequence, then you can bulk change these to be within a GROUP over a STRING(8) by selecting the Group,over option in the Set DateTime Pair to option group.
    
    Note: Selecting this option will not affect DATEs and TIMEs that are not in a pair, in the DATE/TIME sequence.
17. You can enforce the SQL backend to do the auto-numbering instead of the Clarion file drivers. Check the SQL Do Auto Numbering checkbox. This will add a UserOption to your Auto-Numbered field and remove the AUTO attribute from the key.
18. You can create a file of data types that you would like BDE to convert. Use the list box to add field types to. You can choose to ignore arrays (in which case BDE will convert them to a GROUP, over as in Step 3). You can also disable a Field Type conversion. In the above diagram the DECIMAL to PDECIMAL conversion is disabled. You can also specify field conversions for a specific driver type only.
    
    Note: You will notice in the above diagram that DATEs and TIMEs are set to be converted to LONGs. This is if these data types occur outside the pair (as specified in 4.1.).
19. If you would not like BDE to convert the field types in the list, then you can check the Ignore Conversion List. Click the Next button to proceed to the next screen.
20. If you checked the Convert Dictionary to support Replicate checkbox then the Replicate Options window will appear (else proceed to step 7).
    
    
21. You can set the Site field name (which will be the field name used in the added site file (if requested) and the added site fields to your existing files (if requested)). This must be the same as the name specified in your Replicate Global Extension template (on the Site tab).
22. If you've selected a Site field name, then you can specify an Initial Value for the site field to be primed with. This is defaulted to RepGLO:Site, which is a global variable, set in the Init method of replicate.
23. You can check the Add Site fields to your existing files if you would like to do this. This will enable you to select which files and keys you would like to add the site field to (which you can setup in the next window).
24. If you are using a SQL backend and do not have FM3, then it will be necessary to add the fields required to the end of the record structure (and not to the beginning - by default). Check the Add Fields to the End of the Record Structure to do this.
25. Check the Add a Site File to your dictionary if you would like the BDE utility to do this. It will not add the Site File if it finds one existing in your dictionary.
26. Check the Add a LogHistory File to your dictionary if you would like the BDE utility to do this. It will not add the LogHistory File if it finds one in existing your dictionary.
27. Check the No Descriptions For New Fields if you don't want your new fields to have descriptions.
    
    Click the Next button to proceed to the next screen.
28. You can check the Set external NAME for fields checkbox if you want an external name. This makes an XML (and SQL) compliant field name for your fields as they are entered in the logfile.
29. If you checked the Add Site Fields to your existing files checkbox (on the Replicate Options window) then the Setup Site Field addition window will appear (else proceed to step 7). You need to wait until the Dictionary is assessed for the file content. The Progress indicator at the bottom of the window will indicate when the assessment is complete.
    
    
    
    You will see a tree of the files in your dictionary. Each file forms a level 1 item in the tree, and the files' keys are listed as level 2 items. Each relationship that is based on that key are level3 items in the tree.
30. You can click on the black check boxes in the columns to change the adding options. The last 4 columns (Site Field: Add, Site Field: Prime, Add Site to: Keys and Add Site to: Where) indicate the actions that you want to perform on the file. If you want to:
    
    
    • add a Site field to the file/key/relationship, then check the Site Field: Add check box.
    • prime the Site field with the Global Site variable (which is added to your dictionary), then check the Site Field: Prime check box (on files only).
    • change the position of the site in the key's components (for keys) or the default for the file (First/Last). The red checkboxes indicate fields that you cannot alter. For example if there is already a Site field, then you will not be able to check the Add Site checkbox to add a site field to that file. You can right click on the editable columns to get a popup menu that will enable you to make bulk changes to the list box.
31. You can use the Default when Site field added group to preset defaults for adding Site fields:
    
    
    • Click the Prime Site checkbox to automatically prime the site when a site field is added to a file.
    • The Add To Keys option group will give you the option to automatically add the Site field to a key type when adding the site field to that file. E.g. If you select the Unique option, then when you add the site field all the unique keys in that file will have the site field added as one of their components.
    • The Position option group allows you to specify the default key position for a Site field in a key. This applies when adding a site field to a file. Thus if you add a site field to a key the default position will be taken as the position indicated in the Add Site to: Where column, not from the Position option group.
    • The Related Files option group allows you to determine how the relationships are affected when the site field is added to the component. At present there are 2 options: Keys Only and Cascade. Selecting Cascade will force all the relationships and the corresponding keys (and their corresponding relationships) and files to have the site field added. Selecting Keys Only will force all relationships to include the Site field as a link and the corresponding keys (but not their relationships) and files.
32. Some Cautionary Notes
    • Make sure you know which tables to make Site-related tables and which to make global tables BEFORE you convert your dictionary.
    • You should not select First in the Position group and Keys Only in the Related Files group (this will force NOLINKs for the first component in the relationship which is illegal).
    • You can change the position of the Site field within a keys components. If the number of components are not the same between the 2 relating keys and you change the position of the Site field, this can lead to an illegal relationship (with NOLINKs occurring in front of the Site Field).
    • Select None in the Add to Keys group - this will ensure that you select each key with the minimum amount of cascading occurring when adding a site field to file.
    • If you have already run the Bulk Dictionary Editor on your dictionary and are re-running it, then Bulk Dictionary Editor will find your GUIDkey and GUID field in the file. Although your GUIDKey is a unique key, you will not be able to add the Site field to it's field component.
      
      Click the Next button to proceed to the next screen.
33. If you checked the the General Dictionary Maintenance checkbox on the Task Selection window then the General Dictionary Maintenance window appears (else proceed to step 8).
    
    
    • The List box shows the various file properties of each file. You can left click to change each individual property of each file (FileDriver drops a list down for you to pick a driver from, check boxes change status and Driver Options, Owner, Prefix and Filename can be edited in an Edit-in-Place field), or you can right click and select the option from the popup menu that appears (to do a bulk property change). Options that are variables must be prefixed by an !, or else they will be considered constants.
    • In the example above, you can change all the MSSQL drivers to another driver by selecting the For All Tables with this Driver option from the popup menu. This will bring up a window from which you can select the required file-driver.
    • If you want to restore all the defaults (i.e. overwrite the changes that you have just set-up), you can right click on the Table Name
      and select whether you want to restore the defaults for all the tables, or just the highlighted one.
      
      
    • Most SQL drivers will require that each file has a Primary Key. You cannot use BDE to make a Primary in files which don't contain a primary key, but it will notify you (in the Pri column) which files do and don't have Primary keys. Once you have imported your converted txd file into the Clarion dictionary editor, you will need to make one of your keys a primary key.
    • If you are converting your files to SQL, then you may have checked the SQL Do Auto Numbering checkbox (step 4.2. above). In this case you can turn off Auto-numbering conversion for each file
    Once you have finished setting up the options that you want to change, click the Next button to proceed to the next screen. 8. The Confirmation window appears. From here you can click the Go! button to convert the dictionary. The Progress indicator will show the size of the dictionary and how far the conversion is.
    
    

If there were some irregularities in the TXD to be imported (into the Clarion dictionary editor), then these will be reported at the end of the conversion. Irregularities so far found are:

1. More than 512 chars in the SCREENCONTROL prompts.
2. The BOXED attribute in a TEXT control.

The BOXED attribute is removed during the conversion, because the TXD will not be imported into the Clarion Dictionary Editor with these included. The warning will indicate each BOXED attribute that is removed and the line number (of the TXD file) from which it was removed. In the case of the long SCREENCONTROL prompt, you will need to manually shorten the indicated lines (with a text editor), and replace the deletions once you've re-imported the dictionary into the Clarion dictionary editor.

Please note: There is nothing we can do about these irregularities as they are not supported by the dictionary importer (although they are successfully exported from this same Clarion dictionary editor).

Question: I am not sure what to put in the "Full Pathname" of the "Table Properties" when I'm changing my driver to a SQL driver.

Answer: You can check the Set OldName File Attribute checkbox on the SQL Conversion Options tab. This will ensure that the OldName attribute is set to the old file name.


Question: Is there a way to bulk add the Owner Name (!Glo:Owner to each table)?

Answer: Yes. In the conversion process, when you get to the 'General Dictionary Maintenance' window, right-click on the 'Owner' column (in the 'Attributes' column group) and you can copy the current owner name throughout the tables. If there is none set for the current one, edit that one (to the one you want for all your tables) and then right-click on that block and duplicate it for all your other tables. You can also hold down the control key and select certain tables that you would like to change (if you don't want all the tables to have the same owner name).

This application is copyright © 2013 by CapeSoft Software CC. None of the included files may be distributed.

This product is provided as-is. Use it entirely at your own risk. Use of this product implies your acceptance of this, along with the recognition of copyright stated above. In no way will CapeSoft Software CC, their employees or affiliates be liable in any way for any damages or business losses you may incur as a direct or indirect result of using this product.

Bulk Dictionary Editor is included in Replicate, FM3 and Multi-Proj. This is not a freeware/shareware product. You may only use it if you have purchased either Replicate, Multi-Proj or FM3 from Clarionshop, Capesoft or one of their authorized suppliers. You may not distribute any files that come part of BDE including , but not limited to EXE files, graphic files (JPG and GIF)

We welcome any suggestions from users regarding new features that might be added to the Bulk Dictionary Editor.

Your questions, comments and suggestions are welcome. Check our web page (www.capesoft.com) for new versions. You can also contact us in one of the following ways.

CapeSoft Support
Email
Telephone	+27 87 828 0123
Fax	+27 21 715 2535
Post	PO Box 511, Plumstead, 7801, Cape Town, South Africa

The Bulk Dictionary Editor ships with Replicate, FM3 and Multi-Proj which may be purchased from:

CapeSoft Sales
Web	www.capesoft.com
Email
Telephone	+27 87 828 0123
Fax	+27 21 715 2535
Post	PO Box 511, Plumstead, 7801, Cape Town, South Africa

Run the supplied installation file (that comes with Replicate, FM3 or Multi-Proj).

Version 2.08: Released December 12, 2005

• Fix - NAME not added to GROUPs (some SQL drivers don't support this).
• Fix - unique NAME added to over field for array conversions.

Version 2.07: Released January 12, 2005

• Fields added to SiteFile (LastFileRequested and NoOfFilesRequested).

Version 2.06: Released December 22, 2004

• StatusFlag into the SiteFile.

Version 2.05: Released November 18, 2004

• When bulk allocating file names, you can use a blank prefix.

Version 2.04: Released November 1, 2004

• Fixed - UserOption was being added in incorrectly when TRIGGERS section wasn't present.

Version 2.03: Released November 1, 2004

• Fixed - Relationship mods where not being added in (regression in v1.64).

Version 2.02: Released October 11, 2004

• Adds the NoFTP and DontSendFiles to the SiteFile (when adding Replicate support to your dictionary).

Version 2.01: Released October 5, 2004

• Returned the Inc and Dec buttons to the Field Conversion form (for converting Strings to CStrings).

Version 2.00: Released September 7, 2004

• The new look BDE.
• Fixed spurious extra File UserOptions section addition.

Version 1.64: Released July 26, 2004

• Supports dictionary Triggers (in Clarion6.1)
• Supports maintaining BOXED attributes (import into Clarion6.1 dictionary fixed by SV)
• Adds file extension to OLDNAME attribute (where path is also present)
• Bug fix - Fixed UserOption removal from relationships
• Bug fix - No file changes (external names, etc) to Global and Pool files.

Version 1.63: Released June 15, 2004

• Bug Fix: Tables without keys were having an arbitrary key assigned to them (In the Replicate table tree).

Version 1.62: Released April 23, 2004

• Bug fix: Multiple dimensioned fields (where dimensions are > 10)
• New feature: Replicate's Online transportation method requires 2 extra fields in the SiteFile. Also included an optional flag - so that replication can be disabled to one site.

Version 1.60: Released February 3, 2004

• New Feature: You can set the External field names on the Replicate screen - this helps for making your logfiles XML compliant.

Version 1.59: Released January 22, 2004

• New Feature: You can set the value of the Site field for FM3 conversions.

Version 1.58: Released January 22, 2004

• Fix: Was not writing the Dictionary UserOptions in correctly, which was causing no import (where the Dictionary UserOptions were used).

Version 1.57: Released January 20, 2004

• Fix: DateTime conversion to GROUP,Over when the DateTime pair occurs after a GROUP in a table where there are many GROUPs.

Version 1.56: Released December 5, 2003

• Fix: DateTime conversion to GROUP,Over when DateTime pair occurs after a GROUP or last record in the RECORD structure.
• Fix: Ignores all field conversion in GlobalData files.

Version 1.55: Released October 30, 2003

• Added Dictionary Comments (on what options were selected in the BDE execution)
• Fix: Autochange SiteFile prefix if prefix is already used (to avoid duplication)
• Fix: Autochange HistoryFile prefix if that prefix is already used (to avoid duplication)
• Fix: Add Key if Sitefield not added to this component (bug introduced in v1.53).
• Fix: Allows manual overrides of files when DateTime pair conversion to Group,over is required.
• Fix: Allows bulk set/clear of AUTO conversion overriding (at column level) and DateTime Group,over conversion.
• Fix: Includes Auto and Field (in convert group) and UserOptions in restoring changes.
• Changed icon in the Auto column when more than one AUTO key exists in the file (so the AUTO cannot be converted automatically to the SQL backend).
• Increased the maximum size of the window resizing.
• AliasSection indicator prevents global File changes happening on aliases.

Version 1.54: Released October 13, 2003

• AutoNumbered keys displayed in the Replicate list-box.
• Fix: Clipped and left-justified filenames used for the OldFileName FM3 attribute
• Fix: Don't do FM3 conversion for Arrays in GlobalData (for File Arrays only)
• SIT:RelatingSite field added instead of SIT:FromSite
• Added the ability to resize the window.
• Added a horizontal scrollbar to the 2 list boxes that require

Version 1.53: Released August 26, 2003

• Fix SiteFieldname checker.
• Fix NOLINK checker (marked in Red).
• Fixed incorrect placement of relationships (when GUIDKey exists) in the Replicate File Q tree.

Version 1.52: Released August 25, 2003

• Allow formulae when determining the size of a variable type conversion (this allows for size incrementing and decrementing).
• Fixed variable conversion (when variable has description).
• Eliminates size if size not required for that variable type.
• Indicates (in red) a key that has a NOLINK variable that is not the last element in the relationship.

Version 1.51: Released June 17, 2003

• Fixed adding Group, over string option for Date\Time pairs.

Version 1.50: Released June 5, 2003

• Fixed Relationship addition - if both SiteFile and LogHistory File are imported, then add relationship between the two files.
• Made SiteFile a THREADed file - support for Clarion6.

Version 1.049: Released June 2, 2003

• Warning system introduced - when a txd cannot be imported, a warning is generated (at the end of the txd conversion) to indicate the illegal line. Unsupported conditions are: over 512 characters in a SCREENCONTROL prompt, a BOXED attribute in a TEXT control.
• Supports Clarion6 exporting irregularities (sometimes exports a blank UserOption section).
• Oldname (for short filenames - less than 6 characters) attribute fixed.
• Fixed the Driver options inclusion (removed the extra ' on either side of the DriverOption).

Version 1.045: Released May 12, 2003

• Does not convert Dimensioned GROUPs.

Version 1.044: Released May 9, 2003

• Added extra fields for the SiteFile to support FTP transport.
• Fixed the Group,Over declaration (for DateTime pairs) for long group names (was including the file prefix).
• Fixed sequential DateTime pairs (when doing DateTime pairs to Group,overs).
• Fixed DateTime pairs where DATE immediately precedes the DateTime pair.
• Global Data declaration is not assigned an external name.

Version 1.040: Released April 30, 2003

• Bug-fix when converting Date/Time pairs to Group,overs. Was not comparing the fullname, so a partial name was receiving a duplicate group, over definition.

Version 1.039: Released April 22, 2003

• Bug-fix in existing UserOptions, that take up more than one line.
• Bug-fix in Autonumbering - was sometimes removing the AUTO even when not requiring SQL to do the autonumbering.
• Bug-fix in Date/Time group over, when two Date/Time sets are in sequence, and when Date and Time fields have existing UserOptions and No-Populate attributes.
• Bug-fix in one-way relationships (was not entering them correctly).
• Relationships entered into the txd in the same order as previously (makes relationship comparison easy), rather than in primary file order.
• Bug-fix - External NAME in fields entered with the correct prefix.
• Bug-fix - prevents file operations done to a GlobalData table.
• Bug-fix - prevents the version UserOption being added to a Key.

Version 1.036: Released March 27, 2003

• Bug-fix in one-way relationships.
• Bug-fix in ALIAS file relationships (was hanging on the Site field insert screen when ALIAS file relationship was selected for Site field addition).
• Bug-fix in NAMEing fields (i.e. auto-assigning External Field names).

Version 1.035: Released March 24, 2003

• Supports all descending keys (bug - was not supporting foreign descending keys).
• Add FM3.

Version 1.034: Released March 19, 2003

• Supports one-way relationships.
• Facility to auto-add external NAMEs for fields.
• External NAMEs suffixed with Prefix_ instead of Prefix: (the ':' is illegal in some SQL drivers)
• Fixed the SiteFile corruption problem (where no IDENT number was being added to the SiteHi field).
• Fixed the AutoNumber (for first fields in a file)

Version 1.033: Released March 6, 2003

• Supports descending keys.
• Missed add for SiteHi field when including the Site File for Replicate txd conversions.

Version 1.032: Released March 3, 2003

• Changed the search for the beginning of a file to ' FILE,' (was 'FILE,').

Version 1.030: Released February 20, 2003

• Added the ability to add the site field to keys manually.
• Added the ability to effect key changes to relationships and relating keys (and files).
• Added the ability to turn off debugging (checkbox on the About window).
• Added the ability to convert DATE/TIME pairs to GROUP over a string (matching a DATETIME variable type).
• Ensure OLDNAME attribute is set to the correct file extension, and allows variables in the OLDNAME attribute.
• Added the ability to force SQL to auto-number records (instead of the native Clarion).
• Added the ability to convert variable types to another variable type (for ALL file drivers or driver specific).
• Bug Fix - Owner attribute can contain variables.

Version 1.024: Released December 11, 2002.

• In Replicate conversions, the primed field (for SiteFields) was generating a syntax error.

Version 1.023: Released December 10, 2002.

• Fixed action when Add SiteField column is checked.
• Fixed Add Site Field 'Select All' - was not checking the availability of Primary and Unique keys before checking these checkboxes in the respective columns.
• Case insensitive checking for the existence of the Version user option.
• Converted to C55G (columns not correct - groups displayed as columns).
• OLDNAME addition updated - also adds oldname attribute to Clarion and BTrieve files.

Version 1.022: Released December 3, 2002.

• Fixed IDENT number (where Global data was included in the dct) assignment.
• Allow default (add to unique and add to primary) additions when adding the Site field to a file.
• Fixed version incrementing - in some cases the version number was not incrementing.

Version 1.021: Released November 21, 2002.

• Allow you to force Version number into Files, where no files have the version number
• Allow priming of the Site field in Site-related tables.
• Allow default values into the Add Site to Unique, Primary and Prime Site fields.
• Allow bulk DATE and TIME data conversion to longs.
• Allow bulk User definitions inserts for files.
• Creates a Global Data (Site) structure for Replicate conversions. This Site variable is used to initialize the Site field and can be used in the application.
• Allows population of the NAME attribute to ensure that external key names are globally unique (for SQL).

Version 1.010: Released November 8, 2002.

• Fixed bulk editing of the file attributes in the General Maintenance window.
• Fixed the compilation of the FileQ (used in the General Maintenance window).
• Set DOS, ASCII and BASIC drivers to default off when building arrays (SQL dictionary conversion).

Version 1.000: Released October 14, 2002.