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

[Salad_Dressing]

Right-clicking server properties when inside Pervasive Control Center brings up a list of properties of which "Communication Protocols" contains the port number.

However, when I click on Communication Protocols the panel with the settings is nearly blank because of some redraw problem. After clicking repeatedly on the property list, the page showed properly and I was able to see the port number.

Now I need to set up the router's port forwarding to achieve remote access and then start testing the setup via PHP ODBC queries.

[Dave A]

Sounds like you're making good progress.

[Salad_Dressing]

After some interruption, this project is moving forward again, hoping to complete it soon.

I'm successfully accessing Pastel Partner remotely via PHP script. I can query the tables, view their columns and rows, etc.

However, I'm not understanding how to access product price lists. I'm not a Pastel expert. All I understand is that Pastel installations have similar data structures.

Here's a list of tables which I can access, but none of them appear to be a price list when I query its contents:

AccountUser
ActivityMaster
AnalysisCodes
BankDetails
BICUsers
BillingParameters
BOMHeader
BOMLines
CashBookCurrency
Category1099
CompanyParameters
CostCodeBudgets
CostCodeLevelLinks
CostCodeLevelNames
CostCodeLevelSetup
CostCodes
CostCodeTransactions
CurrencyFile
CustomerCategories
CustomerDiscount
CustomerMaster
CustSuppParameters
DeliveryAddresses
EmployeeActivities
EmployeeMaster
Euro
FinancialCategories
Fonts
GAAP
HistoryHeader
HistoryLines
HistoryTax
HistoryUserDesc
HistoryUserSetup
InternetBank
InternetBenf
Inventory
InventoryCategory
InventoryGroups
InventoryLink
InventoryParameters
JumpUserAccess
LedgerBalances
LedgerMaster
LedgerParameters
LedgerTransactions
LinkHeader
LinkLines
MenuAccess
MultiStore
MultiStoreTrn
Notes
OpenItem
POSAudit
POSCashUp
POSPaidOuts
POSPayments
POSSetup
PreferredSupplier
ProcessOptions
ReceiptTransactions
ReportCategories
ReportExport
ReportWriter
SalesmanMaster
SerialMaster
SerialTransaction
SupplierCategories
SupplierMaster
SystemParameters
TaxBox
TaxDescription
TaxParameters
TimesheetHead
TimesheetLines
Unposted
UserGroups
UserStore

[Neville Bailey]

Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.

[Salad_Dressing]

Selling prices are stored in the MultiStoreTrn data table, if I'm not mistaken.

Fantastic! Found the prices. Thanks Neville!

MultiStoreTrn turns out to be one of the few tables I had not yet searched in because the name just does not sound like it would hold item prices!

[Salad_Dressing]

I'm now also looking for the Pastel table where the stock on hand & stock available is stored.

Can anyone please help?

For those interested in this project: I've installed XAMPP onto the Pastel server, allowing me to remotely execute local PHP scripts which perform the queries and return the data to the web server via the HTML post feature, all via PHP. This technique reduces the query time from 9 minutes to 6 seconds. This makes it feasible to run fairly frequent price and stock update scripts.

[Neville Bailey]

I'm now also looking for the Pastel table where the stock on hand & stock available is stored.

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

[Salad_Dressing]

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

Thanks again Neville!

Honestly, without your inputs I'd be in the dark since none of this is obvious.

I'll run this calculation and have the Pastel lady check it.

[Salad_Dressing]

You will find that in the MultiStoreTrn table as well, although you will need to do a calculation of a number of fields there in order to get the info you need.

Stock on Hand is OpeningQty, plus QtyBuyThis01 to QtyBuyThis12, plus QtyAdjustThis01 to QtyAdjustThis12, minus QtySellThis01 to QtySellThis12.

I have played with the formula in all the ways I can think of, but nothing I have done so far matches the actual Stock-on-Hand reported in Pastel Partner.

Do QtyBuyThis01 to QtyBuyThis12 represent activity over the 12 months of the financial year?

If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04

OR

Should the formula be inclusive of all 12 columns, something like this:

$tB = $qB01+$qB02+$qB03+$qB04+$qB05+$qB06+$qB07+$qB08+$q B09+$qB10+$qB11+$qB12; //QtyBuyThis
$tA = $qA01+$qA02+$qA03+$qA04+$qA05+$qA06+$qA07+$qA08+$q A09+$qA10+$qA11+$qA12; //QtyAdjustThis
$tS = -$qS01-$qS02-$qS03-$qS04-$qS05-$qS06-$qS07-$qS08-$qS09-$qS10-$qS11-$qS12; //QtySellThis
$qOH = $qO + $tB + $tA - $tS; //qO = OpeningQty

[Neville Bailey]

If so, would the formula for Stock-on-Hand for the 4th month be: OpeningQty + QtyBuyThis04 + QtyAdjustThis04 - QtySellThis04

No, it would be:

OpeningQty + QtyBuyLast + QtyBuyThis01 + QtyBuyThis02 + QtyBuyThis03 + QtyBuyThis04 + QtyAdjustLast + QtyAdjustThis01 + QtyAdjustThis02 + QtyAdjustThis03 + QtyAdjustThis04 - QtySellLast - QtySellThis01 - QtySellThis02 - QtySellThis03 - QtySellThis04

Sorry, in my previous post I forgot to mention that QtyBuyLast, QtyAdjustLast and QtySellLast must also be included. OpeningQty represents the quantity on hand at the beginning of the prior financial year.