0

Sheet Names in NL(Sheet)

Sandeep Kuntam

June 03, 2019 19:38

Answered

Follow

I have seen a few different problems with NL(Sheet) and a few different solutions for the problems. I feel like I have a unique problem and would really appreciate any help that I can get. 

I am trying to generate sheets based on 2 lookup() for account segment 1 and account segment 2 in Dynamics GP. I can generate sheets and the function works great except for the sheet names.

Right now I am using the following function
=NL("Sheets","GL00100",{"ACTNUMBR_1","ACTNUMBR_2"},"ACTNUMBR_1",$F$5,"ACTNUMBR_2",$G$5)

 but the sheet name is as follows 

"DataSource","Company","GL00100","ACTNUMBR_1","10","ACTNUMBR_2","000"

How can I change this long name to probably "10-000" based on the two account segments or probably even like "10000". 

• Facebook
• Twitter
• LinkedIn

22 comments

• 0
  
  Harry Lewis June 03, 2019 20:21

  Hello Sandeep -

  With Dynamics GP, I know of no way to concatenate two fields together with the NL(Sheets) function.

   

  The best I could suggest for this would be one of two methods:

   

  Option #1)  Create sheets based only on ACTNUMBR_1 and then have sections within each sheet for ACTNUMBR_2.

  Something like this:

  

   

  Which would result in:

  

   

   

   

  Option #2)  Pre-define all of your account combinations.

  e.g.,

  

   

  Resulting in:

  

• 0
  
  Harry Lewis June 03, 2019 20:41

  Hello Sandeep -

  I have another option for you.

  I needed to make sure that my SQL statement would work correctly.

  By using the " SQL= " feature, you could accomplish this:

  

   

  This would result in something like this:

   

  

   

  {obviously, you would want to hide the row or column of the cell containing your SQL= statement}

• 0
  
  Sandeep Kuntam June 04, 2019 18:29

  Thank you Harry

• 0
  
  Sandeep Kuntam June 11, 2019 19:29

  Hello Harry,

  I might need your help to use the SQL= function. I tried the exact same query as you showed me above, however i seem to get the #VALUE! error. Could you help me understand why I am not able to do that? 

  We are using Jet version 20.0.19116.1

   

• 0
  
  Harry Lewis June 11, 2019 19:40

  Hello Sandeep -

  When you select the cell displaying #VALUE! and click on the Check Error button on the Jet ribbon...

  

  What information is displayed?

• 0
  
  Sandeep Kuntam June 11, 2019 19:41

  Invalid Table 'SQL = SELECT ACTNUMBR_1 +ACTNUMBR_2 AS "Numbers" FROM GL00100'.

• 0
  
  Harry Lewis June 11, 2019 19:45

  With the cell containing the SQL= text selected (and the Show Formulas activated from the Formulas ribbon), ensure that the columns are wide enough to see the contents and take a screen shot and paste it here.

  Like this:

  

• 0
  
  Sandeep Kuntam June 11, 2019 19:50

  

  

• 0
  
  Harry Lewis June 11, 2019 19:52

  Try this in cell D3:

   

  SQL=SELECT ACTNUMBR_1+ACTNUMBR_2 AS "Numbers" FROM GL00100

   

• 0
  
  Sandeep Kuntam June 11, 2019 19:56

  was it the space that was causing the issue in my formula? because that is the only difference i saw was the space between ACTNUMBR_1 and 2. But the formula works

• 0
  
  Harry Lewis June 11, 2019 19:58

  Yes.

  Like all special filters (Headers=, Limit=, etc.), "SQL=" cannot have an embedded space.

• 0
  
  Sandeep Kuntam June 11, 2019 20:09

  Thank you Harry, I also wanted to know if there was any way we could reference cells into this formula to make it dynamic. Like right now I can filter using the SQL= function like

  SQL=SELECT ACTNUMBR_1 + ACTNUMBR_2 AS "Numbers" FROM GL00100 WHERE ACTNUMBR_1 BETWEEN 10 AND 15 AND ACTNUMBR_2 BETWEEN 000 AND 020 

  instead of manually inputting those filters it would be awesome if i could reference the cells where in I could get stuff from Options page to show up. Any ideas on that?

• 0
  
  Harry Lewis June 11, 2019 20:33

  Certainly.

  Here is an example.

  I have set up my Options sheet so that I can enter values for ACTNUMBR_1 and ACTNUMBR_2:

  

   

  Following the process listed in the SQL= Documentation, I created this syntax:

  

• 0
  
  Sandeep Kuntam June 11, 2019 21:20

  Thank you Harry, But this does not work like the regular NL(Sheet) because the regular one does not have duplicates, but this will return all the lines that have the number. is there a way to get around it?

  

• 0
  
  Harry Lewis June 11, 2019 21:32

  Hi Sandeep.

  What is the "Activate" window?

  How can there be duplicates in the Account Master table (GL00100)?

  What are the values for  your Report Options?

  Edited by Harry Lewis June 11, 2019 21:43
• 0
  
  Harry Lewis June 11, 2019 21:35

  How can there be duplicates in the Account Master table (GL00100)?

  What are the values for  your Report Options?

  Edited by Harry Lewis June 11, 2019 21:36
• 0
  
  Sandeep Kuntam June 11, 2019 21:43

  Oh sorry that is just to show the different sheets. A right click on the arrows to move to sheets will bring that window up.. Even if I did rows I will get the below result.  why it is doing that is probably because it is acting like a normal SQL query and just returning all the lines which satisfy the requirements of the statement. 

  Here is a snap of the table 

  

  

   

  But traditionally you would see the NL function will eliminate all the duplicates like below. where the combination of  Actnumbr 1  and actnumbr 2 will not have any duplicates. 

   

  

• 0
  
  Harry Lewis June 11, 2019 21:49

  Thank you for the clarification.

  Try :

  SQL=SELECT DISTINCT ACTNUMBR_1+ACTNUMBR_2 AS "Numbers" FROM GL00100 WHERE %Filter1%

• 0
  
  Sandeep Kuntam June 11, 2019 22:12

  Thank you, so basically I can treat it like a normal SQL query and it should work!! 

• 0
  
  Sandeep Kuntam June 12, 2019 19:01

  Hello Harry,

  I am sorry I need your help figuring this out. 

  Following the below article

  https://support.jetglobal.com/hc/en-us/articles/219403107-SQL-Reference

  I tried to use the Order by clause. But for some reason I am unable to use it successfully. the reason I need to use ORDER BY is because for some reason it is generating sheets or rows in random order. 

  

  I want it to sort by ACTNUMBR_1 so its 10 XXX 10 XYZ and then 15 XXX 15 XYZ instead of it returning the result completely randomly. 

  But as soon as I try to use the ORDER BY %sort% clause after the WHERE clause I get a #VALUE! error. Can you please help me understand where I am going wrong? or how to get around the error?

  

• 0
  
  Harry Lewis June 12, 2019 21:29

  Hello Sandeep -

  Because the query is using DISTINCT and the query is not returning ACTNUMBR_1 (it is returning the concatenation of ACTNUMBR_1 and ACTNUMBR_2), you would only be able to sort by that concatenation.

  =NL("Sheets",$B$3,"Numbers","1S=GL00100.ACTNUMBR_1",Options!$C$2,"1S=GL00100.ACTNUMBR_2",Options!$C$3,"-1S=Numbers","*")

  Otherwise you would get an error such as "ORDER BY items must appear in the select list if SELECT DISTINCT is specified"

• 0
  
  Sandeep Kuntam June 13, 2019 18:51

  Thank you as always for your help Harry.