robin
Former Employee
Joined 9 years ago
User Profile
User Widgets
Contributions
Re: Setting up NetSuite OAuth 1.0 authentication
Netsuite + OAuth 2.0 @johnnys I created an Integration in Netsuite (Setup > Integration > Manage Integrations), enabled REST Web Services and Auth Code Grant under OAuth section and entered in the SnapLogic REST Callback URL ( https://elastic.snaplogic.com/api/1/rest/admin/oauth2callback/rest ). I copied the generated key and secret also - creation is the only time these are displayed: You must also enable REST Web Services and OAuth 2.0 as Features (Setup > Company > Enable Features) In the REST POST Snap, create an OAuth 2.0 account, enter in the key and secret you copied earlier as Client ID and Client Secret. I enabled Header Authentication. For “OAuth2 Endpoint” I used https://<ACCOUNT_ID>.app.netsuite.com/app/login/oauth2/authorize.nl as per the Netsuite documentation. For “OAuth2 Token” I used https://<ACCOUNT_ID>.suitetalk.api.netsuite.com/services/rest/auth/oauth2/v1/token Grant Type is authorization_code and the Auth Endpoing config table had a single entry scope whose value should match the Scope section from earlier (in my case that was restlets,rest_webservices,suite_analytics ). Authorizing the account was successful and returned an access token and expiration (consider enabling Auto-refresh token also) Then in the REST POST configuration, I tried to replicate your use case (SuiteQL): Service URL = https://<my-account-id>.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql HTTP Entity = {"q":"SELECT 'Hello world!' AS Greeting FROM DUAL"} (taken from a SuiteQL tutorial) Lastly, the SuiteQL API requires a request HTTP header Prefer . I used a value of transient which I was on the tutorial above: After configuring the Snap to Validate & Execute, a validation run successfully returned the JSON response from the Netsuite SuiteQL API:1.5KViews3likes0CommentsRe: Multi child xml to csv help required
Is there a way to just flatten the KSSK elements and ignore the V_Fleet ones ? You can delete the V_FLEET entry in the JSON Splitter’s “Include Paths” setting and then update the rest of the pipeline where those fields were being referenced.585Views0likes0CommentsRe: CSV to XML with repetitive xml nodes
Just a small change in your Mapper needed: That will output JSON like: [{ "Invoice": { "@xmlns:cac": "urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2", "@xmlns:cbc": "urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2", "@xmlns": "urn:oasis:names:specification:ubl:schema:xsd:Invoice-2", "cbc:CustomizationID": "urn:cen.eu:en16931:2017#conformant#urn:fdc:peppol.eu:2017:poacc:billing:international:aunz:3.0", "cbc:ProfileID": "urn:fdc:peppol.eu:2017:poacc:billing:01:1.0", "cbc:ID": "PRP100418", "cbc:IssueDate": "29/04/2022", "cbc:DueDate": "6/05/2022", "cbc:InvoiceTypeCode": "380", "cbc:Note": "110535 - DAF - XRef607 ICT Services - CRM Developer", "cbc:DocumentCurrencyCode": "AUD", "InvoiceLines": [{ "LineNumber": "1", "Tax": "700" }, { "LineNumber": "2", "Tax": "700" }], "ABNValue": "ABN 31 100 103 268" } }] which the XML Formatter will turn into: <?xml version="1.0" encoding="UTF-8"?> <Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"> <cbc:CustomizationID>urn:cen.eu:en16931:2017#conformant#urn:fdc:peppol.eu:2017:poacc:billing:international:aunz:3.0</cbc:CustomizationID> <cbc:ProfileID>urn:fdc:peppol.eu:2017:poacc:billing:01:1.0</cbc:ProfileID> <cbc:ID>PRP100418</cbc:ID> <cbc:IssueDate>29/04/2022</cbc:IssueDate> <cbc:DueDate>6/05/2022</cbc:DueDate> <cbc:InvoiceTypeCode>380</cbc:InvoiceTypeCode> <cbc:Note>110535 - DAF - XRef607 ICT Services - CRM Developer</cbc:Note> <cbc:DocumentCurrencyCode>AUD</cbc:DocumentCurrencyCode> <InvoiceLines> <LineNumber>1</LineNumber> <Tax>700</Tax> </InvoiceLines> <InvoiceLines> <LineNumber>2</LineNumber> <Tax>700</Tax> </InvoiceLines> <ABNValue>ABN 31 100 103 268</ABNValue> </Invoice> which is what I believe you wanted.11KViews1like1CommentRe: Multi child xml to csv help required
Got it. Here’s the solution - definitely some advanced Expression Language usage is required but I did learn quite a bit doing it: 12465_xml-to-flatten-csv_2022_05_20.slp (13.6 KB) Since you want to keep it to two rows, we drop the second JSON Splitter from my first solution above - and I’ve renamed the first Splitter to Split on KSSK : So we have data that looks like this now: Since you want CHARACT values like PM_ARB_A_AC_MAN to become keys, we have to do an Unpivot (i.e. making { "key": "someKey", "value": "someValue" } become { "someKey": "someValue" } ) I used the following expression to do that (note: yes, this should really be a new Snap): $AUSP.API_VAL_R.map((elem) => { [elem.CHARACT]: elem.VALUE } ) mapped to a temporary field $unpivoted . I also took the opportunity to flatten the structure of the other fields in the JSON in preparation for CSV: What’s important here is that I moved them under a placeholder $root object. That is because I’ll want to merge contents of the $unpivoted array into $root whilst also dropping the array itself. It’s easier to do it this way rather than trying to modify the root $ document and also remove the array at the same time. The last tricky step is the merge: Again I use the Expression Language - this time the object.extend(target:data) Object function: $root.extend(...$unpivoted) The triple-dot is the Spread Operator/Syntax: Spread syntax ( ... ) allows an iterable such as an array expression or string to be expanded in places where zero or more arguments (for function calls) or elements (for array literals) are expected, or an object expression to be expanded in places where zero or more key-value pairs (for object literals) are expected. In other words, I can pass an array of objects of key/value pairs, and extend() will combine the key/value pairs into a single object. And since I am extending the $root objects, those k/v pairs will be combined with the others! Binding the result of the expression to $ means I’ll have two flat objects ready to be used for CSV. Finally, you need to manually specify the CSV Formatter’s Headers to use, since the documents have different fields (the default is to use the headers from the first document). Enabling the “Use Header” checkbox and listing the fields from the input document: should output your desired CSV content: "AEDAT","EQASP","EQUNR","ERDAT","ERNAM","FLEET_NUM","CLINT","KLART","MAFID","PM_ARB_A_BSTNR","PM_ARB_A_BSTPO","PM_ARB_A_AC_MAN","PM_ARB_A_ADVERT_FRAMES","PM_ARB_A_CCTV","PM_ARB_A_CURRENT_REG_DT","PM_ARB_A_EMISSIONS","PM_ARB_A_ENG_EURO_NORM","PM_ARB_A_GPS","PM_ARB_A_MAN_DT" "2021-10-01","E","000000000010014880","2017-09-18","DB21016","SKBMI823EJ","0000027802","002","O","10934902","140",",",",",",","," "2021-10-01","E","000000000010014880","2017-09-18","DB21016","SKBMI823EJ","0000027803","002","O",",","WEBASTO ATHEMIA","No","No","26.01.2017","16.08.2019","EURO VI","No","26.01.2017"2.8KViews1like0CommentsRe: CSV to XML with repetitive xml nodes
deepak.shaw: Can you please help me know to introduce the namespaces into xml You’ll use our JSON-to-XML conventions, which are basically: { "root": { "elementName": { "@attributeName": "attributeValue", "$": "elementValue" }, "repeatedElement": [ { "key": { "@attr": "attr1", "$": "key1" }, "value": "value1" }, { "key": { "@attr": "attr2", "$": "key2" }, "value": "value2" } ] } } That input Document into the XML Formatter will generate the following XML: <?xml version="1.0" encoding="UTF-8"?> <root> <elementName attributeName="attributeValue">elementValue</elementName> <repeatedElement> <key attr="attr1">key1</key> <value>value1</value> </repeatedElement> <repeatedElement> <key attr="attr2">key2</key> <value>value2</value> </repeatedElement> </root> So, for the sample I provided above, the following should give you enough information to get to your final desired XML: [ { "ProductCode": { "@xmlns:cac": "urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2", "@xmlns:cbc": "urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2", "@xmlns": "urn:oasis:names:specification:ubl:schema:xsd:Invoice-2", "cbc:ProductName": { "cac:CostLocation": [ { "cbc:country": "US", "cbc:Cost": "$400" }, { "cbc:country": "AU", "cbc:Cost": "$500" }, { "cbc:country": "EU", "cbc:Cost": "$550" } ] } } } ] which results in: <?xml version="1.0" encoding="UTF-8"?> <ProductCode xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2"> <cbc:ProductName> <cac:CostLocation> <cbc:country>US</cbc:country> <cbc:Cost>$400</cbc:Cost> </cac:CostLocation> <cac:CostLocation> <cbc:country>AU</cbc:country> <cbc:Cost>$500</cbc:Cost> </cac:CostLocation> <cac:CostLocation> <cbc:country>EU</cbc:country> <cbc:Cost>$550</cbc:Cost> </cac:CostLocation> </cbc:ProductName> </ProductCode> Go from there to finish the rest of the XML you want.1.9KViews1like6Comments