salesforce "orderitem", and other errors." /> salesforce "orderitem", and other errors." /> salesforce "orderitem", and other errors." />

Issues with shipstation "orderitem" -> salesforce "orderitem", and other errors.

  • 22Views
  • Last Post 2 weeks ago
  • Topic Is Solved
0
votes
Jake Myara posted this 2 weeks ago

Hi,

 

I am having trouble with a shipstation "orderitem" -> salesforce "orderitem" task. 

 

I have tried upsert, insert and update separately, but it appears there is currently no way to add orderitems from shipstation into salesforce. Is this correct?

 

The limitation appears to be that I can either access the "OrderId" information (the parent order that holds the orderitem) using relational mapping, or access the "lineItemKey" or "orderItemId" (which are the only pieces of unique info for the OrderItems, from the shipstation API:

 

orderItemId    -    number    -    The system generated identifier for the OrderItem. This is a read-only field.

lineItemKey    -    string    -    An identifier for the OrderItem in the originating system.

 

), but not both sets. 

 

When using shipstation orderitems -> salesforce orderitem, (there is no option to load related that adds any additional fields) I have access to all the orderitem fields but no way to establish the "orderid" field; I have tried to do target or source lookups but this is not possible, because the only field available in Orders which would possibly reference the orderlineitem is the Items fields, which according to the shipstation API:

 

items    -    OrderItem[]    -    Array of purchased items.

 

But any comparisons of the orderitem fields to the items field in skyvia fails; in the case of the two most likely things to compare, comparing lineItemKey results in an error of:

 

"No record found in object 'Orders' for lookup by column 'Items' = '65485750620738'."

 

And comparison of the array to orderItemId is strictly disallowed as it is "invalid type." I realize the "items" array contains OrderItems, not lineItemKeys or orderItemIds, but as there is no way to directly reference OrderItem objects (only their fields), I had hoped that this part might have been abstracted to check for an OrderItems object in the array which has the same field values. 

 

Using the "External ID" lookup option gives access to the external id of the Order object, but none of the fields available in the "Source Column" dropdown would correspond to the external ID, or even to the order at all. 

 

When using shipstation orders -> salesforce orders, loading related on salesforce orderitems, and mapping the "order.orderitem" object I have access to the "OrderId" via relation mapping, but only access to the columns from the shipstation "order" object, not the "orderitem" object, so I cannot fill out any fields. Since there are no fields in the "order" object I can use to find the "orderitem" object, I cannot do target or source lookups to fill these fields. But I suspect that this is correct behavior. This doesn't allow me to do anything useful, as even if I were to insert these OrderItems, using constants for the fields I must fill but cannot currently, I would be unable to later update them to correct values since none of the fields I could give them would have values which would be unique to that particular OrderItem. 

 

And so I cannot add any orderitems. I hope that I am incorrect and there is some way to do it, or that this functionality will be added soon. 

 

I have attached pictures demonstrating what I have stated above.

 

I am also having two other minor issues; the first is that I cannot get the source/marketplace from shipstation; I have tried both using "Orders.AdvancedOptions_Source" (which results in a null value) and "Shipments.AdvancedOptions_Source where

Shipments.OrderNumber = (Column = OrderNumber)" lookup, which either results in the error "No record found in object 'Shipments' for lookup by column 'OrderNumber' = '111-6129908-0369864'." or a null value. I am able to view this field in shipstation, but for some reason none of the field options available will transfer this value. 

 

The second is that when trying to use an expression to split up first and last names passed in a full name field, separated by a space character (" "), I get:

 

"ErrorDescription";"AccountId";"LastName";"FirstName";"MailingStreet";"MailingCity";"MailingState";"MailingPostalCode";"MailingCountry";"Phone";"Email";"LeadSource";"email_as_id__c";"Full_Name__c";"Marketplace__c";"Date_Created__c";"Date_Modified__c"

"An error occurred during computation of the expression.   Column name: 'FirstName'.";"";"";"";"";"";"MI";"49866-1572";"US";"9062269658";"x839tpny2qywk1l@marketplace.amazon.com";"";"x839tpny2qywk1l@marketplace.amazon.com";"Amy";"";"1/5/2019 7:10:08 PM";"1/5/2019 7:10:08 PM"

 

for values where there are no space characters (" ").

 

The expression: 

 

