Function::String
AnyLookup
AnyLookup("tablename?query", "fieldname")
AnyLookup("DQLs?ID=/'DQL000123/'","DQL")
retval := AnyLookup(concat("Customer?CusomerID=",customerid),CustomerName)
Traditional LOOKUP in derivation is dependent on a pre-defined relationship. This is very convenient when you want to look up a number of Data based on an ad-hoc query/relationship.
This function will not use pre-existing relationships.
NB!Implemented in 8.6.0.3206 and will not work in 8.5 or below.
Parameters
Tablename?Query: If you just enter a table name it will return values from the first record in the table, if you enter a query it will return the values contained in the first record of the query. Hint. Use a query that return only one record i.e. unique key etc.
Column (Fieldname): The name of the field you want the function to return the value of.
Returns/Result
A Text (string) value of up to 4GB. It will return the value of any column as a text and works on all column types from yes/no via data/time/number/text to Memo (4GB)
Examples
Examples:
execdql(anylookup("DQLs?ID=/'DQL000123/'","DQL"))
Returns the value of the DQL field in the table named DQLs and the ID="DQL000123" and use the value returned as the code in an execdql
Reference
See Also
On the forum about AnyLookup
AnyLookup()/Timer Event and general info...
Download Sample
<span style="color: rgb(192, 80...
Product: DataEase 9 Developer. Written by DataEase 29/01/20 at 17:25:45
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 lo...
Product: DataEase 9 Developer. Written by Paul Cheeseman 03/02/20 at 15:25:11
Re:AnyLookup() to multiple forms
Hi again Paul.One example I have used that worksAnyLookUp(Concat(COUN_LookupForm1,"?Disp_Counter=",COUN_Counter*," "), "DISP_Address1")AnyLookUp(Concat("ADV_Wea...
Product: DataEase 9 Developer. Written by DataEase 04/02/20 at 14:21:40
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=/'",...
Product: DataEase 9 Developer. Written by Paul Cheeseman 04/02/20 at 16:27:32
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!MyAddressMyTownMyCountyMyAddress MyTown<...
Product: DataEase 9 Developer. Written by Paul Cheeseman 04/02/20 at 16:45:32
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...
Product: DataEase 9 Developer. Written by DataEase 04/02/20 at 17:17:49
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!...
Product: DataEase 9 Developer. Written by Paul Cheeseman 05/02/20 at 15:02:57
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 red...
Product: DataEase 9 Developer. Written by DataEase 05/02/20 at 16:05:48
Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms
Version 9.0.0.3995Its 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 oth...
Product: DataEase 9 Developer. Written by Paul Cheeseman 05/02/20 at 16:09:33
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 p...
Product: DataEase 9 Developer. Written by DataEase 05/02/20 at 16:32:09
Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms
CRM_CustomerFile Field derevation: CUST_MailAddressconcat ( CUST_Address1 , if ( CUST_Address1 = blank , "" , "" ) , CUST_Address2 , if ( CUST_Address2 = blank , "" , "" ) , CUST_Address3 , if ( CUST_Address3 = blank , "" , "" ) , C...
Product: DataEase 9 Developer. Written by Paul Cheeseman 05/02/20 at 16:54:43
Re:Re:Re:Re:Re:Re:Re:Re:Re:Re:AnyLookup() to multiple forms
[@EOF@]...
Product: DataEase 9 Developer. Written by Paul Cheeseman 05/02/20 at 17:07:28
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 ;-)...
Product: DataEase 9 Developer. Written by DataEase 05/02/20 at 17:53:18
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....
Product: DataEase 9 Developer. Written by Paul Cheeseman 05/02/20 at 18:02:39
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...
Product: DataEase 9 Developer. Written by DataEase 05/02/20 at 18:58:34
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.Thanks...
Product: DataEase 9 Developer. Written by Paul Cheeseman 06/02/20 at 10:09:24
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...
Product: DataEase 9 Developer. Written by DataEase 06/02/20 at 10:40:08
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?...
Product: DataEase 9 Developer. Written by Paul Cheeseman 06/02/20 at 15:46:37
: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....
Product: DataEase 9 Developer. Written by DataEase 06/02/20 at 16:41:42
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 i...
Product: DataEase 9 Developer. Written by Paul Cheeseman 06/02/20 at 17:28:43
Re:Re::AnyLookup() to multiple forms BUG! ?It is now fixed in DE9 over 9.0.0.4496
Thank you to Paul for finding and pointing out this bug to us.It is now fixed in DE9 over 9.0.0.4496...
Product: DataEase 9 Developer. Written by DataEase 07/02/20 at 13:48:05
On the blog about AnyLookup