Error with orders that have no address

[aeu]

11/29/2021 04:47 : An error occurred attempting to update orders: Error in XML. Reason: The 'Country' element is invalid - The value '' is invalid according to its datatype 'StringExactly2' - The actual length is less than the MinLength value.

We have orders in our system which don't have addresses but they are being sent to ShipStation which is rejecting them. The problem is that from that point onward, ShipStation stops processing and no subsequent orders are being sent.

We need a way to mark orders to not be sent to ShipStation (ref: https://github.com/FosterCommerce/shipstation-connect/discussions/38)

[sjcallender]

Hi @aeu - See the detailed instructions here on how you can do this by creating more than one "store" endpoint. https://github.com/FosterCommerce/shipstation-connect/issues/19#issuecomment-725418386

[aeu]

Hi @sjcallender - that looks like its exactly what we need. I'll give it a shot and hopefully you'll never hear from me again. 😄

[aeu]

Hello - I did these steps and now am getting 401 for user authorization (this was not happening before). Is there a way to test this (via curl for example) so I can isolate the problem to my end vs. the shipstation interface?

[sjcallender]

For curl, can you try following this other comment? https://github.com/FosterCommerce/shipstation-connect/issues/36#issuecomment-969914190

[aeu]

Hi, curl worked great.

This is what I am have in my admin:

My curl:

curl \ --request GET 'https://not.the.real.site.com/actions/shipstationconnect/orders/process?store=standard&action=export' \ --header 'Authorization: Basic not the real info='

And the response: {"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_storeType' in 'where clause'\nThe SQL being executed was: SELECT COUNT(*)\nFROM (SELECTelements.idASelementsId,elements_sites.idASelementsSitesId,content.idAScontentId\nFROMelements`elements\nINNER JOIN commerce_orders commerce_orders ON commerce_orders.id = elements.id\nLEFT JOIN commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId\nLEFT JOIN commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId\nINNER JOIN elements_sites elements_sites ON elements_sites.elementId = elements.id\nINNER JOIN content content ON content.elementId = elements.id\nWHERE (commerce_orders.isCompleted=TRUE) AND (field_storeType='standard') AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.draftId IS NULL) AND (elements.revisionId IS NULL)) subquery\nINNER JOIN commerce_orders commerce_orders ON commerce_orders.id = subquery.elementsId\nLEFT JOIN commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId\nLEFT JOIN commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId\nINNER JOIN elements elements ON elements.id = subquery.elementsId\nINNER JOIN elements_sites elements_sites ON elements_sites.id = subquery.elementsSitesId\nINNER JOIN content content ON content.id = subquery.contentId"} `

[sjcallender]

@johnnynotsolucky - Can you take a look at this tomorrow?

[aeu]

FYI

MariaDB [myDatabaseName]> show columns from content;
+-------------------------------------+---------------+------+-----+---------+----------------+
| Field                               | Type          | Null | Key | Default | Extra          |
+-------------------------------------+---------------+------+-----+---------+----------------+
| id                                  | int(11)       | NO   | PRI | NULL    | auto_increment |
| elementId                           | int(11)       | NO   | MUL | NULL    |                |
| siteId                              | int(11)       | NO   | MUL | NULL    |                |
| title                               | varchar(255)  | YES  | MUL | NULL    |                |
| dateCreated                         | datetime      | NO   |     | NULL    |                |
| dateUpdated                         | datetime      | NO   |     | NULL    |                |
| uid                                 | char(36)      | NO   |     | 0       |                |
| field_summary                       | text          | YES  |     | NULL    |                |
| field_wholesalePrice                | decimal(12,2) | YES  |     | NULL    |                |
| field_subtitle                      | text          | YES  |     | NULL    |                |
| field_productDescription            | text          | YES  |     | NULL    |                |
| field_shortTitle                    | text          | YES  |     | NULL    |                |
| field_titleSegmentOne               | text          | YES  |     | NULL    |                |
| field_titleSegmentTwo               | text          | YES  |     | NULL    |                |
| field_featuredVideo                 | text          | YES  |     | NULL    |                |
| field_contentBlock1_zbffhfye        | text          | YES  |     | NULL    |                |
| field_contentBlock2_cmajwvhx        | text          | YES  |     | NULL    |                |
| field_contentBlock3_uyecsfae        | text          | YES  |     | NULL    |                |
| field_contentBlock4_oxnjsxqw        | text          | YES  |     | NULL    |                |
| field_contentBlock5_iucermmr        | text          | YES  |     | NULL    |                |
| field_featuredVideoPadding_jkrvxrlv | text          | YES  |     | NULL    |                |
| field_costOfGoods_vgdhhdrq          | decimal(12,2) | YES  |     | NULL    |                |
| field_message_toqblkac              | text          | YES  |     | NULL    |                |
| field_storeType_vvcxynwm            | varchar(255)  | YES  |     | NULL    |                |
| field_anotherStoreType_uazjzkcy     | varchar(255)  | YES  |     | NULL    |                |
+-------------------------------------+---------------+------+-----+---------+----------------+
25 rows in set (0.001 sec)

[johnnynotsolucky]

@aeu thank you for the column listing. The important information is that the field column names now include a suffix for all new fields. See https://github.com/craftcms/cms/issues/6922.

Unfortunately the change mentioned is a breaking change for any code which relies on queries which touch the field columns in the content table. In this case, it's related to OrdersController.php#L137-L140 where we assume the a field is assumed to have the format of "field_{$handle}".

This should be resolved in 1.3.7