0

NF function blank with NL Rows=

Nick

September 02, 2024 01:09

None

Follow

Updated to v 24.3.0.2341 today.

Running a report with nl rows=4, the NF function referencing that function on all subsequent lines is not returning any data.

Cell B2 contains formula to return all sales headers for a salesperson:

Cell C4 returns the customer number from B4 which is used to find the customer name etc. Cell B6 has NL formula to return the sales lines with outstanding items.

When run we get the following error

And the report fails to return customer details as cells in column C are blank. All data from replicating cell B6 is correct, but no "Rows=" used.

I don't know if I'm missing something obvious or this report style no longer works with new versions of Jet?

Any help appreciated.

 

 

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Brendan Ryan September 03, 2024 16:47

  In your NL Rows=4 function, at the end with =NL(Rows=4...,+No.,"*"), if you change that to +No.,"@@*") does it fix it at all? Adding the @@ to the NO. reference

• 0
  
  Nick September 03, 2024 23:17

  Thanks Brendan, but changing as suggesting or even removing that part of the formula makes no difference unfortunately.

  I think the cell giving the error on "No." field was F8 in above screenshot which is an NL to return the customer name for the customer no. NF in cell C8.

  Very strange but seems to me there is a problem referring NF to NL with "rows=" when there is another NL nested within those free rows. When I removed all the formulas from row 6 and re-ran the original report all the NF data was populated as expected.

   

  The fix I made was to add another NL in E4 filtering to the sales order number returned by the NF in D4 as this was the only NF working in the original report. All the other NF on that row now refer to E4.

  

  Seems to be working as expected for now.

• 0
  
  CascadiaDataworks September 18, 2024 20:24

  If you want a second opinion on this, reach out to my username at gmail.

  There may be a design flaw in the report which often happens with grouping reports.  Happy to do an initial review for free.