0

How can i get totals from a job type?

I have 3 job types

Wedding Setup

Family Portrait

Wedding Officiant

They are all in the customer info under Job Type.

 

How can i get a total for each job type for month and for year?

Also a total of all for the year.

But also deduct out the Permit fee so each total dose not include the permit fee.

Plus show a total for month and year for the permit fees.

All to show up in a Dashboard.

I started a dashboard and have it showing things but not what i need.

It is showing a full list of everything but not by job type with a total for each one.

I do not need to see the full list of items just need to see the total for each one by month and year.

I looked this up and could not find anything on how to do something like this? It so easy in Apple Numbers or Excell.

I tryed to make a drag in formula but dont know how to break it down to the different job types and then then permit fees all with totals.

Also i can not get the Total to show a sum at the bottom i am thinking it is becouse it is set to a Text field not a number field as the number fields do let me sum at the bottom.

22 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Last question first, yes you can not sum a text field. You will have to create a new formula field that converts Total: to a number.

    number(fieldname)

    Remember you have to strip off the $ like we discussed in another post.

    Once you do that you can add that field to the view above and then sum the column.

    One way to show totals by Job Type is to group the column job type and it will show you sums by groups.

      • Edward.1
      • 1 yr ago
      • Reported - view

      Fred How do i group a column to see the totals for each job type?. But also i need to minus out the permit fees other wise i am not seeing my real number.

      • Edward.1
      • 1 yr ago
      • Reported - view

      Edward Ok got it i found grouped. Now to minus out the fees?
       

      • Edward.1
      • 1 yr ago
      • Reported - view

      Edward Got the fees taken out now..

       

      Now dont know how to break it down into month and year?

      And it is strectching out long from right to left i can not make the browser window smaller as it gets cut off.

      Any way to make it auto resize to fit? This all could be in a small space not so sctrtched out?

      Thanks

      • Edward.1
      • 1 yr ago
      • Reported - view

      Edward I got it to this now and showing months in a area in teh center.

      Any way to get the bottom bar chart to show the Totals $

      I still han not fix the way the tables are spred out if i make the browers even a little smaller it starts cutting parts out as it is not resizing down with it.

    • Lars
    • 1 yr ago
    • Reported - view

    Edward, first of all, you should convert the total to a number. It doesn't make sense to keep numbers as text. If you like the $ sign, just add it in the formatting menu of the column. Other things will also come easier.

      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars How do i add $ to the formatting menu of the column i opened that up and put that in but did not work.?

      • Lars
      • 1 yr ago
      • Reported - view

      Edward There's an option in the field configuration menu - I was wrong about the menu of the column. In the field configuration menu there's the "Number format" and by using e.g. the prefix "$", you can add that to the number without altering the value itself (i.e. it will still be a number, but when being displayed, the $ is added).

      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars Hi i do not get that field config maybe as it is a view.

      Here is what i get for settings.

      • Lars
      • 1 yr ago
      • Reported - view

      Edward you're right, if it is not a real field of a table, you can't format the number by menu. Instead use the format function:

      format('Total:' - 'Permit Fee:' - 'Help Payout:', "$ #.00")
      
      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars Ok i put that in and i get the red letters so i can not save it.
      Is this the right place to put it?

      • Lars
      • 1 yr ago
      • Reported - view

      Edward I think you're in the wrong place to change the line. Replace your Formula (second field) where you now calculate the result. This gives you the $ in the display and still keeps real numbers in the database:

      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars No that did not work still get the red and can not save.

      • Lars
      • 1 yr ago
      • Reported - view

      Edward you forgot the "format"-command. 

      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars I do not know what a format command is? This is my first DB using ninox.

      • Lars
      • 1 yr ago
      • Reported - view

      Edward Look at your last image. What you entered is

      ('Total:' - 'Permit Fee:' - 'Help Payout:', "$ #.00")
      

      What you should enter is

      format('Total:' - 'Permit Fee:' - 'Help Payout:', "$ #.00")
      

      Your code is missing the word "format" at the beginning

      • Edward.1
      • 1 yr ago
      • Reported - view

      Lars Ok thanks but that still did not work.

      It made it no longer work to add up.
       

       

    • Edward.1
    • 1 yr ago
    • Reported - view

    Ok i fixed the Title it is now changed to a number. I am still working in the test site.

    The newer site version that was all ready fixed so when i import in i do get numbers.

     

    So the total is working now at the bottom just with no $ in front of it

    • Edward.1
    • 1 yr ago
    • Reported - view

    Another ?
    I had watched this video
    https://www.youtube.com/watch?v=nUqGl84gh08&t=1334s

    But i do not see how he got the search area up top on dashboard with search by date range, customer and so on?

    • Fred
    • 1 yr ago
    • Reported - view

    Just food for thought.

    Why are you keeping job data like total, down payment, job start time, etc. in the customer table?

    I would think that information should be in your Payment table. I say this because your current setup will force you to create a duplicate customer if they come back for another job.

    Just something to keep in mind.

      • Edward.1
      • 1 yr ago
      • Reported - view

      Fred Not sure as i never did this before.

      but the way the customer info comes in will be from a new csv file each time so i was thinking it needs to match up to the fields as it comes in?.

      i am not going to enter in any of it manual. Its all in the CSV that is created for each customer from our website we get sent.

      My plan was to make a new version for each year to keep everythign for each year for all the numbers and totals.

      Most of this is weddings so repeat customers and no very much and if so many years apart.

      Now the family portraits - we do have a hand full that come and hire us every year or every other year they come here on vacation.

      • Fred
      • 1 yr ago
      • Reported - view

      Just thinking out loud about some limitations.

      If you decide to do things differently then we are here to help.

Content aside

  • 1 yr agoLast active
  • 22Replies
  • 106Views
  • 3 Following