1

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

Hello,

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.

Any ideas?

Thank you!

20 replies

null
    • John_Halls
    • 7 mths ago
    • Reported - view

    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

      • Taylor_Hanson
      • 7 mths ago
      • Reported - view

       yeah it's quite clear I have no idea what I'm doing, and am learning as I go. I'm not surprised to hear my post is full of red flags, lol.

      Ultimately the goal is to have Zapier map entries to Quickbooks to make bookkeeping easier, which needs to map entry to entry as I understand it (and I haven't figured out yet how to include multiple lines per job, which is common in my case). Originally I had x1 area for the job description and quote, but switched it to individual cells to try and help map things...

      It would be ideal if there's a way to include x1 line with those entries (type, job details, quote), then have it auto populate additional lines as needed (without having to setup x14 more 'turned off' lines waiting in the wings, if that makes sense). 

      And, per this post, ideally I'd love to see the total invoice amount at the bottom of the section, broken into non-tax (design items) and taxable items (print, shipping, and the like). This table has hundreds of jobs so I'd really like to avoid re-doing the whole thing, if possible.

      Here's a copy of my table with x1 job showing what I'm working with, hopefully that helps.

      Thank you!

      • John_Halls
      • 7 mths ago
      • Reported - view

       Hi Taylor. We were all there once and you are doing the best thing of all, having a go at building something. You will find a wealth of support here. Daniel has it right, add a second table to cover any line items. Regards John

    • Daniel_Berrow
    • 7 mths ago
    • Reported - view

    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)

      • Taylor_Hanson
      • 7 mths ago
      • Reported - view

      Thank you Daniel, I'll look into this.

    • Taylor_Hanson
    • 7 mths ago
    • Reported - view

    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... 

    • Daniel_Berrow
    • 7 mths ago
    • Reported - view

    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_Hanson
      • 7 mths ago
      • Reported - view

       Thank you! Something changed, I'm not sure what yet, but it's still not quite adding the columns correctly. I've attached a new screen shot showing one of my entries in my 'Job Status Log' table. What I'd like to do is add all the numbers in the 'Quote' column as long as the 'Tax' toggle (in the column next to it) is turned off.

      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')

       

      Thanks!

    • Mel_Charles
    • 7 mths ago
    • Reported - view

    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
      • 7 mths ago
      • Reported - view

       Hi fellow graphic designer! Thanks for your reply. I put in your script (copied below with my table name 'Job Status Log' and number field 'Job Number'):

      let before := max((select 'Job Status Log').'Job Number');
      'Job Number' := before + 1

      However, I'm still getting the same issue. Right now my current job number is 1224, and when I add a new job it becomes 12241, instead of 1225. Any ideas?

      • Fred
      • 7 mths ago
      • Reported - view
       said:
      However, I'm still getting the same issue. Right now my current job number is 1224, and when I add a new job it becomes 12241, instead of 1225. Any ideas?

       Is 'Job Number' a text field? That is what happens if it is.

      To fix it try:

      let before := max((select 'Job Status Log').number('Job Number'));
      'Job Number' := before + 1
      • Taylor_Hanson
      • 7 mths ago
      • Reported - view

       This is exactly it, it worked! Job numbers fixed. Thank you!

    • Fred
    • 7 mths ago
    • Reported - view
     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_Hanson
      • 7 mths ago
      • Reported - view

       This is it exactly! Thank you so very much!

    • Mel_Charles
    • 7 mths ago
    • Reported - 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
      • 7 mths ago
      • Reported - view

       thank you for the insight, this is super interesting.

      As you can tell I’m fairly new in the Ninox world. I’ve been using it for a couple years at a very basic non-formula level, but in an attempt to automate things and streamline I’ve been digging more into what it can do.

      Next, I want to do a couple things:

      - Automate my estimates to Quick Books online using Zapier to make the invoicing process quicker 

      - 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.

      - Then ideally I could tie it to this main table and auto populate prices based on the job I plug in…

      I’d love to see a test file of what you’ve got setup if you think it would be helpful? And, always love getting to know other folks in the industry, where are you located? I’m in Washington state.

      Thanks!

    • Fred
    • 7 mths ago
    • Reported - view
     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
      • 7 mths ago
      • Reported - view

       thanks! Most of the jobs will have multiple items associated with them, and each item is usually a different price. I have lots of learning to do but it’s pretty fun so far…

    • Mel_Charles
    • 7 mths ago
    • Reported - view

    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.

      • Taylor_Hanson
      • 7 mths ago
      • Reported - view

       Ah I was as well! Hope you enjoy your holiday. If you get a chance to send it over when you're back in I'd greatly appreciate it. Thanks!

Content aside

  • 1 Likes
  • 7 mths agoLast active
  • 20Replies
  • 217Views
  • 5 Following