(UTC)
  Lantica User Forum › Lantica Discussions › Lantica Discussion › Xlookup working for me!...sort of...
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Normal Topic Xlookup working for me!...sort of... (Read 915 times)
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Xlookup working for me!...sort of...
Apr 24th, 2009 at 2:47pm
Print Post Print Post  
I have multiple databases in one application.  I am working with two at the moment trying to extract a policy premium amount from one and placing it in an LE in the other.  

Using the following: XLOOKUP(@FN, policy_no, "ydm!policy_no", "tot_pol", ydm_prem)

Works like a charm! Being an admitted "newbie" when it comes to code I was ecstatic when it worked the first time!
However, like most things IT there is a caveat.  

Both databases (Policy and YDM) contain the same LE "policy_no".  Policy Number as the name implies is a perfect unique record identifier to use as a key, however, the caveat is that the third character from the left is different between the YDM and Policy database (in most but not all) records.  This is used internally to identify the type of coverage afforded by the particular general policy.

The policy numbers are exactly similar, however, in all other respects.  My thought is to try to use the 5 right most digits as the "metric" by which the "tot_pol" data from the YDM database may be replicated to the "ydm_prem" LE in the policy database for each specific policy.

I've tried various things including the following "trick" (which I am sure is "frankensteinian" i.e., adding bits and pieces that just don't work) with no luck.

XLOOKUP(@FN, @Right (policy_no, 5) , "ydm!policy_no", "tot_pol", ydm_prem)

As always, any and all suggestions are greatly appreciated.

Thank you...
  
Back to top
 
IP Logged
 
Ben
Lantica Support
*****
Offline



Posts: 218
Joined: Apr 7th, 2005
Re: Xlookup working for me!...sort of...
Reply #1 - Apr 24th, 2009 at 5:02pm
Print Post Print Post  
Hello Cliff,
Try the programming below:

Code
Select All
var vPolicyNo as String
	vPolicyNo = @ReplaceByPosition(policy_no, "?", 3, -1) //Replace the third character with a wildcard character
XLOOKUP(@FN, vPolicyNo, "ydm!policy_no", "tot_pol", ydm_prem)
  
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Xlookup working for me!...sort of...
Reply #2 - Apr 24th, 2009 at 5:43pm
Print Post Print Post  
Ben! That is simply amazing...what an elegant solution!
Kudos to you!!!
Thank you very much indeed...!
I imagined the solution to be far more complex spending the better part of the day trying to figure out a way to do this...
Amazing...!
Thanks..
  
Back to top
 
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Xlookup working for me!...sort of...
Reply #3 - Apr 24th, 2009 at 5:58pm
Print Post Print Post  
Cliff, it would be wise to use @Xlookup in any new code instead of XLookup. XLookup (no "@" sign) is only supported for compatibility with Q&A and is deprecated.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Xlookup working for me!...sort of...
Reply #4 - Apr 24th, 2009 at 6:01pm
Print Post Print Post  
Thanks, yes I did read that in the programming guide but being as "code challenged" as I am I figured the old school XLOOKUP seemed a bit easier for me to work with.

However, re: code challenged, I could be wrong about that...please let me know if that is the case.

Thanks much....!
  
Back to top
 
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: Xlookup working for me!...sort of...
Reply #5 - Apr 24th, 2009 at 6:47pm
Print Post Print Post  
XLookup does not let your code look at the value returned from the external record before writing that value to an element in your local form. So if you want to check that value, you end up reading a local element, saving that value, writing to an element, reading from that element, correcting any problems or restoring its saved value, thereby writing to that element again. With @Xlookup, you can use a conditional ("if" statement) to check if the returned value is what you want, before you write it to the element.
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
Cliff
Full Member
***
Offline



Posts: 126
Location: New Hampshire
Joined: May 5th, 2006
Re: Xlookup working for me!...sort of...
Reply #6 - Apr 24th, 2009 at 7:00pm
Print Post Print Post  
Thanks again Mark:

I am beginning to enjoy my foray into programming with these first steps.

It really is enjoyable work.

I am learning, albeit slowly and do appreciate your assistance and coaching.

Again, thank you...
  
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge