Simplicty and flexibility!

New Sample - Labels... do I need to say more? (Ver

Started by DataEase
You will need to Sign In to be able to comment on the Blog!

New Sample - Labels... do I need to say more? (Ver

Download Sample

To enter in Design Mode: 
Username: high
Password: high

DataEase 8 is all about tearing down walls and remove boundaries. 

One almost insurmountable problem seem to be labels. Something that was so simple in DFD has been almost impossible in DFW.

If you managed to fit the labels on Page 1 they would miss on page two. They would print differently on different printers. You would get unwanted lines and borders...

Anyway. It might not be so much used anymore, but it is a good way to show off some of the new functionality in 8 so we had a go.

This application use ExecDQL(),PrintMemo(), MemoMemoCopy(), MemoReplace() and a lot of other neat 8 functions to achieve the goal.

Remember, this is a sample but feel free to "steal" it and make it your own, but remember that we do a lot of stuff in one place just to show off the functionality so you might want to use the functionality a little more "sparingly" and spread out ;-)

1. The Data-Entry Form (Print_Custom_Labels)

This is a "funny" little form where we have included a Subform as a Picklist. This is to show you that you don't have to use the traditional OpenRelated or CTRL-F10 stuff to build a dynamic pick list or search view. 

We use a normal Subform related to a virtual field that create the search filter. All that is easy, standard DFW and straight forward, but the big difference here is that we set the value in the Data-Entry fields directly from the search view with SetValue().

SetLabelText("From",concat("From customer (",CustomerName,"):"))+SetValue("From Customer", CustomerNr)+SetFocus("From Customer")
This is the "function" on the button. It changes the label in front of the From Customer field, it sets the value in the field and it gives  the field focus. 

Same stuff for the To field and voila... we can search and pick as much as we like in one form without having to open a lot of forms a lot of times ;-)

2. Generating the Labels.

Forget about the "old style" DQL, everything in 8 is about ExecDQL and the same here. To do the generation of the DQL we simply load a virtual memo with the DQL we want to use and execute it on   like this 

MemoExecDQL(DQL,From Customer ,To Customer ,"","","")
The DQL itself looks like this:

define temp "Telle" number .
define temp "tRow" number .
define temp "tColumn" number .
define temp "tTemporaryID" text .
define temp "retval" text .
define temp "tMCopy" number .
tMCopy := 1 . -- The switch that tell the MemoMemoCopy to Overwrite or append a page. First page is Overwrite, rest is append.
tColumn := 1 . -- Column number in array
tRow := 1 . -- Row number in array.
for Customers  with CustomerNR between data-entry field1 to data-entry field2; -- Here you set the selection criteria for the labels. Lazy so just use CustomerNr < but you can use anything.
if tColumn > 3 then --end of row x, ka-ching new line. Here you can read the 3 as a variable from the Template system instead if you want to make it generic.
tColumn := 1 . 
tRow := tRow+1 .
if tRow>6 then -- this is hardcode because we are lazy, you can of course look this up from your template form together with the template.
tRow := 1 .
Telle:=0 .
tMCopy := 0 . -- now 0 because we want to append another page.
modify records in Temporary with TemporaryID = "Avery1"  -- we use a pre-existing page because you can't insert and modify a page in the same DQL. Lazy again.
Dummy := if(telle=0, concat(MemoMemoCopy(Body, any Templates with TemplateID ="TL0002" Body ,tMCopy),"") ,"") ; -- here we insert the first page, and append the successive.
Dummy := MemoReplace(Body ,concat("[{CustomerName",tRow,tColumn,"}]"), Customers CustomerName ) ; -- here we dynamically replace the tags in the matrix
Dummy := MemoReplace(Body ,concat("[{Address",tRow,tColumn,"}]"),Customers Address  ) ;
Dummy := MemoReplace(Body ,concat("[{Town",tRow,tColumn,"}]"),Customers Town  ) ;
Dummy := MemoReplace(Body ,concat("[{PostCode",tRow,tColumn,"}]"),Customers PostCode   ) ;
Dummy := MemoReplace(Body ,concat("[{Country",tRow,tColumn,"}]"),Customers Country  ) .
tColumn := tColumn + 1 .    -- counting up the columns
telle := telle + 1 . -- lazy again, it never need to be more than 1 to make the trigger mechanise safe. 
retval := SetLabelText("Status",concat("Generate Labels finished. ",data-entry field1, " labels generated.")) .
retval := SetGlobal("TempFilter","Avery1")+DocumentOpen("Temporary") .

