CSV file seperate rows for Ref_Rows

[SKETCHwade]

Hello,

My intention with this CSV file is to import it into accounting software Xero for automated invoicing.

Xero supply a Template of their own that the CSV file has to follow. It allows for all details of the invoice in a single CSV line. The issue being that this only allows for one line in the invoice eg one item can be invoiced per invoice.

In order to add more lines to an invoice you must also include lines with the same Invoice number in your CSV.

The app has a table for Job Details and a seperate table for Parts. This is because there can be many parts to one job. They are linked with REF_ROWS(). This works great in the app but makes this task much more difficult.

Creating the start expression for the job details was easy but how on earth I am going to create extra rows based on the ref rows I have no idea.

I am thinking that because Xero links them based on Invoice number and not on order displayed I could run the Job details seperate to the Parts but I still have to link them somehow.

Any ideas are much appreciated!

Example Spreadsheet https://docs.google.com/spreadsheets/d/1aSg3IbL1S32ARuCH01F1tTKoSXreHLZ-qjNK0ufs1dY/edit?usp=sharing

[WillowMobileSys]

The REF_ROWS based column will be named something like "Related Parts".  If you are accessing the Job Details table rows then in the START expression you can reference the "Related Parts" column like so:

...

<<START: SELECT(Job Details[Job Detail ID], [Job] = [Job ID])>>
     <<START: SELECT([Related Parts])>>
              ... write out Parts rows data for the Job Detail ...
     <<END>>
<<END>>

The template will know based on context that [Related Parts] is a column from the Job Details row. 

Note the use of "[Job] = [Job ID]",  in a template when using columns for a join there is no [_THISROW] capability that would help with ambiguity of the columns were named the same.  So if you need to use these columns for joining to select rows, the columns must be different names.  The template will figure out from which table based on context and the different names.

[SKETCHwade]

Thank you for your response!

I tried this:

"*ContactName","EmailAddress","POAddressLine1","POAddressLine2","POAddressLine3","POAddressLine4","POCity","PORegion","POPostalCode","POCountry","*InvoiceNumber","Reference","*InvoiceDate","*DueDate","InventoryItemCode","*Description","*Quantity","*UnitAmount","Discount","*AccountCode","*TaxType","TrackingName1","TrackingOption1","TrackingName2","TrackingOption2","Currency","BrandingTheme"

<<Start: SELECT(Jobs[Job ID],[Time Completed] > Today()-50000,TRUE)>>

"<<[Customer]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<Concatenate("INV_A",[_ROWNUMBER])>>","<<>>","<<>>","<<>>","<<>>","<<[Description]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>"

<<Start:[Related Parts]>>

"<<>>","<<>>","<<>>","<<>>","<<>>", "<<>>","<<>>","<<>>","<<>>","<<>>","<<Concatenate("INV_A",ANY(SELECT(Jobs[_RowNumber],[Job ID]=[Job ID])))>>","<<>>","<<>>","<<>>","<<[Product Code]>>","<<[Product Description]>>","<<[Quantity]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>"

<<END>>

<<END>>

 Appsheet is telling me this is empty. There is definately data that matches the requirements. Am I missing something simple?

[WillowMobileSys]

Do you have a [Related Parts] column on your Jobs table?  In your original post you described a REF_ROWS relationship between "Job Details" and "Parts".  From this I would expect a Job Details table with a [Related Parts] column.

[WillowMobileSys]

Just to be crystal clear, I missed a "..." to imply more to the template.  I was trying to suggest this structure:

<<Start: SELECT(Jobs[Job ID],[Time Completed] > Today()-7,TRUE)>> 
...
     <<START: SELECT(Job Details[Job Detail ID], [Job] = [Job ID])>>
          ...
          <<START: SELECT([Related Parts])>>
              ... write out Parts rows data for the Job Detail ...
          <<END>>
          ...
     <<END>>
     ...
<<END>>

[SKETCHwade]

The job details I was referring to are within the Jobs table in columns such as [Customer] and [Address], Sorry for my confusing explanation.

I should not need your second select correct?

[SKETCHwade]

Yes the app does use:

[Related Parts]

[SKETCHwade]

This is where I am at now with it:

"*ContactName","EmailAddress","POAddressLine1","POAddressLine2","POAddressLine3","POAddressLine4","POCity","PORegion","POPostalCode","POCountry","*InvoiceNumber","Reference","*InvoiceDate","*DueDate","InventoryItemCode","*Description","*Quantity","*UnitAmount","Discount","*AccountCode","*TaxType","TrackingName1","TrackingOption1","TrackingName2","TrackingOption2","Currency","BrandingTheme"

