Display Record if Join/Table doesn't exist

[ciaran_mcgowan]

Hi,

I have a customer who uses QV to display the amount owed to them by their clients. They have 3 tables that are used to calculate this; SALE -> PAYMENTMETHOD -> PAYMENT.

SALE joined to PAYMENTMETHOD using SaleID

PAYMENTMETHOD joined to PAYMENT using PaymentID

A problem arises when the payment method is set to Layaway and there is no initial payment, the debt is not displayed because there is no PaymentID. Is there a way to create a dummy record for the PAYMENT table if the PAYMENTMETHOD = 'Layaway'? As it currently stands, users have to populate the PAYMENT table with an initial value of £0.00.

Thanks

[#2]

Ciaran,

do you have 1 table after this join, or there are 3 associated tables?

regards

Darek

[ciaran_mcgowan]

Hi Dariusz,

I'm not sure I follow the question, these three tables are not linked to each other: SALES & PAYMENT are not directly linked, they are only linked to PAYMENTMETHOD.

The report takes the amount of money the sale was for, checks the payment method and then takes away the sum(payed_amount). But if there has been nothing paid, the total amount left to be paid isn't displayed as there is no PaymentID stored in the PAYMENT table that matched the PAYMENTMETHOD table.

I hope that explains it.

[#4]

Ciaran,

You may generate those dummy rows in load script.

After loadingsome table you may use resident and where clauses to find some rows in previously loaded table.

Than you may prepare data for rows you find and concatenate it with another table. It is rather easy. But if you need more assistance, please attach script part where those 3 tables are loaded.

You may also try to use set analysis in your expression.

regards

Darek