Simplicty and flexibility!

AnyLookup() to multiple forms

Started by Paul Cheeseman
You will need to Sign In to be able to add or comment on the forum!

AnyLookup() to multiple forms

AnyLookup() is another function that I have been testing.
I have a test setup, so field names do not always match data I am looking up, but the field type is always correct, ie text.

I am getting differing results with this. Some forms the lookup works exactly as expected, including either using fixed formnames ("ADV_Session") or looking up the same value from a field, either typed in or derived. Other forms, it does not matter how I set it up, it just refuses to return data.

One example I have used that works

AnyLookUp(Concat(COUN_LookupForm1,"?Disp_Counter=",COUN_Counter," "), "DISP_Address1")
AnyLookUp(Concat("ADV_WealdenDisplays","?Disp_Counter=","10001234"," "), "DISP_Address1")

Another that refuses to work:
AnyLookUp(Concat(COUN_FormName,"?CUST_Unique=",COUN_CustUnique," "),"CUST_Company")
AnyLookUp(Concat("CRM_CustomerFile" ,"?CUST_Unique=","Weald53322"," "),"CUST_Company")

There are several of these that work and several that don't, so it isn't isolated. On the ones that dont work, I have systematically gone through the various permutations of fixed data values and field data values, and am fairly sure it isnt just a simple syntax error (although some of the tests I have done, there ARE syntax errors which get highlighted)

Is there some form of limitation on forms that could be stopping these working? Am I missing something on the formulae that needs to be there?


Written by Paul Cheeseman 03/02/20 at 15:25:11 DataEase 9 Developer

Re:AnyLookup() to multiple forms

Hi again Paul.

One example I have used that works

AnyLookUp(Concat(COUN_LookupForm1,"?Disp_Counter=",COUN_Counter*," "), "DISP_Address1")
AnyLookUp(Concat("ADV_WealdenDisplays","?Disp_Counter=","10001234**"," "), "DISP_Address1")

* This works because you use a field.
** This works because you use a number.

It is not as hard as it seems but it might be "voodoo" before one get into the right frame of mind.


STRING1 is a valid text string that set a valid filter on a table.
STRING2 is a string containing a valid field/column name.

In DE8 onwards we mainfly add functionality by adding functions. It kind of builds on the idea of CDFs where you can make any new feature as long as it is a function.

The difference between a function and a command, operator etc. is how its called and parsed.

If we want to add any new command or operator etc to DataEase we need to change all the code parsers i.e. change the language. A function on the other hand is an easy addition and its easily included in the language as it follow some very simple rules. 

Functions can be an argument in other functions or they can simply be added on to something and the only demand is that they are called according to their own rules/limitations.

So you can say that functions in DE8 onwards are more like commands or functionality than traditional functions which job it was to return a computation etc. Now functions can be manipulators or functionality that strictly speaking don't return anything useful but do something useful instead (normally the job of a command).

The limittation however is that the input to a function need to follow the rules too and as the input to AnyLookup (or ExecDQL) is basically DataEase command language it need to comply with those rules.

AnyLookUp(Concat("CRM_CustomerFile" ,"?CUST_Unique=","Weald53322"," "),"CUST_Company")

AnyLookup is the Function "sister" of Any in DQL 

any CRM_CustomerFile with Cust_Unique="Weald53322" CUST_Company .

AnyLookup use a slight different format as it is related to DataEase web URL format

What you have tried to execute is this:


But when you try to compare with a string it need to be encapsulated in " " so what you need to execute is below


I guess you now see the problem, you can't encapsulate "" inside ""...

Which is why you need to use the escape version of " which is /'

AnyLookUp(Concat("CRM_CustomerFile" ,"?CUST_Unique=","/'Weald53322/'"," "),"CUST_Company")

The same goes anywhere you want include a string constant inside another string.

execdql("define /'mytemp/' text . mytemp := concat(/'Todays date: /',current date ). message mytemp window .") 

Written by DataEase 04/02/20 at 14:21:40 DataEase 9 Developer

Re:Re:AnyLookup() to multiple forms

When you word it like that, passing a string within a string, it makes more sense. Escape strings make it interesting!

