Actinic to Linnworks Script

[gabrielcrowe]

I'm writing a connector for Actinic to sync its orders with Linnworks

Its nearly completed, it syncs and most of the data is sent to the package for processing. Mostly it works just great. I'm stuck on a few things and I need some help regarding any changes in Actinic's database structure since I last wrote a plugin (V9). Mainly, the problems orient around syncing stock back into Actinic.

Limitations in the software mean you can execute a single SQL statement to update the status of stock on a per item basis. The sql can contain the [Product reference]. My question is: how do Actinic maintain stock with this new fangled online system. Is there a single item to update in [Product]?

Are there any fields in permutation tables i need to worry about?

[I-CC]

My understanding that the online stock function is controlled from the single file called stock.fil - this is what Actinic Desktop updates when it uploads or collects orders and is what the online site updates as it receives orders (prior to the desktop application downloading the orders).

[fergusw]

Gabe,
Have a look at http://community.actinic.com/showthread.php?t=51475

Direct inject stock updates look like they may violate the synch'ing of Actinic desktop to web and therefore require a normal update to resynch. File import updates certainly look like they do, and I would imagine "under the hood" DB changes will as well.

[gabrielcrowe]

oh jesus christ, its never simple :/

I guess stock sync from linnworks is out of the question then.

[gabrielcrowe]

can somebody give me an example of stock.fil? is it an array?

[I-CC]

Afraid the resultant file is encrypted in the local and online versions, unlike almost every other .fil file which is ASCII notepad readable

[gabrielcrowe]

looks like the way its built, i cannot sync stock because it would disrupt the way Actinic syncs online.

Linnworks also wants to mark the order as completed in the software and sync that across too looks like without an API (to properly complete an order without hacking parts of the database) to help me out i'm going to make this a 1 way sync only.

Ah well.

[fergusw]

Gabe, there is a way!
As well as changing the [nStockOnHand] field in the product table you also need to create an entry into the "OutstandingStockChanges" table, which is used during synching with online stock levels.

e.g.
Decrease product stock by 10 in product table
Create entry in "OutstandingStockChanges" table:

INSERT INTO [OutstandingStockChanges] SET (sProdRef,nStatus,nChange) VALUES ({prodRef},0 , -10)

At the next synch Actinic will "act" on this entry, update the online stock accordingly and then delete this entry, and all other entries, from the OutstandingStockChanges table.

Should work

[fergusw]

Just undertook some simple testing and this works.
As long as when changing the stock levels directly in the database, and when using online stock control, you create an appropriate entry in the OutstandingStockChanges table the stock change will be updated onto the website at the next synch.

[Mike Hughes]

Hi Fergus, useful info.

Do you know if it'll work with increasing stock as well as decreasing? For example if you have new stock arrive. I think it should work but it's always nice to have confirmation.

Mike

[fergusw]

Hi Mike,
this works both ways. i.e. if you incremented the stock level by 10 for a product with reference ABC123 then you'd simply execute
INSERT INTO [OutstandingStockChanges] SET (sProdRef,nStatus,nChange) VALUES ('ABC123',0 , 10)

[note the positive 10 value]

[rbagga]

Hi Fergus, So would you say that I would be better of using a sql to update my stock levels in both tables:

[product] with (with actual stock)
UPDATE Product SET Product.[Product Reference] = "ABC123", Product.nStockOnHand = 2;
&
[OutstandingStockChanges] with (stock difference)
UPDATE OutstandingStockChanges SET OutstandingStockChanges.sProdRef = "ABC123", OutstandingStockChanges.nStatus = 0, OutstandingStockChanges.nChange = 1;

Then use the switch "C:\Program Files\Actinic v11\Catalog.exe" /d to Update Online Stock (time 6 sec's)?

As apposed to importing a flat file (time 300 sec's)and then being forced to update after (time 3,600 sec's)? (time = my site & approx)

All very interesting stuff BTW and guess potentially likely to open "a whole can of worms" if this all works!

[fergusw]

[product] with (with actual stock)
UPDATE Product SET Product.[Product Reference] = "ABC123", Product.nStockOnHand = 2;
&
[OutstandingStockChanges] with (stock difference)
UPDATE OutstandingStockChanges SET OutstandingStockChanges.sProdRef = "ABC123", OutstandingStockChanges.nStatus = 0, OutstandingStockChanges.nChange = 1;

That's pretty much it, however you have to make sure the quantity in the second query is the variance in stock i.e. If the change in nStockOnHand is an increase of 10 (e.g. from 34 to 44) your first query should be "....nStockOnHand = 44...." and the second would be "......nChange = 10...."

I hope that's clear.

Secondly, if you have stock control switched on you would not have to invoke a "catalog.exe /d" command. Actinic will take care of that for you, as I indicated above.

All very interesting stuff BTW and guess potentially likely to open "a whole can of worms" if this all works!

If it's worms in the can, then perhaps a quick weld on the lid might be better!

[rbagga]

Perfect! Understood loud and clear! I'll get the welding gun out!

[gabrielcrowe]

I have it working now at skate asylum.

The problems i cannot solve are:

- No stock can be sync'd back into actinic, the way it deals with stock makes this nearly impossible. (Just read the replies and i'll look into this solution. looks promising)

- Linnworks cannot mark Actinic orders as completed. I get around this one becasue I can get Linnworks to change the highlight colour in Actinic of orders completed in the Linnworks app. This means that somebody can manually select the highlighted orders and complete them en-masse.

- ALL Actinic line items need unique reference numbers. If your items dont have them I have written a script that simply generates numbers based off a random number and the name of the product. This fixes the issue. Auto-numbers aren't enough. Can't figure out why but it does odd things.

So basically, it imports all the orders and highlights them in actinic when you 'complete' them and not a lot else.