Results 1 to 5 of 5

Thread: Updating Pastel tables from outside Pastel

  1. #1
    New Member
    Join Date
    Aug 2019
    Location
    Hermanus
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Pastel tables from outside Pastel

    Hello,

    I've decided to make this a separate thread to my previous post (on pulling data from Pastel to Excel using VBA and ODBC connection). I now want to attempt the opposite: Sending updated stock values to the relevant pastel stock table.

    Let me explain the WHY before I continue:
    We have long lengths of timber in stock, but frequently (actually many times a day) have to cut long pieces up into shorter pieces which gets used in our products. This requires us to do inventory journals daily (taking long lengths out of stock and putting back shorter lengths and so on...) which is extremely tedious and time consuming in Pastel.


    I would therefor like to do this inventory journal directly into the Pastel database using data from my picking slip. This will require a very careful and full understanding of which tables gets altered when running an inventory journal and then replicating (verry carefully and thoroughly off course!) the same process in VBA's SQL statements...

    Questions:
    1) Anyone here done something similar before?
    2) How can I "audit" / "verify" which tables was updated in the database while running an inventory journal?

    Again, as always, if there is a way more obvious way of handing a large amount of inventory journal items please share? (maybe a dedicated Pastel module which I'm unaware of? or an import function of sort?

    thank you
    PMH

  2. #2
    Full Member
    Join Date
    Jul 2019
    Location
    Pretoria
    Posts
    71
    Thanks
    8
    Thanked 9 Times in 9 Posts
    Hi PMH

    Thanks for your message.

    Regarding your questions.
    1. We have done something similar to this, where we had to speed up capturing 100 fold. We built a Pastel Partner import tool called MyDocuments and MyImporter which we use to quickly generate multiple transactions for any EType (including any journal), create the CSV and then process through the import interface in Pastel. We spoke to a few Pastel data specialists who also agreed that using Pastel's import facilities within the GUI is the best as multiple data files are updated with every transaction and processing outside of Pastel exposes you to risk that you miss a data table update.
    2. I would recommend you process an inventory journal close Pastel and then go to the raw .dat files. Filter by date and see which .dat files updated. That should show you which .dat files were updated for the transaction. (remember to only process an inventory journal in the Pastel session as to ensure that you do not update any other .dat files). Alternatively you can make use of our off the shelf tool, MyBI, which gives you insight into your raw data files, enabling you to audit transactions audit trails.

    Hope this helps.

    Kindest regards,
    Alec Candiotes CA(SA), MCom Taxation
    Alec Candiotes (CA) SA
    Business Intelligence and Report Automation developer

    www.mybi.co.zaalec@mybi.co.za • 072 997 9553

  3. #3
    Silver Member
    Join Date
    Aug 2018
    Location
    Johannesburg
    Posts
    262
    Thanks
    0
    Thanked 35 Times in 31 Posts
    Hi PMH

    I agree with Alec re using the batch import functions to maintain the integrity of the database files. You can raise an inventory journal, export it, and see what the import batch file's structure needs to be. Under the help menu in Pastel, search for import file layouts - the layout of the inventory journal specific to your version of Pastel will be there.

    When you post an inventory journal - the stock transaction will be written into the historylines file (acchistl). While the batch is open, you should write entries to the unposted file (accprdq), which clears on update of batch (skip if writing directly to tables). The stock qty needs to be adjusted in the multistoretransaction file (accstkst) in that period. The general ledger entries (inventory control and inventory adjustment) need to be written to the ledgertransaction (acctrn) file and the balances need to be updated in the LedgerMaster (accmas).

    I am not a developer - not sure how long it would take to get all that right, tested and functioning - but it would seem like inventory journal batch imports would probably be easier.

    Good luck

  4. #4
    Full Member
    Join Date
    Jul 2019
    Location
    Pretoria
    Posts
    71
    Thanks
    8
    Thanked 9 Times in 9 Posts
    Thank Kevin. You are spot on. Exporting already loaded transactions before batch update gives you a great learning environment.

    Thanks for sharing your insight on the .dat files. Very insightful.

    Have a wonderful day.
    Alec Candiotes (CA) SA
    Business Intelligence and Report Automation developer

    www.mybi.co.zaalec@mybi.co.za • 072 997 9553

  5. #5
    New Member
    Join Date
    Aug 2019
    Location
    Hermanus
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Okay, good ideas. Yes, I think I'm going to follow the advice and create .csv files to import each time an inventory journal must be run.

    Thanks guys

Similar Threads

  1. Pastel Partner SDK - Updating Inventory Categories (C#)
    By franktank in forum Accounting Forum
    Replies: 1
    Last Post: 17-Jul-19, 08:04 AM
  2. [Question] Pastel GRN batches not updating
    By Coolblue in forum Accounting Forum
    Replies: 0
    Last Post: 03-Feb-18, 05:43 PM
  3. [Question] Updating Pastel Supplier Payments from previous year end
    By sonjennik in forum Accounting Forum
    Replies: 6
    Last Post: 29-Nov-16, 01:26 PM
  4. DATA TABLES FOR PASTEL EVOLUTION
    By jonesey in forum Accounting Forum
    Replies: 0
    Last Post: 10-Oct-13, 03:54 PM
  5. Replies: 1
    Last Post: 10-Dec-12, 11:37 AM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •