CSV Template - display fields if this is the first record

[RedVox]

I'm trying to make a csv file that matches Quickbooks invoice template.  The template calls for a csv where each row is basically the child invoice_items record.  But on the first row related to an invoice, (and only the first row), details from the parent invoice are included.  So, how can I make an if statement that will check that the current invoice_item is the first one in the list of Related Invoice_items? 

[Marc_Dillon]

---

@RedVox wrote:

how can I make an if statement that will check that the current invoice_item is the first one in the list of Related Invoice_items

---

Not sure why you need to do that. Can't you just make your CSV template something like this:

col1_header,col2_header,.....
<<[invoice_col1]>>,<<[invoice_col2]>>,....
<<START:[related....]>><<[related_col1]>>,<<[related_col2]>>,....
<<END>>

[RedVox]

Nope.  Without the IF block, it would make all rows the same and that's definitely not what I need to do. (I dunno why QB can't just ignore the redundant data, but it's quite persnickety about it.)  After further trial and error, I've arrived at this template script:

InvoiceNo,Customer,InvoiceDate,DueDate,Terms,Location,Memo,Item(Product/Service),ItemDescription,ItemQuantity,ItemRate,ItemAmount,ItemTaxAmount
<<Start: ORDERBY(SELECT(Invoice_items[id], [related_invoice].[Include In Export] = "Yes"),[Related_Invoice].[InvoiceNo], FALSE)>><<[Related_Invoice].[InvoiceNo]>>,<<IF: ([id] = [related_invoice].[First Invoice Item])>><<Concatenate([Related_Invoice].[Member].[Name], "- ", [Related_Invoice].[Member].[member_number]>>,<<[Related_Invoice].[Create_Date]>>,<<[Related_Invoice].[Due_Date]>>,,,,<<EndIf>><<IF: ([id] <> [related_invoice].[First Invoice Item])>>,,,,,,<<EndIf>><<[Description]>>,<<[Formula]>>,<<[Quantity]>>,<<[Unit_Price]>>,<<Concatenate([Amount])>>,<<Concatenate([Amount]*0.13)>>
<<End>>

It seems to be working.  The place I was hung up last night turned out to be a problem with using [_THISROW] in the IF condition.  For some reason, during every iteration, my [id] value was always 1 whenever I included [_THISROW].  

[Marc_Dillon]

Oh the parent details go in their own separate columns adjacent to the first child detail records. I didn't understand that from your OP, I thought it'd be its own separate row. Yes, the way you did it is ok.

---

@RedVox wrote:

For some reason, during every iteration, my [id] value was always 1 whenever I included [_THISROW].  

---

If you post your old attempt I could try and see what the issue there was.

[RedVox]

It's lost to the mists of time, I'm afraid.  According to the trash in my inbox, I've tested 40 different versions of the template.  It's all a blur now.  But the if block used to read:

<<IF: ([_THISROW].[id] = [related_invoice].[First Invoice Item])>> and it wasn't until I put <<[_THISROW].[id]>> to output in advance of the if block that I realized that [_THISROW].[id] was constantly outputting "1".  Getting rid of the [_THISROW] fixed that issue.  

[RedVox]

One thing I don't like about where I landed is that it requires a virtual field.  I was trying to figure out a way so that the template itself could determine whether the current child record was the first child record.  I never did crack that nut.

[Marc_Dillon]

Yah that [_THISROW] is not required there, because you're already in the context of the individual records of the start expression. So it was actually referring back to the original record that the bot was triggered on.

To avoid the VC, take that whole ORDERBY expression that you have, wrap it in ANY(), then compare that to the [id].

<<IF: [id] = ANY(ORDERBY(SELECT(Invoice_items[id], [related_invoice].[Include In Export] = "Yes"),[Related_Invoice].[InvoiceNo], FALSE)) >>