Override result in formula field

Hi Davie

Rather than using a  formula field use a number field and a script in a trigger to populate it. You will be able to amend the field as you wish afterwards. You may want to build in a mechanism that doesn't allow the trigger to re-calculate the field value once it has been manually adjusted.

Regards John

Davie

John's advice is good.

Similarly I have a cost - Markup % and Total fields  (all input fields instead of having the total as a calculated formula). In each of these is a script to then calculate the other field after any 2 have been input.

ie I enter cost and markup then sales is calculated

I enter the cost and total then markup is calculated

I use many triggers that only fire provided several other switch condition have been met.

That the beauty of Ninox it can be simple or quite elegant!

My two cents for stuff like tracking cost per invoice per client I would stick with number fields as much as possible so your history is kept accurate. I use formula fields a lot, but if you change something in the future and don't track the change properly then you might screw up your historical records since the formula fields haven't been updated.

So in your case I would say you have fields called hours, rate, discount, total.

You said in your original post:

Davie said:
but I want to offer a one-off discount and charge £150

 I don't see how your Trigger code on Appointment allows you to do this.

Your Trigger code finalizes Price as text. Plus I see that anything over 4 hours, the Price field will say "Manual Quote". That means you have to go into Price and manually input data?

In my mind, you would need 4 fields (hours, rate, discount, total). Hours, rate, and total are formula fields. The only number field would be discount so you can input data per invoice.

Back to your question about your Trigger code:

Ninox accepts that but when I go back and look it's added a couple of lines. The last lines become:

That is interesting. I copied your code and put it in my table and I don't get that. Here is what my DB shows me:

Price2 := (
        let x := number(duration(Appointment) / 3600000);
        ceil(x);
        if date(Appointment) > date(2023, 7, 25) then
            if x > "4" then
                "MANUAL QUOTE!"
            else
                if x > "3" then
                    "£400"
                else
                    if x > "2" then
                        "£300"
                    else
                        if x > "1" then
                            "£200"
                        else
                            if x = "1" then "£100" end
                        end
                    end
                end
            end
        end
    )

And it works just fine.

For me, I start to lose track of my embedded ifs after 1. You can use the switch() command to help keep things neater:

Price := (
        let x := ceil(number(duration(Appointment) / 3600000));
        switch date(Appointment) > date(2023, 7, 24) do
        case x > 2:
            200
        case x > 1:
            100
        end
    )

Just follow the pattern to add cases for the other three options.

I guess another option is if you want to keep using the Fee formula field then you need to add a discount number field so you can add this field into the Fee formula field.