Auto-Calculate Invoice Line-Items

This topic is 6633 days old. Please don't post here. Open a new topic instead.

Recommended Posts

The idea for this style of auto-enter calculations came from a post by Queue in late June or early July which I cannot now locate on the Forums. His post was regarding two-way conversion between inches and centimetres

I have Invoice line-items with three currency fields: Net, Tax, Gross and a variable TaxRate field.

Initially I want to Auto-enter the Quoted amount into Net and then calculate the other fields with a default tax rate which is Auto-entered as Data (0.175 = Standard rate of VAT in the UK). Then I want to be able to change the amounts for various reasons (a customer negotiates a Gross amount for the job rather than Net + Tax; Some jobs have non-standard tax rates; extremely rare - it is necessary to make a change to the actual tax amount to account for creeping round-off error with multiple items; etc) So we have a table of allowed changes and the changes that have to follow

Change Net --- recalculate Tax and Gross

Change TaxRate --- recalculate Tax and Gross

Change Tax --- recalculate Gross

Change Gross --- recalculate Net and Tax

This is accomplished by the following Auto-enter calculations

Net

```Case(

IsEmpty(Net);

Quote::QuotedNet;

Get(ActiveFieldName)="Gross";

Round(Gross/(1 + TaxRate);2);

Net)```
```

Tax

```
```Case(

Get(ActiveFieldName)="Net";

Round(Net*TaxRate;2);

Get(ActiveFieldName) = "TaxRate";

Round(Net*TaxRate;2);

Get(ActiveFieldName)="Gross";

Round(Gross*TaxRate/(1+TaxRate);2);

VAT)```
```

Gross

```
```Case(

Get(ActiveFieldName)="Net";

Round(Net*(1+TaxRate);2);

Get(ActiveFieldName)="TaxRate";

Round(Net*(1+TaxRate);2);

Get(ActiveFieldName)="VAT";

Round(Net + VAT;2);

Gross)```

Obviously the Case statements can be made more efficient since the same calculations are being repeated but with my limited memory I like the statements to make obvious sense.

Share on other sites

• 1 month later...

Hi Jim,

I was pointed to this thread by the recent post on auto entry calcs. I wanted thank you for sharing the above but more than that I wanted to thank you for your statement

Obviously the Case statements can be made more efficient since the same calculations are being repeated but with my limited memory I like the statements to make obvious sense.

Boy was I glad to read that! I thought it was only me!

I am constantly amazed (not to say humbled) by the breadth of knowledge and laser like logic of lots of the people in here. I cannot hope to work to that standard. I am just too old!

Whilst that may not be the case for yourself, It was none the less refreshing to find someone else who is happy to take the longer way round with something simply 'because it suits them'

Breath of fresh air mate!

Regards

Phil

Share on other sites

Phil

Are you suggesting I don't have breadth of knowledge and laser like logic?

OK, you're right.....

I have the logic (University mathematics teacher in a former life) more like an old torch now though, little knowledge and failing memory. Growing old is great though - everybody thinks you're going gaga and they forgive you all your "mistakes"; my children no longer expect me to refurbish their properties because I am "past it"; I have time to make a monkey of myself on various Forums. But I always remember the words from a Fats Waller CD (can't remember the title of the song!)

"Don't give your right name yeah!"

Share on other sites

Jim,

Having followed, and benefited from, lots of your threads,I have never been less than impressed by your contributions.

With regard to the age related comments

Regards

Phil