CapeSoft.Com
Clarion Accessories
Bulk Dictionary Editor
CapeSoft Logo

CapeSoft Bulk Dictionary Editor
Documentation

Installed Version 2.08 Latest Version 2.08
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

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.

Features

A brief Run-through

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).

    File Selection screenshot

    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.

    Task selection screenshot
  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).

    SQL Conversion Options screenshot
  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.

    SQL Field Conversion Options screenshot
  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).

    Replicate Options screenshot
  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.

    Setup Site Field Addition screenshot

    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).

    General Dictionary Maintenance screenshot
    • 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.

      Restore defaults screenshot
    • 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.

    BDE Confirmation screenshot
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).

Frequently Asked Questions (FAQs)

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).

License & Copyright

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.

Support

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 sales at capesoft dot com
Telephone +27 87 828 0123
Fax +27 21 715 2535
Post PO Box 511, Plumstead, 7801, Cape Town, South Africa

Installation

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

Version History

Version 2.08: Released December 12, 2005 Version 2.07: Released January 12, 2005 Version 2.06: Released December 22, 2004 Version 2.05: Released November 18, 2004 Version 2.04: Released November 1, 2004 Version 2.03: Released November 1, 2004 Version 2.02: Released October 11, 2004 Version 2.01: Released October 5, 2004 Version 2.00: Released September 7, 2004 Version 1.64: Released July 26, 2004 Version 1.63: Released June 15, 2004 Version 1.62: Released April 23, 2004 Version 1.60: Released February 3, 2004 Version 1.59: Released January 22, 2004 Version 1.58: Released January 22, 2004 Version 1.57: Released January 20, 2004 Version 1.56: Released December 5, 2003 Version 1.55: Released October 30, 2003 Version 1.54: Released October 13, 2003 Version 1.53: Released August 26, 2003 Version 1.52: Released August 25, 2003 Version 1.51: Released June 17, 2003 Version 1.50: Released June 5, 2003 Version 1.049: Released June 2, 2003 Version 1.045: Released May 12, 2003 Version 1.044: Released May 9, 2003 Version 1.040: Released April 30, 2003 Version 1.039: Released April 22, 2003 Version 1.036: Released March 27, 2003 Version 1.035: Released March 24, 2003 Version 1.034: Released March 19, 2003 Version 1.033: Released March 6, 2003 Version 1.032: Released March 3, 2003 Version 1.030: Released February 20, 2003 Version 1.024: Released December 11, 2002. Version 1.023: Released December 10, 2002. Version 1.022: Released December 3, 2002. Version 1.021: Released November 21, 2002. Version 1.010: Released November 8, 2002. Version 1.000: Released October 14, 2002.