Open Forum

Expand all | Collapse all

Jet Report

  • 1.  Jet Report

    Posted May 11, 2021 04:15 AM

    I have just been given a designer licence for Jet and I am trying to create a report to use for customs clearance of goods. I have managed to pull the information I need but one of the fields creates multiple rows whereas I need the data all in one cell.
    Can anyone assist?
    I do not know the number of rows the NL function is going to create as the values can be different each time.

    Jon Kedge

  • 2.  RE: Jet Report

    Posted May 11, 2021 10:57 AM

    Hi Jon,

    The best way to do this will be to set the line of your item details row to be a separate repeater with NL("Rows=2"...) and then a second repeater on the subrow for your extended text table. What happens is the sheet will expand out based on all the item lines, with the repeater NL("Rows", "Extended Text") embedded between each item line. The Extended text repeater then gathers its data adding in however many lines it finds between the item lines.
    How this will look in use:

    Line 1 | NL("Rows=2","Item",,Filters)  | NF(Cell with the key, "Item Number") | NF(Cell with the key, "Description") | NF(Cell with the key, "Field Name") etc
    Line 2_____________________NL("Rows","Extended Text","Text", filters including Item No = Cell with the previous key)

    Best practice is to not return a specific field in the NL function when more than one field will be pulled from it, to do this simply leave the Field portion of the call blank.

    Insight Software offers developer training sessions that may be helpful in getting the experience to build out your reports, if that is something that interests you I suggest talking to your NAV/BC partner to discuss scheduling.

    Lydia Deese
    Seattle WA

  • 3.  RE: Jet Report

    Posted May 11, 2021 07:32 PM

    One follow-up as I may have missed a key part of your request.

    You cannot directly return multiple rows into a single cell that I am aware of. However, you could return the rows and then use an excel function to convert them all to a single field. The function to do that would be similar to the above layout with two minor changes. Rows=3 as you will want a blank row to output at the end, and then on the first line, you can use the excel function =concatenate(R2:R3) it will then grab all the fields of row 2 to the blank row when it expands them out and will provide the result in that field. To make your output cleaner you would then want to use the Hide functionality on rows 2 and 3 so they are not visible in the output.

    Row 1 | NL("Rows=3","Item",,Filters)  | NF(Cell with the key, "Item Number") | =Concatenate(ColumnRow2:ColumnRow3)

    Row 2________NL("Rows","Extended Text","Text", filters including Item No = Cell with the previous key)
    Row 3 Blank

    There are a few other ways to handle the request depending on the report requirements.

    Lydia Deese
    Seattle WA

  • 4.  RE: Jet Report

    Posted May 12, 2021 08:08 AM
    Hi Lydia

    Many thanks for your response.

    I have done as suggested (or at least I think I have)
    When I try =concatenate (ColumnRow2:ColumnRow3) I get #value error.
    If I put in each of the ColumnRows followed by , it misses out the information pulling through on the report rows aside from ColumnRow2.

    I am using Excel 2013.

    Jon Kedge

  • 5.  RE: Jet Report

    Posted May 12, 2021 09:44 AM
    ​Hi Jon,

    I was able to get this to work using the TEXTJOIN formula instead of CONCATENATE for a range of cells.

    The formula would be =TEXTJOIN(,TRUE,ColumnRow2:ColumnRow3)
    • The first value sets the delimiter - since the values are different rows I've left it blank
    • The next value determines is blank cells are ignored - if true, then blanks are ignored
    • Lastly you set the range of cells to combine
    I tried this using an on-prem BC 16 database and here are the results.  Let us know if you have more questions!

    John Hawkins
    Application Consultant
    Clients First Business Solutions

If you've found this thread useful, dive deeper into User Group community content by role