It might look a little heavy, but it is in fact quite neat.

What we do is to copy a pre-defined template which contain a table matrix with tags that is replaced. We copy it into the temporary form and run through the DQL to replace the 3x6 labels, when we reach the end we throw in a new page from the tablet with the same matrix and do it over and over and over until we have done all the labels. Because we use Memo which has a maximum size of 32k we can only generate 32 pages of labels this way...but hey?  that is 1152 labels so gets you started. You can of course simply re-write, use more than one record etc and get this to go infinite. We do this to inspire you, not to do the entire job for you ;-)

When the DQL is finished it simply set the status in the form and open the form Temporary which has a QBM filter TemporaryID=GetGlobal("TempFilter) so you will see the result of  your work.

Now you can simply hit the Print Preview button (either in the Webfield presentation or above...fool proof;-) and see the actual result of your work, or if you feel confident enough simply hit Print Direct and the labels get kicked out on the printer perfectly formatted.

That is all for now folks. There is some more goodie in that app, but you can see if you figure it out yourself.

Our next sample will be how to use a similar functionality to generate invoices, email them as HTML etc.

So watch this space ;-)

Written by DataEase 05/06/13 at 20:16:09 Dataease [{8}]FIVE

Re:New Sample - Labels... do I need to say more? (Ver

Update 06.06.13

We have now included some more cool functionality for you to rip off ;-)

The first example was just to show off how to print labels and that was just for fun, but then we got so much feedback that this was functionality that people wanted to use "as is", so we decided to make it a little more practical.

When you write labels you don't write from to a CustomerNR... You want to pick the addresses you want on labels and then print them off and only them.

And this is what we now have done.

Simply go in search up "google style" the records that you want, check them and search again. When you are happy with having selected all the addresses you want to print labels for, simply check the "Only print Selected Customers" check box and then Print Labels will be activated and you can print exactly those labels.

The way we do this is quite a lot of fun, and we actually load a different DQL with a Different with clause into the virtual DQL field in the Form.

This is stuff you can do now with MemoExecDQL().

Clear Selected and Refresh.

Another fun thing is how we clear the selected addresses and refresh the screen. This was origianlly a direct ExecDQL on the button but that can be a little tricky when you have to have a lot of "" (chr(34)) etc on the button

This is how it looked:

ExecDQL(concat("define temp ",chr(34),"retval",chr(34)," text. Modify records in customers with Selected=Yes Selected:=blank. retval :=SetValue(",chr(34),"Search",chr(34),",",chr(34),"A",chr(34),")."),"","","","","")

And you only have a limited space there, so we had to revert to our favoured MemoExecDQL and load the DQL from our DQLStore.

Only problem with that method is that we need to either have a fresh relationship or take advantage if to existing one.

The existing one was originally hardcoded to: 303 (which is the DQLNr in our ExecDQLStore form) but then it was changed to

 if (Only Print Selected Customers =yes, 304,303)

After we introduces the Selected feature. For that we used a checkbox which is a field/column and will trigger the virtual field to re-derive, but what about if I want this to be triggered from a button?

Simply take advantage of some of the new beauties of DataEase 8. SetValue(), Wait() and of course MemoExecDQL() in full harmony ;-)


This is the action on the button, and it is almost too good to be true. The field DQLNr is virtual and derrived, but still we can "force" its hand with SetValue().

SetValue() then trigger DQL which is a virtual Memo field with derivation.

lookup "ExecDQLStore" "DQL" 

to re-load and now loads the new DQL with DQLNr 305 which is the one that rest all the Selected checkboxes and refresh the screen.

But in the "old" days this wouldn't have worked because the functions would have just been sent to the stack and fired in "random" order, so that most likely the MemoExecDQL() would have been fired before the DQL field had reloaded, but not so. The trick here is simply to call Wait() with a miniscule delay - Wait(0.1). This causes the first function to fire, it waits till it finishes and then fire the next... so we get everything in the right sorted order...Finally!

And this is the simple DQL that clear up and then clear the form.

define temp "retval" number .
modify records in customers with Selected = yes
Selected := blank .
retval := FormClear() .

You will find the download link to the updated sample on the top of the main article.

Written by DataEase 06/06/13 at 13:19:02 Dataease [{8}]FIVE