Changing the test fields to this for the text field based lookups works:
AnyLookUp(Concat(COUN_FormName ,"?CUST_Unique=/'",COUN_CustUnique,"/' "),"CUST_Company")

AnyLookUp(Concat("CRM_CustomerFile" ,"?CUST_Unique=/'",COUN_CustUnique,"/' "),"CUST_Company")

or simplified AnyLookUp(Concat("CRM_CustomerFile?CUST_Unique=/'",COUN_CustUnique,"/' "),"CUST_Company")
Now I just need to remember the key type, and apply the appropriate version.

Thank you :)

Written by Paul Cheeseman 04/02/20 at 16:27:32 DataEase 9 Developer

Re:Re:Re:AnyLookup() to multiple forms

One interesting oddity, when I apply this to a lookup for an address field, where its a concat(many, address, fields) on multiline display, it shows the address text twice like this!





Written by Paul Cheeseman 04/02/20 at 16:45:32 DataEase 9 Developer

Re:Re:Re:Re:AnyLookup() to multiple forms

When you know about this you very quickly start to use keys that are numerical as it save you a lot of hassle ;-)

filters like this is very much how you do things on the web. Free relationships like this first appeared in Dataease Generation 3 on the web where you don't really use fixed relationships to link elements together at all.

Written by DataEase 04/02/20 at 17:17:49 DataEase 9 Developer

Re:Re:Re:Re:Re:AnyLookup() to multiple forms

Is there a way to stop this firing twice? I do remember somewhere on the site it was mentioned, I just cant find it!

Written by Paul Cheeseman 05/02/20 at 15:02:57 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

Two questions:
What version of DE9 are you using now?
How are you calling it? I.e. button, virtual field, OML?

There was a lot of events that fired not just twice but even more than that but we hope and believe that we have managed to reduce this now so it should only call once.

We have even split ValueLoad() and ValueChanged() events so the first is called when a record is loaded and the second for every subsequent change.

Written by DataEase 05/02/20 at 16:05:48 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms


Its from a field, with this as derevation:

AnyLookUp(Concat("CRM_CustomerFile?CUST_Unique=/'",SESS_CustomerUnique ,"/' "), "CUST_MailAddress")

Shows fine on a normal lookup, but anylookup() duplicates. All the other Anylookup() derivations seem fine, just this one!
The MailAddress field is a virtual, combining the address details into one field.

I have tried adding extra escape quotes , as I seem to remember surrounding the formulae stops it going round again, but as mentioned, I can't find that original post.

Written by Paul Cheeseman 05/02/20 at 16:09:33 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

1. Your versions is ages old. Download the latest version.
2. WHen you say it doubles do you get the result double in the field? MyNameMyName? or does it call he lookup twice.

Can you attache the entire derivation in the field as well as a picture of the result.

Written by DataEase 05/02/20 at 16:32:09 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

Download: de-sess_mailaddress.jpg

CRM_CustomerFile Field derevation: CUST_MailAddress

