View Order State - not status

[metalmattlee11]

Someone has previously made a mess of my order State//Status. Shame on me for giving them the access to do this.

I need to clean this up and in order to do that, I need to see both the order State and status. The Status is easily viewable on the Orders screen and the individual order. I don't see how I can view the State and status so I can start to see what I need to do to move orders to appropriate State and then status. Once I see this, I can figure out what temporary and permanent changes I need to make to delete the unneeded Statuses, since right now, there's a flood of orders in the wrong State and Status.

Thanks.

[Ankit Jasani]

Hi @metalmattlee11 ,

SELECT entity_id AS order_id, state, status, increment_id, created_at FROM sales_order ORDER BY created_at DESC LIMIT 100

Thanks,

Ankit

[metalmattlee11]

Thanks @Ankit Jasani. The next question is how can I change/fix these - I assume a direct change of state in this table will create a problem for the system? I see several other tables where the status is listed, and I don't want to corrupt my database by having a state changed when theres no appropriate status in the table for it, and/or it doesn't match the status in the other tables like sales_order_grid.

[Ankit Jasani]

Hi @metalmattlee11 ,

1) First necessary step is to take backup of your db.

2) Run several queries and prepare list of problem orders (collect increment_ids)

3) Take 5-10 increment_ids & order_ids

Evaluate sales_order, sales_order_grid etc tables to evaluate and figure out consistent pattern and prepare SQL to update at everyplace at once (1 or multiple queries for different tables)

4) Reindex and flush cache.

Thanks,

Ankit

[metalmattlee11]

Hi @Ankit Jasani ,

Thanks - I'll try this. The key seems to be those 2 particular tables and no others - sales_order & sales_order_grid, and those 2 columns - increment_id & order_id. I did a little checking and found that I have statuses that actually can't be updated - that they somehow are issues for the state progression "enforced" by Magento, although I'm not sure why. Nevertheless, if I can get these old orders dealt with, I can ensure going forward that I can have a sense of "order" with my orders.

[Ankit Jasani]

Hi @metalmattlee11 ,

You’ve nailed the key point. Magento / Adobe Commerce enforces state > status relationships. Each state has only certain valid statuses. Also, audit your sales_order_status_state mappings to keep them clean.

If any of my answers helped solve your problem, please mark it as the accepted solution. This will help others facing the same issue.

Thanks,

Ankit