Custom total value and calculated fields

[Seph]

Good day everyone,

Really sorry to ask such a mundane question here, but I'm struggling to finish off my invoicing.

In short, I make use of a query (InvoiceSalesQ) to calculate Totals on my InvoiceSalesF form.

However, there are times where I want to amend the Total amount for certain instances (E.g. discounts, certain clients get certain pricing etc.), while still showing clients what they would've paid.

I know that Calculation fields are always read only as per this article (http://allenbrowne.com/ser-61.html).

Is there perhaps a way that I can get an unbound field on my form with the calculated amounts and still be able to amend it?

Thank you!

 

[XPS35]

Maybe you can use a (bound) discount field?

 

[Pat Hartman]

Instead of saving the calculated value, save the fixed price. Then calculate the discounted price in your query.

 

[Cotswold]

If you have a discount column, clients that you don't give discounts to will want to know about the discount is and why do they not have one. You will fail to convince anyone not on a discount just why that is.
Better as Pat H suggested, work it out in your query and show it as an extra line.

 

[Seph]

Instead of saving the calculated value, save the fixed price. Then calculate the discounted price in your query.

Sorry for the late reply everyone, year end is hectic.

@Pat Hartman thanks for your suggestion. I don't store the calculated field (Fixed Price). Only the Time & Labour rate (Calculated) fields are stored in tables.

The Fixed Price field is simply for the sake of printing the invoice.

I'd like to still keep the Unbound SUM field (Fixed Price) for most invoices. It's only necessary for my SLA customers whom pay in advance for labour, so I don't want those totals appearing on their invoices. Yet it should still reflect what they would of paid had they not taken the SLA, reassuring that they made the correct choice.

Its not a discount per say, its payment made in advance. Like loading cell phone time that gets depleted over time.

 

[arnelgp]

you should add the field to your table.
for history purpose.
if today you add value to fixed price, maybe later on, for some other reason
you want to check the transaction on a specific date/customer.
rather than when you go back, you see that the fixed price is not saved
and you wonder what have you put into it.

documentation and recording is the most vital in any business.

 

[Pat Hartman]

I'd like to still keep the Unbound SUM field (Fixed Price) for most invoices.

You can't store an unbound field. Your item record should contain quantity and price. When you select the item with a combo, you should also have the price in the combo's RowSource. That way you can save it:

Me.Price = Me.cboProductID.Column(2)

The RowSource of the combo is a zero based array so .Column(2) refers to the third item assuming you select ProductID. ProductName, Price

So, when you print the invoice, if there is a discount, you can apply it there and also show the regular price. I'm not sure where the discount percent comes from. Is it stored in the customer record? If so, the invoice needs to join to the Customer table but it probably needed to anyway to get the name and address info. The only time you would need to store the regular price is if you can actually change the regular price in the item record. And then you need some logic to determine if you will be applying the discount or not.

 

[Seph]

Thanks to everyone for their input.

I implemented a Discount field and a Invoice Total that stores the final value.

I appreciate the insights.