Pastel Partner Database - Pervasive

[Riandwelm]

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

[ulbricht]

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