concat ( CUST_Address1 , if ( CUST_Address1 = blank , "" , "
" ) , CUST_Address2 , if ( CUST_Address2 = blank , "" , "
" ) , CUST_Address3 , if ( CUST_Address3 = blank , "" , "
" ) , CUST_Town , if ( CUST_Town = blank , "" , "
" ) , CUST_County , if ( CUST_County = blank , "" , "
" ) , CUST_Postcode)

ADV_Session Field Derevation: SESS_Address

AnyLookUp(Concat("CRM_CustomerFile?CUST_Unique=/'",SESS_CustomerUnique ,"/' "), "CUST_MailAddress")

Written by Paul Cheeseman 05/02/20 at 16:54:43 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

Written by Paul Cheeseman 05/02/20 at 17:07:28 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

So you lookup the concatenated field into another form and then it suddenly has double the value.

Sorry for being so slow ;-)

Written by DataEase 05/02/20 at 17:53:18 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

Yep thats right. I just did the same on another set of forms, same types of fields and it did the same.

Written by Paul Cheeseman 05/02/20 at 18:02:39 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

The only thing I can think is your version is too old. It predates anything we ever released and we have no control of what is in it.

Download the latest version from Download here.

if you have some problem in new version, make a sample that show it and upload it.

Written by DataEase 05/02/20 at 18:58:34 DataEase 9 Developer

Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms

This is now on the newest version, I did that yesterday.

I'll mess around with it a bit more today, then make an upload for you.

Written by Paul Cheeseman 06/02/20 at 10:09:24 DataEase 9 Developer

Re:AnyLookup() to multiple forms

Hi again Paul.

Can't wait to see what cause this. I'm convinced its not the functionality itself now so it must be something cyclic.I guess we will both laugh when we finally figure it out.

PS! I removed 20 re: from the reply...maybe we should get rid of the re:re feature on here...he,he.

Written by DataEase 06/02/20 at 10:40:08 DataEase 9 Developer

Re:Re:AnyLookup() to multiple forms


Ok, I have made a small demo, two forms. CustomerFile has two customers, Display form has one advert each. Same lookup, same issue.

Hopefully this one will be an obvious fix?

Written by Paul Cheeseman 06/02/20 at 15:46:37 DataEase 9 Developer

:AnyLookup() to multiple forms BUG! Found

Hi Paul.

Thanks for not giving up on us.

No smoke without fire...

Luckily there was a couple of trigger points so our QA doesn't have to fall on their sword just yet.

It is a combination of circumstances and the problem is the "donor" field
1. Virtual Field.
2. The value in the virtual field is the result of a string function like concat() or Firstc() etc.
3. You look it up with AnyLookup()

We will fix it but the quick fix for you is simply to convert the donor field from Text to Memo.

Memo in DE9 can be virtual and up to 4GB. All string functions support return values up to 4GB, so you can concatenate memos together and build very advanced structures.

Written by DataEase 06/02/20 at 16:41:42 DataEase 9 Developer

Re::AnyLookup() to multiple forms BUG! Found

OK at least there is a pointer :)

Not used those memo fields yet, we do have some that will benefit from the conversion. Easy steps for now though!

Giving up on you is not an option! We want to make this conversion and update work. There is so much potential here, It just may take some thinking through for us :)


Written by Paul Cheeseman 06/02/20 at 17:28:43 DataEase 9 Developer

Why Memo means great in DE9

Memo fields just to scare people to death ;-)

Mainly because of migration. From 6.x to 7.x all text fields that was longer than 255 (longtext) was converted to memo.

The fact that memo was extremely bugy and you were very likely to loose all the content suddenly and irrecoverably was not the main reason why peope shyed away from them - it should/would have been enough - but they were also extremly slow and you couldn't manipulate the content or use it outside 255.

This was changed in DE8 where the quality and stability was vastly improved when at the same time you got a lot of functions that took advantage of this larger storage capacity as well as help you manipulate the content. The speed issue was still there if used as a slightly larger text field which is why we re-introduced longtext (up to 4k) and fixed migration from 6.x to 8.x so longtext (memo in 6.x was migrated to longtext). You could also have virtual memos in DE8 which was a great way to keep a lot of data in a form, or send it from one form to another (lookup).

But it in DE9 it really come to its own. Now it is expanded from 64k to 4GB and all text functions can handled up to 4GB, you have got memo variable type in DQL and you can of course also put a a 4gb  memo into a global variable with Setvar()/GetVar().

At the moment we are working with freetext search in an entire table, so you can search in a DQL for instance 

For Table with Table="My Name" ;
list records

And it will search in all fields including Memo and return all the rows where "my name" is anywhere in the field, memo.

Memo was completely reworked for DE9 and is not rock solid but it should not be overused. 

When you use Virtual fields there is no downside to using a memo, and it is not even a downside when you use it in a table, but obviously if you store a little data (typical a name) and use a memo it will be a very inefficent way of doing it.

Written by DataEase 06/02/20 at 17:31:38 DataEase 9 Developer

Re:Re::AnyLookup() to multiple forms BUG! ?It is now fixed in DE9 over

Thank you to Paul for finding and pointing out this bug to us.

It is now fixed in DE9 over

Written by DataEase 07/02/20 at 13:48:05 DataEase 9 Developer