Linked table not returning correct value

Discussion & Feedback for Jet Enterprise
dmc
Posts: 8
Joined: Thu Jun 21, 2012 6:55 am

Linked table not returning correct value

Postby dmc » Tue Feb 28, 2017 12:16 pm

I've attached what appears on the tables for the NL formula below which is not returning the correct value. I tried checking the knowledge DB but couldn't find anything that would give me the correct solution.

I want the formula to pick up the correct PBN based on the set “EXP_DATE” of 1/31/16 so that I can get the correct “PREMIUM” of $29.00. The formula below is giving me a “PREMIUM” of $19.33. The common factors in the two tables are the “GRP_NBR”. What is the correct link formula to return the correct premium?
=NL("first","pbn","PREMIUM","DataSource=","Oracle","GRP_NBR",$L3,"INSURER",$E3,"PLAN_TYPE",O$2,"Link=","GRP_PLAN","GRP_NBR","=GRP_NBR","PLAN_TYPE",O2,"EXP_DATE",N3)
Attachments
I want the formula to pick up the correct PBN based on the set.docx
(34.21 KiB) Downloaded 17 times

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

Re: Linked table not returning correct value

Postby HPDeskJet » Wed Mar 01, 2017 4:22 pm

To start, what happens if you add DATASOURCE= to your link?

Code: Select all

=NL("first","pbn","PREMIUM","DataSource=","Oracle","GRP_NBR",$L3,"INSURER",$E3,"PLAN_TYPE",O$2,"Link=","GRP_PLAN","GRP_NBR","=GRP_NBR","PLAN_TYPE",O2,"EXP_DATE",N3,"DataSource=","Oracle")
-HP
_______________________
Jet 2015 Certified Trainer

dmc
Posts: 8
Joined: Thu Jun 21, 2012 6:55 am

Re: Linked table not returning correct value

Postby dmc » Thu Mar 02, 2017 7:47 am

No that didn't work, it's not picking up the correct PBN number, which should be 2 based on the EXP Date of 01-31-2016 and PLAN_TYPE(PLAN) of CI on the GRP PLAN table. I want it to automatically be able to pick of the PBN number based on all the other criteria in the function. The only issue may be that the GRP Plan table says PBN and the PBN Table says PBN1. Don't know if this won't allow the correct PBN to be recognized. Can you link= on multiple criteria like the GRP_NBR & PBN?

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

Re: Linked table not returning correct value

Postby HPDeskJet » Thu Mar 02, 2017 8:35 am

Hello -

In order for LINK= to work, the contents of the fields on which the link is based must match *exactly*.

In the example you state (where the field in the PBN table contains "PBN1" while the field in the GRP_PLAN table contains "PBN") there would be no way to establish the link.

While it is possible to link two tables together based on more than one field (e.g., [table1].field1=[table2].field1 *AND* [table1].field2=[table2].field2), it sounds like you are asking about being able to link based on different *contents* of the the fields.

e.g., "If [table1].field1 is "xyz" and [table2].field1 is either "xyz" or "abc", I want these to match up".

To accomplish this, I would recommend using an NL("Filter") function in a separate cell, and then referencing that from your NL(Rows) function. This gives you a great deal of control over your filter values.

More information on NL("Filter") can be found here:

https://jetsupport.jetreports.com/hc/en ... 5001652367
and
https://jetsupport.jetreports.com/hc/en ... /218953168

I hope that helps
-HP
_______________________
Jet 2015 Certified Trainer

dmc
Posts: 8
Joined: Thu Jun 21, 2012 6:55 am

Re: Linked table not returning correct value

Postby dmc » Thu Mar 02, 2017 10:07 am

Would it be better to using the NP function so that it can return the correct PBN based on the exp date and plan type, and then use the NL function to return the correct value. Is it possible to nest these 2 functions together? Thanks for all of the help!

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 442
Joined: Wed May 26, 2010 3:40 pm

Re: Linked table not returning correct value

Postby HPDeskJet » Thu Mar 02, 2017 10:18 am

I'm not exactly sure what you have in mind, but...

There are (obviously) multiple methods of achieving just about any result. If you have an idea that will will give you what you need... Go For It!
-HP
_______________________
Jet 2015 Certified Trainer


Return to “How do I?”

Who is online

Users browsing this forum: No registered users and 2 guests