0

G/L entry to Vendor table link

Jet Reports Historic Posts

November 05, 2012 11:27

None

Follow

I have a simple report over the G/L entry table linking to the Vendor table to pick up Vendor name and address where the source no. (G/L Entry) and No. (vendor) fields are the link.
A problem arises where a vendor no. is also a customer number. The report reads the number in the Source No. field and uses it to retrieve vendor name and address but this is not correct as it is a customer number not a vendor number.
How do I incorporate the source type field into my link / report i.e. only use the Source No. to retrieve the vendor name and address if the Source Type = Vendor?

Thanks

• Facebook
• Twitter
• LinkedIn

5 comments

• 
  Jet Reports Historic Posts November 05, 2012 17:25 Official comment

  Hi Mmccabe,
  
  You can combine an Excel IF function with an NL("First") to do this. How about something like:
  
  Cell C5: =NL("Rows";"G/L Entry"… filters…)
  Cell D5: =NF($C5;"Source Type")
  Cell E5: =NF($C5;"Source No.")
  Cell F5: =IF($D5<>"Vendor";"";NL("First";"Vendor";"Name";"No.";$E5))
  
  Does that work for you?

• 0
  
  Jet Reports Historic Posts November 09, 2012 11:20

  Hi Hans
  Thanks for you reply but still can't get this working. If I use an 'IF' function on the report the column is not shown when I refresh. In fact, if i use an NL function the column is not shown when i refresh. I have attached the report. (Columns M-Q are dimensions). Column V is not shown when I refresh the report. In my initial report cell V10 contained the following: =NL("Linkfield","Vendor","Name"). This worked ok except for the problem i reported below whereby the source no. is both a customer and a vendor no.

  • GL entries by dim inc vendor name and address - WORKED.xlsx
• 0
  
  Jet Reports Historic Posts November 09, 2012 15:47

  Hi again,
  
  So you're using an NL(Table)…. hmmm… don't think you can do the IF there. But maybe someone else knows how to do that.
  
  You can use NL(Rows) though to generate your report. Why don't you give it a try?

• 0
  
  Jet Reports Historic Posts November 09, 2012 17:08

  Hi Hans
  I redesigned the report using NL(Rows) and it worked fine. No noticeable difference in performance.
  Thanks for your help.

• 0
  
  Jet Reports Historic Posts November 22, 2012 05:59

  Hi mmccabe & Hans,
  
  I believe "if" statement working just fine inside NL (Table),
  
  Please try this one, copy paste the following code to cell V10:
  
  =NP("Formula","=if([@[Source Type]]=""Vendor"",NL(""Rows"",""Vendor"",""Name"",""No."",[@[Source No.]]),"""")")
  cheers :mrgreen:
  Andy