Results 1 to 2 of 2

Thread: Pastel Partner Database - Pervasive

  1. #1
    Email problem
    Join Date
    Mar 2017
    Location
    Randfontein
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pastel Partner Database - Pervasive

    Morning Everyone

    So i need some help with reports I'm writing . I'm currently using power bi desktop to write visual reports for our business . Firstly my database is from pastel partner and as you all know its a pervasive database . Well we have created warehouses for all our trucks and products . So for each truck their will be an inventory code with the extension the product . for example
    JKL919-50 PPM
    JKL919-10 PPM
    JKL919-IP
    JKL919-93ULP
    JKL919-95ULP

    These inventory codes are linked to certain GL CODES that represent branches and their products like :

    Sales Brandvlei Diesel - 1005/101
    Sales Brandvlei IP -1005/102
    Sales Brandvlei ULPPETROL -1005/103

    Now I would like to now how do i link the two tables together so that when i create a report i can see the total sales as per income statement and then next to it i have the quantity and then i have the average selling price per litre as all clients have their own unique prices .

    I know this is possible because i can do this on a monthly basis using excel .

    So basically i would like to link the following two tables together History Lines and the Ledger transactions with a formula .

    If anyone could assist that would be awesome !!!

    Warm Regards
    Rian Smith

  2. #2
    Junior Member
    Join Date
    Feb 2017
    Location
    zimbabwe
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good day

    Depending on the version of PSQL, you can create a view (table) combining the two source tables into one.

    Your income source table should give the 'balance' at a given period and the stock source table the 'stock on hand' and 'other details' at a given period.

    The trick is to identify a unique field in HistoryLines which is also in LedgerTransactions then use it to join the two.

    Your join statement would be something like ([LedgerTransactions]
    INNER JOIN [HistoryLines]
    ON [LedgerTransactions].[UniqueField] = [HistoryLines].[UniqueField])

    Resultant table (view) will show income a/c balance and linked stock item details at a given period.

    NB: Works only if one stock item is linked to one income a/c

Similar Threads

  1. At last! Pastel's Pervasive SQL to be replaced by MS SQL!
    By Neville Bailey in forum Accounting Forum
    Replies: 8
    Last Post: 16-May-16, 08:51 AM
  2. Pastel Xpress Startup - Pervasive
    By Malcolmw in forum Accounting Forum
    Replies: 5
    Last Post: 15-Dec-15, 11:25 AM
  3. finding details of pastel accounting in pervasive database
    By NAIMIKA in forum Accounting Forum
    Replies: 1
    Last Post: 13-Aug-15, 11:18 AM
  4. [Question] Tech question - Pastel Xpress Start-up. Pervasive Database VERY slow
    By johnmac in forum Technology Forum
    Replies: 0
    Last Post: 11-Aug-15, 04:55 PM
  5. Replies: 3
    Last Post: 04-Aug-12, 08:18 PM

Tags for this Thread

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
  •