Cardbox Talk


CardboxForumsCardbox Talk > "Macro Programming"

Macro Programming

This will be a weird one, hopefully someone can help

Posted By Post


8-Apr-2008 14:39

I have set up a field where I use the file: command so that when I input a hyperlink location to a path on our server, we can access a file within our server that is particular to the Cardbox record we are viewing.


Is there a way of telling the field we are using as a hyperlink in Cardbox to replace any spaces automatically with the %20 command?

This is what the link originally looked like, which would not have worked if I had not replaced all the spaces with %20

Example: file:\\Server-alpha\master alpha\Saved maintenance Quotes

I have over 1000 hyperlinks to set up, where I need to replace spaces with %20 so Cardbox can see the full path and open up the server record.

At present I am copying the path location into word and using the Ctrl & F function to replace the space with the %20, then copying and pasting this into the Cardbox field.

Any advice would be appreciated.

R. Woodhouse

8-Apr-2008 16:35

You could check the solution given in other posts, but I had a similar problem which Cardboss helped me solve with a hyperlink macro tied to a push-button. I had a structured set of folders (F:\My Documents\consulting\catalog) and sub-folders below containing the source pdf documents. The path to the main catalog folder was contained in a field "P1" and the subsequent part of the path to the source documents (\IFPP\WP.15.pdf)was contained in a field "P2". Thus the main catalog folder and its sub-folders could be moved to another location and the filed "P1" updated by a batch edit. The macro was:

x=abspath & filename
Launch x

This works fine, and does not require %20 to replace all the spaces in the file path.

The other alternative suggested was to copy the source documents into the Cardbox Image field. I rejected this because a) the input copy process with pdf is slow, and b) I will need the ability to copy text from the source documents - this is possible from the pdf but not from the image (copy only gives you an image and not editable text).

Hope this is useful,


8-Apr-2008 22:59

Unless someone comes up with something much smarter, you could try using batch edit as follows:

    * load %20 onto the clipboard (enter in any field, highlight and press Ctrl+C - than quit without saving)
    * at the first record of the set:
          o choose Edit-Batch-Edit
          o go to the field with the paths to be edited
          o press Ctrl-right arrow (this will take you to the beginning of the word after the first blank space)
          o press Backspace (to delete the blank)
          o press Ctrl+V (to paste %20 from the clipboard)
                + press Ctrl-right arrow again (this will take you to the word after the next blank space)
                + press Backspace (to delete the blank)
                + press Ctrl+V (to paste %20 from the clipboard)
          o Repeat as many times as blanks in the path of the selected set

This of course will only work for the paths of the same format, i.e. same number of blank spaces
 e.g. file:\\Server-alpha\master alpha\Saved maintenance Quotes - has 3 blank spaces

If you have multiple path formats (e.g. I would expect that the file names may have different numbers of words), you will need to execute multiple batch commands each on sets with paths of similar structure, but it sure beats copy/past in word.

Good luck

Iuliana Pop,
ICAR Consulting: Library&Information Services
Voice: 905-884-9320mailto:icar-consulting@rogers.com
Providing services to the Pharmaceutical, Biotechnology & Health care industry


9-Apr-2008 06:25

