Worked Example - Consistently Filling Drop Down Boxes etc

Parent Previous Next

Consistently Filling  Drop Down Boxes With Up To Date Information

Obtaining Consistency and Accuracy Through Scripting Automation

N.B.  Some of these techniques use \write18{...} from within a .sty. Whether used in the preamble of your document or form a  .sty through using that package, \write18 has certain security issues. I strongly suggest that you acquaint yourself with those issues before using the techniques using \write18{} here.

Under Windows,  Heidi can be used to create and fill out MySql databases. MySql provides an efficient and effective means of centralising data that will be used for various drop-down boxes in TwScripting, and can be accessed by other applications as well. More details follow...

"HeidiSQL is an easy-to-use interface and a "working-horse" for web-developers using the popular MySQL-Database. It allows you to manage and browse your databases and tables from an intuitive Windows® interface."

The following example is specific to some very definite needs, yet has in it elements and features that may be found useful in many publishing projects. But as it has grown by accretion is not necessarily the best solution to the overall problem it seeks to solve, and defiantly is not any sort of an example of optimised code. Plus I am not a LateX or TeX guru by any means!

We have recourse to quote a lot of Scripture. Various Bible publishers can be very stringent in their requirements for counting the number of verses used, and for correct placement of copyright notices.

So we needed to quote scripture verses in such a manner that an accurate reference was made to the quoted bible, the verses were counted, and when the document was prepared, copyright notices for any bibles used in the document are placed in the appropriate section with a tally of verses used.

It is surprisingly easy to get this wrong, when it is all done by hand, and that increases the burden for proof readers.

We also needed that any corrections or updates to copyright notices and other bible details, would be universally corrected in all .tex that use them when making a new .pdf

The task was divided up into areas of technology.  (Please see the following files for the detail - this overview will hopefully help pull what you read in those together.)






TeXworks Scripting would handle the insertion of the verses, and ensuring that correct references were being made, syntax-patterns.txt would handle making an inclusion of the bible reference in the document's tag window in the outline section ...

5 \\bibleNumeral\{(\d?.*[a-zA-Z].+.\s).+\s([A-Z]{3})\}

... through Heidi, MySql would store the bible and publishers' information as LaTeX.

CREATE DATABASE `bible_ref` /*!40100 CHARACTER SET utf8 COLLATE utf8_general_ci */

CREATE TABLE `bibles` (
`name_Full` TEXT NOT NULL,
`informatoin` TEXT NOT NULL,
`copyright` TEXT NOT NULL,
`owner` TEXT NOT NULL,
`contact` TEXT NOT NULL,
`contact_email` TEXT NOT NULL,
`permission` TEXT NOT NULL,

In any arbitrary bible display layout, the bible verses would be selected and copied to the clipboard,

TWScript system() calls would use a batch file (shell script) in the DOS path, which then calls a command line (CLI)  php to poll the MySql databse and bible_ref table to obtain and prepare material for the TwScript drop-down list for the User to choose from (covered below).

A LaTeX style sheet (for shared document usage) would format the verses,

... using \immediate\write18{da dahd dah} the .sty retrieves copyright information for only the

actual bibles used in a particular document,

and the .sty would keep track of the bible and verse usage,  

and insert an entry in a sperate index for bible quotes, for each verse portion referenced.

This would be achieved by Document calls to the .sty macros

We work a lot from portable path set ups, and so use intermediary batch files which are placed in the path (even temporarily set so) to explicitly call php in case php is unaware of various path aspects of our Tw and MiKTeX setups. In such circumstances php will require an absolute path to the php script to be run. And we have found this approach to be stable so far.

We reuse a lot of these functions so this is the flow...

From a function in the main script helper module (loaded at start up into a global variable. the module is viewable here helper_twPan.mod

var bibleList = this.osCmd('cmd /c getBibleNamesAbrs.php.bat', true);

return bibleList.split('\n');

And in getBibleNamesAbrs.php.bat

@echo off

rem make sure this bat file directory is in path

php \LaTeXPortable\LatexUtils\TeXworks\TeXworks\config\scripts\PHPtexworks\getBibleNamesAbrs.php

With bibleList assigned as an array getNamesAbrvs, it is used in this function call:

    userChoice  = TW.getItem( null,  "Bible Version ?", "Bible Version : ",

                                        getNamesAbrvs ,  choiceIndex , true ) ;


     var result = [];      


      if( userChoice == undefined)      


        result.abbrv= '';                    

        result.full= '';        




        var abrvStart = userChoice.lastIndexOf(' ');

// line returned from dialogue box split into component parts

         result.abbrv = userChoice.substr(abrvStart + 1);                    

         result.full  = userChoice.substr(0, abrvStart );  



        return result;  

Then a drop-down  asks for the book and chapter reference in a stipulated (conventional) form.

This ends up by inserting the following in the Tw Editor (script here) bibleVerseParNoHeads.js ...

Which displays as the above pdf output

The .sty  initially retrieves a list of current abbreviations from the MySql again through \immediate\write18

(portions shown below - present .sty version - rough and ready! 2011-07-31 available for perusal

here: panMagUseAa.sty)


\twScriptPhp used below stores a hardwired relative path to the php files needed,

         and applies it to a specific php file as pased.

\immediate\write18{php "\twScriptPhp{getBibleAbrsCommaList.php}" >  getBibleAbrvs.txt}




getBibleAbrvs.txt, depending on the current document's bible usage, contains a comma separated list like this


We chose for long term stability instead to use Heiko Oberdiek's catchfile package

This gives us a globally accessible variable \versionList which contains a list of currently stored bible name abbreviations for use in other macros. This is only retrieved once during the document typesetting but is used at least five times I think.

The \versionList varible is used in this manner:-



\newcounter{ver\val}% used for keeping track of bible verse usage on for each version of the bible



\newcommand{\checkVersion}[1]{% helper function





and among other places like this:-

%:      \bibleAllUsedCopyrights    \bibleCopyright{ useabreviation from MySql/bible_ref/bibles/Abbr}

%    and    \bibleAllUsedCopyrights does all where verse count above zero




\ifthenelse{\value{ver\val}>0}{\bibleCopyright{\val}}{}% // some verses from this bible version




\immediate\write18{php "\twScriptPhp{doBibleInfo.php}"  "copyright" "#1" "Abbr"  >  getScripturePermissions#1.txt}



No doubt those better acquainted with TeX and LaTeX could devise better more optimised macros, and would avoid the obvious mistakes that I have made (and somehow got a way with). I hope none-the-less that this project of ours may ideas wise,  prove helpful to others in solving their needs.

Created with the Personal Edition of HelpNDoc: Easily create PDF Help documents