automatic price & stock updates from Pastel Partner to MySQL website?

[Salad_Dressing]

We are running Pastel Partner version 11 (build 11.2.1) and I am putting up a webpage for our business (HTML with PHP and MySQL backend).

Is there a way to automate updating of product prices and stock count from Pastel Partner to the MySQL database?

Any help would be greatly appreciated!

Thanks in advance.

[Neville Bailey]

Unfortunately my knowledge about MySQL databases is negligible, so I can't comment on that aspect of your query.

However, if you have Pastel's Business Intelligence Centre (BIC) module, you can extract the relevant data from the Pastel database into an Excel format. From there it may well be possible to transfer the data into a MySQL database, but you might be better qualified to answer that question.

[Dave A]

However, if you have Pastel's Business Intelligence Centre (BIC) module, you can extract the relevant data from the Pastel database into an Excel format.

Can it be extracted as a csv or XML file by any chance?

[Neville Bailey]

Can it be extracted as a csv or XML file by any chance?

Not directly from Pastel, but a macro button can be embedded into the BIC Excel report to generate a CSV format file.

[Dave A]

Surely Pastel has a connector app for third party plug-in developers?

[Neville Bailey]

Surely Pastel has a connector app for third party plug-in developers?

Yes, it does, but one has to find the right Pastel-accredited 3rd Party Developer. Here is a list of them.

[Dave A]

I hear you about the "authorised" bit, but ...

Salad_Dressing - do a Google search for pastel sdk download and see if you can find the relevant version. If you tack on free at the end of that the result does change a bit.

[Salad_Dressing]

Thanks all for your inputs into this matter. Here's what I have found...

Pastel can output spreadsheets and I can easily script to rip and update my database with them. However that does not fully automate the process.

a link to Pastel Partner 3rd Party Developers

a link to Pastel SDK download

I installed the SDK to see what sort of language I'll be getting myself into and I see examples in VB6 (Visual Basic?)

Anyway, I was really hoping for some sort of scripting language which can hook into Pastel Partner in order to perform my functions to our website's database. I find it hard to believe that in the Internet age there's no way to automate stock counts and price changes in a fairly easy way???

Here's another approach, perhaps...

Can Pastel Partner be set up to create scheduled CSV exports, let's say each half hour? I can then write code to pick up the latest CSV file, parse it, and update the online database.

[Salad_Dressing]

The other thing I want to ask is this: Can Pastel Partner be queried from outside via an online connection to obtain relevant stock and pricing information, much like one would query a database?

[Salad_Dressing]

OK, I may have found a solution where the SDK is used through PHP... check out this link on the Pastel Development Zone.

[Neville Bailey]

The other thing I want to ask is this: Can Pastel Partner be queried from outside via an online connection to obtain relevant stock and pricing information, much like one would query a database?

It depends on what you mean by a query.

If you want to access Pastel remotely via the user interface, then I would simply create a VPN connection from the outside to the local machine and then run Pastel remotely. You can create user profiles that limit the remote user from accessing certain sensitive info. You can also use systems such as LogMeIn or TeamViewer (my personal favourite).

Is that what you had in mind?

[Salad_Dressing]

It depends on what you mean by a query.

If you want to access Pastel remotely via the user interface, then I would simply create a VPN connection from the outside to the local machine and then run Pastel remotely. You can create user profiles that limit the remote user from accessing certain sensitive info. You can also use systems such as LogMeIn or TeamViewer (my personal favourite).

Is that what you had in mind?

I want to do what you are saying without using Pastel remotely via a user interface, but rather using a PHP script running from a server to sign-in to a Pastel host using the correct protocols and authentication credentials.

From such a connection I can imagine very easily doing a query for stock levels and latest prices and then updating the website database accordingly. It would also mean that task scheduling (e.g. doing a stock update every 15 minutes) would be handled by the web server running PHP with no burden on the Pastel Partner sys admin.

[Neville Bailey]

I want to do what you are saying without using Pastel remotely via a user interface, but rather using a PHP script running from a server to sign-in to a Pastel host using the correct protocols and authentication credentials.

From such a connection I can imagine very easily doing a query for stock levels and latest prices and then updating the website database accordingly. It would also mean that task scheduling (e.g. doing a stock update every 15 minutes) would be handled by the web server running PHP with no burden on the Pastel Partner sys admin.

Hmmm, I'm afraid you have surpassed my level of expertise in this case! I have no idea whether it is possible to do what you want and, if so, how to do it.

[Dave A]

I've kept pretty quiet up to now because we are doing something similar - but there are differences which may be significant.

First, we're on Quickbooks - which has to run on a Windoze machine.
Then we're connecting to Sugar CRM, which we've got running on a Kubuntu server.
Third, the two hosting machines are within a LAN, not a WAN setup like you're facing.
Fourth, the SDK for Quickbooks pumps out its query returns in XML.
Lastly, we're using a javascript rather than a PHP script.

However, the basic principle should be pretty similar.

1. The SDK query tool resides on the same machine as the financial package (the Windoze server) and is all set up to return data when queried. I expect this will be much the same in Pastel.

2. The query script is set up on the Kubuntu server and is triggered by a CRON job. However, because it's javascript it could just as easily reside on the Windoze machine which I suspect is probably the way you should think about going.

3. The script queries the SDK script, which returns the data, which is then parsed by the script to match the Sugar MySQL fields it needs to fill, and then appends or updates the records in the Sugar db using MySQL queries and commands. This needs a coder!

Essentially you're constructing a bridge between Pastel and your MySQL database. The SDK just helps you extract data out of the Pastel database via script rather than a user interface. But from there you're pretty much on your own.

[AndyD]

I've also been watching this one from a distance. It's been a few years since I was involved in pastel database systems but a few things strike me immediately.

Firstly you're dealing with Pastel Partner which as far as I know is pervasive database system which evolved from the old btrieve system. Does Partner come with a sql frontend nowadays? Evolution used sql databases so it might be more suitable for what you're trying to do.

Secondly with either system you can set up simple transactions without the sdk kit. You obviously need to know which tables you're dealing with, the fieldsand their relationships but lifting customer lists for example would be straight forward. Complex transactions can also be performed directly into the tables but for safety purposes it might be easier to use sdk scripts for this.