What an amazing discussion... This is simply written in the Help and in the Cardbox Manual!
- Batch Edit,
- Go to the field where the spaces has to be replaced
- Then Ctrl-H (Edit-Replace), Find what? ... uuuh, a space ; Replace with? Type %20 (have attention, remove the space there). Leave all other things default (if you changed some thing: important is: "Look at" = everything and "in" = "this field"
- Push button Replace all.
- Save Record
Cardbox does the work in all selected records from the current record after it...

Mary Doyle (DAF)

9-Apr-2008 08:28

Bert's solution works fine in the normal way but does not work with spaces.
I myself use Robert's method above, which is very quick and flexible.

If you do want to replace your spaces here is a quick macro:

Be sure to select the records you want to loop through (batch edit) first!!

'Create object "recs"
Set recs = Records

n = recs.Count ' Count the number of records in recs

' Define i as records 1 to the last number, i.e. each record in turn

For i = 1 to n

Set rec = recs(i) Create object "rec" to equal the current record
x = rec.Fields("sm")
y = Replace(x," ","%20") ' " " indicates a space
rec.Fields("sm") = y

The above macro is very useful and can be easily adapted for different situations. You could also add an instruction in the macro to select the records first e.g. using the Pause command (see Cardbox macros manual). Actually, Pause does not seem to be in the Macros and Programming manual, at least not in the index. It can be used instead of the VbScript message box. Pause "Select the records you want to amend" ... It pauses the macro so that you can manually intervene and access the database e.g. to select and tag records.

Another way of writing the above macro would be:

n = Records.Count

For i = 1 to n

x = Fields("sm")
y = Replace(x," ","%20")
Fields("sm") = y

It is probably easier to see what is happening here, though it won't run as quickly.

Charles Welling

9-Apr-2008 09:01

I believe Alpha wants to have his/her spaces automatically replaced not only in existing records, where anything from Batch Edit to macros that loop through a selection will work fine, but also everytime a new record is saved.

Please that for some time you have been able to assign a macro to the Save Record command. This means that every time a record is saved, a macro will run before the record is actually saved.
The following lines will automatically replace the spaces within the field "LINK" with "%20":

Fields("LINK")= replace(FIELDS("LINK1")," ","%20")

You can assign any macro to the Save command by:

Editing the format where the macro must run, normally the native format so that the macro will always run.

Click Tools > Toolbar > Editing Records and look for the Save Record button. Change the action from "Save Record" to "Run Macro" and assign the above macro to the button. Both the button and the Save Record menu will run the macro. Don't forget that such macros must contain the SaveRecord command, or your record will not be saved.
You can also assign the same macro to the CTRL-s keystroke.

Having done so, any space within the field "LINK" will always be automatically replaced by "%20".
Such "on save" macros can be used to perform any task, including validation more extensive than the standard.


9-Apr-2008 09:48

For existing records, standard search and replace works perfect, also replacing spaces (I just tested it Mary!)

For new records: you have to add in your record a path and/or location of the files. What can be more simple to do this by using this macro:

if x = "" then halt
x = replace(x," ","%20")
fields("hyperlink") = fields("link") & " file:" & x

In this way you do'nt need a "file:" caption in your field. More: you can add more than one file in that field (one by one).

Btw: Adding your macro to the Cardox save command has to be implemented very careful. It is the best using this option only if you work with special workspaces for separated applications.


Mary Doyle (DAF)

9-Apr-2008 10:12

I would agree with Bert to be careful assigning a macro to the Cardbox save command. We do use it occasionally and it is very useful. However we build in certain conditions in the macro that it runs.

For instance
- if link field contains text then convert spaces to %20 and save, otherwise just save the record
- if open database = Catalogue then do xyx and save, otherwise just save the record
- etc
You need to be aware of the implications of running the macro if the conditions the macro requires are not met, or if the macro is run in the wrong database if, for example, you have several databases open in a workspace.

For existing records, standard search and replace works perfect, also replacing spaces (I just tested it Mary!)
Bert, It doesn't work for us. When we enter a space in the "Find what" box all commands except Cancel are greyed out.


9-Apr-2008 10:51

I cannot send you a screendump... On the moment I place a space or character in the "find what" line, "find button" and "replace all" are activated. So if this does not work in your case, there's something wrong!
Perhaps a profile? I use build 4263.

Charles Welling

9-Apr-2008 11:50

Just a short reaction to the "on save macro":

As to Bert's suggestion that it should be best used in special workspaces, I'd like to know what his arguments are. I've been using this feature in all my databases since it was added (on my request), and implementing it in the formats of the databases guarantuees that the assigned macro runs only in that database. So, even when a workspace has several databases open, the macro will only run in the database it was assigned to or even only in the format it was assigned to. As safe as the Bank of England (perhaps not the most realistic expression).
And as to being careful and being aware of all the implications: this goes for each and every macro that is written. Even macros that are started by hand can be disastrous if not properly tested.
Using macros this way greatly improves data integrity. Macros that should always be used but are assigned to buttons or menus will inevitably from time to time be forgotten.

Mary Doyle (DAF)

9-Apr-2008 12:48


You are quite right about being careful and aware of all the implications, regardless of the macro. I was just keeping my response simple.

Thank you for inspiring the "On save" macro. Very useful.



9-Apr-2008 13:05

You wrote some arguments by yourself:
- You need to test very conscientious to find out what are the consequences of replacing/enhancing a standard Cardbox command;
- I think that non-developers often cannot find out what consequences are of their macros, so damage of data is not unthinkable;
- Then: does your save record macro work also in (standard) batch-edit and “save-as-new etc?” Or do you need to programme for al these functions separate macro's to get Cardbox consistent and Cardbox transparency back?
- If you use Cardbox databases for very different goals (I have some 10), it is almost impossible to enhance standard commands in the standard workspace.

So, in my opinion is it safer to use this part of Cardbox in special dedicated applications in special dedicated workspaces. The standard Cardbox workspace is in my opinion the best as it is... standard.


Charles Welling

9-Apr-2008 13:59

I never suggested that there should be a single "on save" macro for all databases, or that perfectly working standard commands should be replaced by non-standard ones. I simply advocate the use of the "on save" mechanism because it runs transparently. Users don't see it and they can't stop it. They just see that spaces are replaced by "%20" or by "_". Or they may get messages that if they filled out field A, field B must also contain something etc. etc.

Each and every one of my databases (27 to be exact) has its own "on save" macro, assigned to the native format. These macros vary from just a few lines (mostly to replace and improve standard validation) to extensive macros for an ordering system (sending confirmation e-mails, compute VAT etc.) and even a macro of over 400 lines that maintains a thesaurus. They all work "on save" and in their own databases.
In all cases these macros save an enormous amount of effort and time. I repeat that the main advantage of this kind of macro is that it cannot be forgotten by the user and that it runs at a predetermined moment. This enables the developer to take all circumstances into account. Any mistakes that a developer makes can allways be made, no matter when the macro runs.

The Save as New button can also have a macro assigned to it, either the same as "on save" or another. You can even make a single macro behave differently depending on whether it works on an existing or on a new record.

The only thing that does not work when the Save button has been redirected is Batch Edit. However, none of my users is allowed Batch Edit because the average user is very likely to bring disaster to the database. When told of the consequences of a rash Batch Edit, every user up to now happily declined his or her right to use it.

"Superusers" however may be granted a way out of the "on save" macro by assigning a special and highly secret keystroke to the internal Save command. Hitting that key instead of CTRL-s (or the button/menu) will activate the save command instead of the macro. Or the "on save" macro may be made part of the non-native formats, whereas the native format is left unchanged. Users that may use the native format may then save a record in the standard way. Thus you can have your profiles dictate who uses what.

Globally speaking: anything that automates a task, however simple, reduces the time that is needed to perform it, and reduces the risk that it goes wrong. Every user who enters data is likely to make mistakes or to be forgetful. It's a real comfort to know that when that user hits "save", a macro checks whatever it can check, corrects whatever it can correct and finally says to the user, in his own language: "Well done Bert, no mistakes!"


9-Apr-2008 14:22

You're right - with this all *this* info I agree with you.
But in gereal say "enhance standard functions in Cardbox by macros", that is a little too much I think for many.
I only meant to say "be careful" know what you are doing!"

Mostly I add functionality visible, transparent. So users can see that they are using this functionality.



10-Apr-2008 08:49

I Must thank each and everyone of you who have contributed to this discussion so far, although i am a simpleton when it comes to macro writing, I have no programming background, the arguments and comments posted are fantastic. I am just pleased to see so may knowledgeable people trying to solve a common problem

Mary Doyle (DAF)

10-Apr-2008 09:59


I was once a simpleton too, with no programming background. Necessity for me was certainly the mother of invention. I first started with the Cardbox macro system and was very pleased with what I achieved over the years. When Cardbox switched to Vbscript I was convinced I would never master it. As a non-programmer I found a lot of VbScript books quite daunting. I found "Learn Microsoft VBScript In a Weekend" by Jerry Lee Ford Jr., Premier Press, ISBN: 1931841705 a great book to get me over my fear. It is a real beginner's book (not for the experts, so ignore criticism from expert programmers). You won't learn it all in a weekend of course and it won't teach you how to use Vbscript with Cardbox, as the examples tend to relate to the web. However, it will teach you the basics in a very unintimidating way. Used in conjunction with the Cardbox macros and programming manual it could bring you a long way if you are interested. Even when I had become fairly accomplished in some areas I found myself going back to it to get started on programming for web pages and file management. Our entire library system is now managed by Cardbox and Vbscript programming. In addition to standard library activities we also use Cardbox to automate many other activities such as updating our intranet pages, or tracking the status of our legislation, all of which would be too labour-intensive to do without using macros. My motto has been "there must be an easier way of doing this!". That is a strong motivation for learning programming.

Good luck.


© 2010 Cardbox Software Limited   Home