Need some help with Celigo Integrator.io if anyone has experience. I've created a dataflow from our Snowflake DW to NetSuite to import sales transactions as a Cash Sale. Got everything running fine, only problem I'm having is that because I'm querying data at the line-item level, I need to tell Celigo to group the import by Order ID so the cash sale has multiple lines on it and doesn't try to create multiple cash sales for the same transaction. I see that with Data Loader (CSV --> NetSuite) I have the ability to Group by Field Value, but I don't have that same option with the DataFlow out of Snowflake? Almost like I need to first export my data into CSV form, then upload the CSV into NetSuite just to have this ability? What am I missing here?

i'm not familiar with Snowflake, but like a csv import, if you map a field that all lines have in common to the Cash Sale # in netsuite, then you should get one cash sale with multiple items. It's all about the mapping.

Hi Andrew. I passed this to the team, and they suggested this: 1. Some sql databases like Snowflake can have variant fields where the field houses JSON data. In the snowflake query as the source, you can actually group fields into a json array. IO would then only get the header group with the array already presented to it. https://docs.snowflake.com/en/sql-reference/functions/array_agg.html 2. Alternatively, I would suggest they have the source as exporting only the header data, then create a lookup to lookup the line data.

@AK47 because I'm using a Cash Sale transaction, NetSuite doesn't allow me to manually enter a transaction # like you can with a sales order, it auto-assigns one, so I don't have a way of telling Celigo to group by the Order ID since I have nowhere to map it in this scenario

@Andrew Cohen the best way to handle this would be Rico's first suggestion. You would query snowflake with something like this: select t.recordtype ,arrayagg(object_construct('tranid',t.tranid,'lastmodifieddate',t.lastmodifieddate)) within group (order by t.tranid desc) as data from celigo_labs.netsuite_suiteql.transaction as t where t.recordtype is not null group by t.recordtype ;

@Tyler Lamparter thanks for that, I've been messing around with the array_agg and object_construct functions to try and get what I need, but having some issues getting the desired JSON format output. I'll try the array_agg(object_construct()) and see if that works

@Andrew Cohen sounds good. With object_construct, you are forming your JSON for that individual line, then array_agg is the aggregate function to group all the individual objects together into an array. Let me know if you need any other help.

ahhh that makes sense, thanks for the explanation! Side note, any idea why doesn't celigo allow the "Group by Field" function in the native dataflow setup and is only available for CSV uploads? Feels like a simple solution to modifying the query for the desired output

Good question - this same use case came up a month or so ago from another customer and I had it added to the enhancement roadmap for all database connectors/http connectors. It's currently slated for our Q4 2022/Q1 2023 release. The background process to handle this is a bit more resource intensive than other use cases because Celigo has to pull all the data from the data warehouse, put it into memory, group the records, then page the data.

@Tyler Lamparter sounds great! So I got the query to work and spit out the results I need, but maybe I have a step wrong here. I tried to export my query results into JSON format, as well as XLSX format, then previewed the data in integrator.io, and the formatting contains a bunch of page breaks and quotes. Do I just need to run the query directly from the dataflow instead of trying to test with a JSON import?

Do you mind sharing your sql query? Do you have line breaks in your object_construct?

Here's the select section

select 
"Order ID"
,array_agg(object_construct(
'paymentmethod', "Payment Method"
,'revchannel' ,"Revenue Channel"
,'campaignid' ,"Campaign ID"
,'dateclosed',"Date Closed"
,'datecompleted',"Date Completed"
,'datecaptured',"Date Captured"
,'shiptocountry',"Ship To Country"
,'shipfromcountry',"Ship From Country"
,'ffregion',"Fulfillment Region"
,'currency',"Currency"
,'amt',"Amount"
,'promo',"Promo Code"
,'qty',"Quantity"
,'location',"Location"
,'customer',"Customer"
,'taxcode',"Tax Code"
,'acct',"Account"
,'product',"Product Name"
,'pod',"POD Segment"
,'state',"Ship To State"
,'project',"Project Number")) as Data


from(---huge query----)

try putting the whole function there on one line. It looks like Snowflake is adding line breaks into the construction since you have it built on different lines in the query

I'm running this query from a SQL IDE if that makes any difference

Also try removing all spaces from the construct query as well

@Tyler Lamparter thanks for all the help, was able to get this successfully working. I do have one more question - I was able to create a secondary flow here to create cash refund entries for refunds (anything where the transaction total is <$0), but the way I have it setup it creates a standalone cash refund in NetSuite. Is there a way where I can initiate a refund on the original transaction rather than creating a whole separate transaction? They would share the same "Campaign ID#" and "Customer Order #" as the original order (shown below). Hope that makes sense.

Hi Andrew - I assume you want to make the refund from the cash sale. You would add a couple things in your cash refund mapping like this. Here is also a good article discussing how to do it for work orders, but I just tested this out for cash sale -> cash refund and it worked as well. https://docs.celigo.com/hc/en-us/community/posts/4413033089435-Assembly-Build-for-Work-Order