0

Compare two fields from same table and only display one SO

Jason Rossmeisl

June 25, 2018 19:00

Answered

Follow

 Hi,

 

VERY new to Jet Reports (and I'm finding it's more difficult than learning a foreign language).  First off, I don't understand the difference between NF, NL, NP and GL, or what they stand for.  After trying to do different web searches, I'm ending up more confused than before.

Also, my main challenge-  I'm trying to take and compare data from a Sales Order Archive table (No. of Archived Versions v.s. Version no.)  and generate a table that only uses the last S.O. archive.  (only display the SO# where the No. of Archived Versions=Version no.)  Is there a way to do this?  If so, how? 

Thanks for any assistance!

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Harry Lewis June 25, 2018 19:16

  Hi Jason -

  Sorry to hear that you are experiencing challenges. As with anything new, proper training is invaluable. For our part, let's at least get you a bit of introduction, so as to remove some of the mystery.

  First, the online Knowledgebase is your friend.  While there is definitely a great deal of information, it can probably bit overwhelming.

  I would definitely encourage you to start with the Your First Reports article.  It explains the main tools that are available and provides links to more information about them.  Included in there is a link to the Grouping and Subtotaling Tutorial - which can be a great guide to creating reports, in general.

  Next, let's look at the functions:

  The NL function retrieves data from your database.  It includes parameters that allow you to filter your data so that you get just that data you need.

  The GL function specifically retrieves General Ledger data (account balances, budgets, debits, credits, net change).  Again, specialized filters are used to get exactly what you need.

  The NP function provides you with multiple tools that come in handy for performing various tasks.  These are often done in conjunction with the NL function.  Using the NP, you can perform the Union of sets.  You can take an array and Join it in to a value-separated list of values.  And more.

  Finally, the NF function.  The NL function can be used either to return a list of field values... OR it can return a pointer to an entire record within your database.  When used in this way, the NF function is used to retrieve individual fields from that pointer.

  Let's pause here.  I'll respond, again, after reviewing the example you mention and see if I can point you in the right direction.

   

  Edited by Harry Lewis June 25, 2018 20:00
• 0
  
  Harry Lewis June 25, 2018 20:41

  Hello Jason -

  While my sample database does not have a Sales Order Archive table, I'm somewhat familiar with the Sales Header Archive table, so I'll use that for my example.

  That tables includes a No. field and a Version No. field

  So, for each No., what I want is a list of only those records (I'll use the pointer technique I referred to, earlier) where the Version No. field is the last version number for that No.

  This will combine a couple of the functions you asked about, so it's a good example.

  The first concept is to get records from the table:

  =NL("Rows","Sales Header Archive")

  Notice that I did not tell what FIELD to return from that table; this is what makes it return a pointer.

  Let's suppose that function is in cell D5 and that I use the NF() function to put the No. field in cell E5:

  =NF(D5,"No.")

  (remember, the NF function retrieves a specific field from the record pointer)

  Next, I want a function the returns the last Version No. for whatever No. field is returned by that first NL(Rows) function (let's assume I put the No. field in cell E5):

  =NL("Last","Sales Header Archive","Version No.","No.",E5,"+Version No.","*")

  What this says is "return the last Version No. where the No. field matches what is in cell E5, and be sure to sort the records based on the Version No. field (anytime you want the First or Last of something, if you don't specify a sort order, you get whatever is the default defined within the database).

  So far this is fairly straight forward.  

  What we need to do next, is to use that NL(Last) function as a filter in our original NL(Rows) function [so that only the last record is returned for each No.].

  This has two issues:

  (1) our NL(Last) function references cell E5 [which is based on the function in cell D5 - into which we are going to place our NL(Last) function].  This results in a circular reference that Excel is not going to like.  Don't worry, we'll change that.

  (2) Filters within an NL() function need to be with double quote marks.  This gets challenging when the text we want to put within those quote marks ALREADY HAS quote marks.  

  To address issue #1, we use the NF function again - this time in the NL(Last) function.

  To address issue #2, we double all the quotation marks in that NL(Last) function

  Here what it will end up looking like:

  =NL("Rows","Sales Header Archive",,"Version No.","=NL(""Last"",""Sales Header Archive"",""Version No."",""No."",NF(,""No.""),""+Version No."",""*"")")

  We replaced the reference to cell E5 with an NF function that looks at the current NL function (notice that the NF does *not* include a cell reference... this makes it examine the current function into which it is embedded).

  Then, we doubled all of the existing quotation marks.

  So, what that function states is "get all the Sales Header Archive records where the Version No. field is equal to the last Version No. value for any record in the table which has the same No. value as the record currently being examined".

  I hope that helps.

  As I mentioned earlier, training is important... but not every organization takes that route. 

  I encourage you to use the online Knowledgebase.  Start with that "First Reports" article and start exploring.

  Edited by Harry Lewis June 25, 2018 20:44
• 0
  
  Jason Rossmeisl June 26, 2018 11:45

  Hi Harry,

  Thank you for the support!  Right after I posted, I was able to find a demo with a compare function.  I created that table in a blank sheet and just copied the syntax over to my table, modifying to use my fields, and I THINK it's working.

  I am definitely going to be using the demos that you recommended and hopefully I will be able to get a better grasp on the syntax and report types.

  Thanks again for the quick reply.