(TOKENCOUNT(" ", Name) > 0)? REPLACENULL(SUBSTRING(Name,1,FINDSTRING(Name," ",1)-1),"") : " "

 

and also tried

 

(TOKENCOUNT(" ", Name) > 0)? REPLACENULL(SUBSTRING(Name,1,FINDSTRING(Name," ",1)-1),"") : Name

 

What I am trying to accomplish is splitting up a space separated full name into first and last name, but to have it check first if there are spaces and if not to skip trying to split up the name. It seems to fail on thing that do not contain any spaces. Is it still evaluating the first expression in the if statement even though its not being run?

 

 

Thank you so much for your time spent looking into this. 

Attached Files

Order By: Standard | Newest | Votes
0
votes
Dmitriy Muzurov posted this 2 weeks ago

Hi Jake,

Thank you for submitting a ticket with us.

We answered to you by email. Please check your inbox.

Should you have any questions, do not hesitate to contact us back.

0
votes
Jake Myara posted this 2 weeks ago

Thank you guys for your response. In case someone else with the same issue finds this ticket, I figured I would post your emails here, as well as what I ended up doing to work around the current limitations of the beta status of the shipstation integration:

/////////////EMAILS

Hello,

Thank you for contacting our Skyvia Team.

The field Items is an array which data is presented in special object OrderItems. We tested it from our side and it seems there is no opportunity to map the appropriate OrderItem to a certain OrderId.

Regarding source/marketplace, we have created a ticket for our development team. Once we have any results, we will update you.

Regarding expressions, the fun—Ātion TOKENCOUNT returns zero only if the character_expression is null. Since the field Name is not null, the function takes the entire string as the first token and the result in your case will always be True for "> 0" condition.

So, all you need to change is to replace 0 with 1 in the first part of both expressions:
(TOKENCOUNT(Name," ") > 1)

Also, despite SSIS recognizes arguments for TOKENCOUNT even if they are not at their places, we would recommend swapping them, so that a character_expression argument is at first place and delimiter_string argument at second. Please check the official documentation here:
https://docs.microsoft.com/en-us/sql/integration-services/expressions/tokencount-ssis-expression?view=sql-server-2017

So, the expressions will look like this:
firstname:
(TOKENCOUNT(Name," ") > 1) ? REPLACENULL(SUBSTRING(Name,1,FINDSTRING(Name," ",1)-1),"") : Name

lastname:
(TOKENCOUNT(Name," ") > 1) ? REPLACENULL(SUBSTRING(Name,FINDSTRING(Name," ",1)+1,(LEN(Name)-FINDSTRING(Name," ",1)+2)),Name) : Name

Should you have any questions, do not hesitate to contact us back.


Best wishes,
Dmitriy
Skyvia Team

 

Hello,

We are from Skyvia Technical Team.

Our developers looked into the issue and tested the use case with Source columns.

A test order was created through API and a value "Webstore" passed to this field.

When doing SELECT, the value is visible. Also, ShipStation Orders were imported to Salesforce and the data was migrated successfully.

If you have a blank value after import, that means the column does not have any data. ShipStation UI can show the data while API sends other values.

You can test in Skyvia Query and call this field. Here is the guide for Query:
https://skyvia.com/resources/docs/how_to_query_data_via_sql.htm

Should you have any questions, do not hesitate to contact us back.


Best wishes,
Dmitriy
Skyvia Team

 

/////////////MY SOLUTION

Shipstation allows exporting of orders via CSV, including lineitems. Since each line contains all order information as well as all lineitem information, it was then trivial to either use Skyvia to import those lineitems into salesforce using skyvia, or what I ended up doing: putting the information into google sheets to automatically prepare it for importing via the salesforce Data Import Wizard into a custom object which from there uses an apex trigger to create the order item objects. The reason I did that instead of using Skyvia is because there was additional functionality I wanted to add that neither Skyvia nor the Data Import Wizard would allow. 

For most users, the Skyvia CSV -> Salesforce import would probably be best, as it allows more functionality than the Data Import Wizard without apex. 

As an added bonus, Shipstation correctly exports the marketplace/source when exporting via CSV instead of the APi. 

The only drawback to the above methods is that they are not fully automated and must be done manually; once set up, however, either method only involves a few clicks on each respective website to execute.

 

0
votes
0
votes
Dmitriy Muzurov posted this 2 weeks ago

Dear Jake,

Thank you for posting your solution!

We are looking forward to hearing from you, should you require any further assistance.

Close