Conditional formula to calculate 'taxable' and 'non taxable' items...
I'm trying to figure out how to include two fields that calculate the sum of taxable and non-taxable items.
If you look at the attached image, you'll see that there's a field for the base price "Quote", followed by a toggle button "Tax", then a field that is marked "Total + Tax".
The "Quote" field shows the base-level quote. If the item is taxable (for instance, print items) I will select the "Tax" toggle, which then tells the "Total + Tax" field to calculate and display the total with our local sales tax.
What I'd like to do is create a field that adds up the non-taxable items in the current job (i.e. all of the "Quote" fields as long as the "Tax" toggle isn't selected) and another that adds up all of the taxable items (i.e. all of the "Total + Tax" fields as long as the "Tax" toggle is selected).
Here's some top level details:
- Everything is in one table, called "Job Status Log"
- There are 15 lines for entries within each job. Each 'line' includes the same fields as shown in the image- "Type", "Job Item", "Quote", "Tax", and "Total + Tax"
- As you move through each line, the names become "Type 2", "Job Item 2", etc up to 15.
- I'll also be including a field that shows the total invoice, i.e. the sum of the taxable and non-taxable items together.
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.
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)
Well I think I'm getting closer, thank you both for your help.
I've cleaned up my multiple entries (into a referenced table) and things are looking much cleaner.
Here's the formula, and it almost works, but now it's adding ALL the tax=false Quote entries in the Project Details table, not just the ones in the specific job I'm wanting to calculate.
sum((select 'Project Details' where Tax = false).'Job Status Log')
'Job Status Log' is my main table with all of my job info, and 'Project Details' is the table that I built to reference.
In the attached screenshot, the $5370 total is the sum of all 'non-tax' items in my 'Project Details' table. I just need the ones on this individual page. Any ideas?
And, a hopefully easier request- when it comes to job numbers, I'm trying to figure out how to have the job number automatically populate with the next number when I add a new record. I'm currently at 1224, all of which have been manually populated so far. Is that possible?
Thank you in advance, I really appreciate the help! I'm having a blast learning, but some of it feels super complicated...
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
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
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.
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....
- 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.
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.
- 2 mths agoLast active