Conditional formula to calculate 'taxable' and 'non taxable' items...

Hi Taylor

Reading your post and seeing red flags like 15 lines, and Type 2, I wonder if your setup is correct and that is why you are now struggling. Can you upload a copy of your database for us to have a look at.

Thanks John

Based on the image you have, this may work better as a sub-table, with the fields of "Type", "Job Item", "Quote", "Tax", "Total+Tax".

You can then have the view show this same data, and use the built in sum function to display these values, or use a formula sum(jobs[tax=true].'total+tax') and sum(jobs[tax!=true].quote)

Hi Taylor,

To get it from that record you don't need the select statement, you can just do the formula

sum('project details'[tax = false].'job status log')

Your formula is summing ALL of the records in project details, rather than going through the reference table for your singular record

hope this helps

Taylor

Ah ha same industry as me !!!

Yep all my quotes use a master header form main quote detail and subform(child) for the quote lines etc.

See my thread here on how to deal with the auto Quote or Job number

https://forum.ninox.com/t/q6yqpnv/automatic-incremental-numbers-modifiable.

Taylor Hanson said:
In this case all the of the toggles are turned off, so the items in that column should total $608. For some reason (sorry, I know the red wrench is covering it) the total says $1518. 
Any ideas? Here's the formula I have: sum('Project Details'[Tax = false].'Job Status Log')

 You use ‘Job Status Log’ in your code but the picture shows the column name of Total. If they are not the same field then you need to change the name of field in the code to match the column that shows up in your view.

@Taylor

NO Buddy  - Not so grand as a designer. but do I own a print and branding company.

Just for information... My approach to this is ever so slightly different insofar an I don't use a yes/no switch for vat I simply enter the vat rate itself. (I did have a complicated version for pulling the stock item with vat code lookup on but this was over complicated for what I needed) Although I still have the test database for anyone that is interested.

Anyway. I use formulas within the line to either calc the vat value of set it to zero. then at the foot use a simple formula to total all the vat column.

Test Sample with 20% VAT  

Same sample with Zero Vat

Main form showing vat total at foot

As you are aware in our industry, you often have to enter multiple product lines for the same item but only differing by thy qty/price

so i use a set of data entry fields on the main quote form, that whne you press the enter button, writes the info directly into the quote child line. Then on the data entry fields I can very quickly change the qty/price and write the line again without having to type all the description again.

I can then clear the data to enter a different product etc

I also use a line numbering system to index the child lines, so that if i have to add in a line right in the middle of the quote I can do so or shuffle the lines around.

if of interest I can add here or start a new post....

Taylor Hanson said:
- Create some kind of price table in Ninox. I don’t bill per hour, instead per job. So I’m imagining a list of jobs varying in price/mapping to the client type, along with a field for markups or discounts, etc.

 This is a great idea. Somethings to think about.

Does each invoice/job for a client only have 1 item/task associated with? Or can you have multiple items/tasks to each invoice/job?

You will need to get comfortable with formulas in Triggers/buttons so you can copy the data from the price table to your invoice. You want to copy data because if you only link data then any changes to the price table will populate to all linked records, even historical. That would be bad news. Plus you can then manipulate any of the data to make it specific for that invoice/job.

Taylor Hanson

I am Lichfield in the UK.

We used to have price tables with set price scales per product per customer etc but now as I changed the model as to how we work- we price products on the fly using a fixed price list and vary the margin depending upon workload.

That said I do have model db still with a product table that might be off some use. However I am away on holiday (vacation) at the moment and don’t have access to it to upload just now.