0

link two tables, two fields must be identic

Jet Reports Historic Posts

April 20, 2012 12:10

None

Follow

Hi,

I have a problem and have already tried many different things and formulas, but failed.
Hope, anyone can help me. I will try to explain what I need, but my English is not the best ;) I do even have problems explaining this in German ;) :?

So, I need rows of
"Purch. Rcpt. Line": "Posting Date" "01012011..31122011" ___ "Location Code" "WER*" ___ "Type" "Artikel"
That's no Problem ;)

But I only want those rows in which the fields "Order No." and "Order Line No." are identically with the rows in the table "Purch. Inv. Line" with "Posting Date" 01012011..16.02.2012 or ''

Should be like this:

Purch. Rcpt. Line ____________________________________________________________Purch. Inv. Line
No.___Order No._____Order Line No.____Posting Date____Location Code____Type_____No.______Order No.___Order Line No._____Posting Date
121___77100________100______________04.11.11_______WER1___________Artikel____E456_____77100______100_______________25.01.12
129___77111________100______________05.11.11_______WER1___________Artikel____E321_____77111______100_______________08.11.11
129___77111________200______________05.11.11_______WER1___________Artikel____________________________________________________________ (empty, because the article hasn't been invoiced yet)
129___77111________300______________05.11.11_______WER1___________Artikel____E340_____77111______300_______________09.11.11

Thanks a lot!! :)

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts April 23, 2012 20:44

  Hi,
  
  It sounds like you need to use Link= to link from the Purch. Rcpt. Line table to the Purch. Inv. Line. It might look something like this:
  
  =NL("Rows","Purch. Rcpt. Line",,"Posting Date","01012011..31122011","Location Code","WER*","Type","Artikel","Link=","Purch. Inv. Line","Order No.","=Order No.","Order Line No.","=Order Line No.","Posting Date","''|01012011..16.02.2012")
  Does that work for you?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 24, 2012 06:01

  Hi,
  
  thanks for your fast reply.
  
  Unfortunately this does not work.
  
  I changed this formula in a count-formula to see if it works.
  The result is 63000.
  If I change the "Posting Date"-Filter of the "Purch. Rcpt. Line" into 01012010..311222011, the result is 116000. So far so good.
  But if I change the "Posting Date" in the second part (Purch. Inv. Line) into 01012010..30042012 the result does not change.
  Seems as if no change of filter in the "Purch. Inv. Line" does have any consequences.
  
  I think there has to be a different result, hasn't it?

• 0
  
  Jet Reports Historic Posts April 25, 2012 17:22

  Are you actually running the report after you make changes by selecting Jet -> Report or Jet -> Refresh? Results in design mode are not guaranteed to be correct and things like link filters are not applied in Design mode in order to make report writing faster.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts April 27, 2012 06:30

  Hi,
  
  you are right. That was my mistake. :oops:
  
  I always thought that Jet calculates the "count-result" immediately.
  
  Thank you!!!!