<<Start: SELECT(Jobs[Job ID],Date([Time Completed]) > NOW()-90000,FALSE)>>

"<<[Customer]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<Concatenate("INV_A",[_ROWNUMBER])>>","<<>>","<<>>","<<>>","<<>>","<<[Description]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>"

<<Start: [Related Parts]>>

"<<>>","<<>>","<<>>","<<>>","<<>>", "<<>>","<<>>","<<>>","<<>>","<<>>","<<Concatenate("INV_A",ANY(SELECT(Jobs[_RowNumber],[Job ID]=[Job ID])))>>","<<>>","<<>>","<<>>","<<[Product Code]>>","<<[Product Description]>>","<<[Quantity]>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>","<<>>"

<<END>>

<<END>>

While making changes to it appsheet will tell me if there was error in the expression. But with this it tells me the data is empty?

Surely I am nearly there just missing something.

From my understanding my first row here is a header so there should always be some data to create the CSV?

[WillowMobileSys]

Ok got it, [Related Parts] is a column on the Jobs table. 

Can you show an image of the error you are receiving?

[SKETCHwade]

Yes exactly. Here's a screenshot:

[WillowMobileSys]

I am thinking that the error does NOT refer to the expressions in the template.  It is perfectly valid for expressions in the template to not return rows - they simply just don't "print" anything to the end document.

How is your Event configured in the Bot?  

I would try this:  temporarily remove the expressions in the template and only have the column headers remaining.  You should at least get a generated document with those headers.  Work on the process until you get at least that far.  Then introduce each START expression individually to make sure they are performing as expected.

[SKETCHwade]

That's a great point!

Thank you I'll look into it!

[SKETCHwade]

Thank you @WillowMobileSys 

Turns out the issue was simple and right in front of me the whole time.

Solution:

"*ContactName","EmailAddress","POAddressLine1","POAddressLine2","POAddressLine3","POAddressLine4","POCity","PORegion","POPostalCode","POCountry","*InvoiceNumber","Reference","*InvoiceDate","*DueDate","InventoryItemCode","*Description","*Quantity","*UnitAmount","Discount","*AccountCode","*TaxType","TrackingName1","TrackingOption1","TrackingName2","TrackingOption2","Currency","BrandingTheme"
<<Start: SELECT(Jobs[Job ID],Date([Time Completed]) > NOW()-90000,FALSE)>>
"<<[Customer]>>","","","","","","","","","","<<Concatenate("INV_A",[_ROWNUMBER])>>","","","","","<<[Description]>>","","","","","","","","","","",""
<<Start: [Related Parts]>>
"","","","","","","","","","","<<Concatenate("INV_A",ANY(SELECT(Jobs[_RowNumber],[Job ID]=[Job ID])))>>","","","","<<[Product Code]>>","<<[Product Description]>>","<<[Quantity]>>","","","","","","","","","",""
<<END>>
<<END>>

 I was using "<<>>", where I wanted blank cells instead of "",

That means I was saying dont run any code while running code. That does'nt make sense haha

[SKETCHwade · Accepted solution]

For reference If anyone else is learning about CSV exports. My solution .txt document looks like this:

"*ContactName","EmailAddress","POAddressLine1","POAddressLine2","POAddressLine3","POAddressLine4","POCity","PORegion","POPostalCode","POCountry","*InvoiceNumber","Reference","*InvoiceDate","*DueDate","InventoryItemCode","*Description","*Quantity","*UnitAmount","Discount","*AccountCode","*TaxType","TrackingName1","TrackingOption1","TrackingName2","TrackingOption2","Currency","BrandingTheme"
<<Start: SELECT(Jobs[Job ID],Date([Time Completed]) > NOW()-8,FALSE)>>
"<<[Customer]>>","","","","","","","","","","<<Concatenate("INV_A",LEFT([Job ID], (FIND("_", [Job ID])+1)))>>","","","","","<<[Description]>>","","","","","","","","","","",""
<<Start: [Related Parts]>>
"","","","","","","","","","","<<Concatenate("INV_A",LEFT([Job ID], (FIND("_", [Job ID])+1)))>>","","","","<<[Product Code]>>","<<[Product Description]>>","<<[Quantity]>>","","","","","","","","","",""
<<END>>
<<END>>

Could not have done it without your help @WillowMobileSys 

Thank you for your time!