0

Auto-populate pricing from another table?

Ok Ninox wizards, now I'm trying to auto-populate some pricing and job details.

Here are the top level details:

  • Main table is 'Job Status Log' which has 100s of job entries that are connected to my...
  • 'Project Details' table, which is where I put in the details of each job (info, pricing, tax calculated, etc)
  • This is not a 'sub' table yet, but it could be if needed (have to figure out how to do that, haha)
  • Screen shot of setup attached

Ideally, I'd love to be able to:

  • Type in the 'Details' text field and have it auto populate details that are sourced from a 'pricing' table
  • For example, if I were to type in 'Brand Gui...' it would populate 'Brand Guide, Per Page' and give me an option to choose from x3 tiers of pricing, then based on the tier I pick it would auto populate the price that again is dictated in the 'pricing' table I've created 
  • The thought behind this is because:
    • Most of my jobs are serviced based, and priced per-job (not hourly) and based on client type.
    • For example, I wouldn't charge a small mom-and-pop type business the same thing that I would charge another, larger client where the design I create would get more visibility and use 
  • Ideally, I'd also like to have the ability to modify the 'details' and 'price' fields depending on if I need to add additional details or a discount/markup

Maybe this is a 'pie in the sky' type thing, but as a whole it would be great to standardize my pricing a bit. Any ideas on how to accomplish or make this easier please let me know. Still fairly basic but learning lots...

Thank you!

14 replies

null
    • Fred
    • 10 mths ago
    • Reported - view

    What you need is a reference field in Project Details that points to Job Pricing. You can call it whatever you want in Project Details, but let us leave it as Job Pricing for now.

    For every new record in Project Detail you will click on Job Pricing and at the top will be a search box and you can start typing in "Brand Gui..." and it will bring up any record with any data with "Brand Gui". Then you can select the record you want. You can modify the columns that are shown in the pop up.

    You can then create three buttons in Project Detail below Job Pricing that say Low, Mid, High and put the following code in each, assuming that Project Detail.Quote is a number field:

    Quote := 'Job Pricing'.Low
    

    Replace "Low" with the appropriate field name for Mid and High.

      • Taylor_Hanson
      • 10 mths ago
      • Reported - view

      Thank you, I'll give that a try!

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       This is getting pretty close, and I'm partially able to make it work.

      See attached 'HGD Test' file.

      In 'Job Status Log', when you click on the first entry called 'Job Name Test' and go to add a new item in 'Project Details' in the 'Estimate' section, it opens the 'Project Details' table.

      There, you'll see a section that is called 'Job Pricing'.

      If you type in that section 'Logo Des' and click on 'Logo Design', you can then select 'Low' 'Mid' or 'High' and it will auto populate the 'Unit Price' field above it.

      However, none of the other 'Job Pricing' items seem to work. Any ideas?

      Also, I'd love to have the Job Pricing item I select (in this case, Logo Design) auto populate in the 'Details' field above it. I will need to be able to add more text to that section though, so if that's not possible then no worries.

      Thank you! 

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    Have a look at a file of an unfinished DB test I was trying to develop some time ago 

    Look at quote lines. This looks up a product from another table and picks up the pricing. I was also experimenting with qty breaks etc and batch updating the prices. In this example there is no main header quote body as I was only concerned with the testing of the quote lines section.

    I went down another direction in the end. So this project was parked and not touched any further! But might/might not be of some use. (even if not how to do it 🤣🤣)

    One of the things I personally don't like, which I have seen various examples on this forum, is putting prices and or tax codes directly into a script embedded within the table form. I see these as quick fixes but when you want to make changes to tax rates your effectively diving back into design and could potentially fall foul to unintentionally altering previos stored records. I prefer separate tables for products / prices and tax codes. 

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       This is excellent Mel, thank you!

      I'm still refining, but I've attached where I'm at currently.

      Thanks to lots of help on these forums I've gotten it to a place where I can populate prices using a reference table, and I can also type within the field if I want the price to be custom.

      Re: sales tax, I've got it setup so the sales tax auto-populates based on the 'job type', and can also be modified if needed.

      I'll dig into the file you've built for any ideas to refine further and streamline, as I'm sure some of what I've got going on in my file is redundant (but hey, that's learning for ya!).

      Thank you for sharing and for your help.

    • Fred
    • 9 mths ago
    • Reported - view
     said:
    However, none of the other 'Job Pricing' items seem to work. Any ideas?

     You originally had the code written as:

    if 'Job Pricing N1' = true then
        'Unit Price' := 'Job Pricing N1'.Low
    end
    

    I guess technically it should work for all records if it worked for the first one. But if you think about it the field is not a yes/no field so I'm not sure what Ninox is evaluating as "true".

    I changed all the buttons to:

    if 'Job Pricing N1' != null then
    

    and that seemed to do it. Now we are telling Ninox to only do the copy when the field is NOT null. Which is what you want, only when there is data in the field do you want to copy the data.

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       that did the trick, thank you!

    • Fred
    • 9 mths ago
    • Reported - view
     said:
    Also, I'd love to have the Job Pricing item I select (in this case, Logo Design) auto populate in the 'Details' field above it. I will need to be able to add more text to that section though, so if that's not possible then no worries.

     Just add it to the buttons:

    if 'Job Pricing N1' != null then
        'Unit Price' := 'Job Pricing N1'.Low;
        Details := 'Job Pricing N1'.Item
    end
    

    Now this will delete whatever is already in Details and put in what is in Item. If you want to add what is in Item to the front of what ever is there you have to add:

    if 'Job Pricing N1' != null then
        'Unit Price' := 'Job Pricing N1'.Low;
        let extra := if Details = null then "" else "  -  " end;
        Details := 'Job Pricing N1'.Item + extra + Details
    end
    

    But this will add it every time you press one of the buttons, so I guess you need to figure out if it works better as a button or Trigger after update for 'Job Pricing N1'. If you do it as a trigger then you can remove the if statement and the Unit Price part.

    So what line 3 does is depending on if there is already data in Details, it will add a dash so you can visually separate the data and if there is isn't data then it doesn't add anything.

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       The second solution works pretty well, thank you! I added it to the 'on trigger' section of that Job Pricing field. 

      Since I am using that function first to find the price, it works because it adds the general term 'Logo Design' to the field, then I can come in and add any additional details I need.

      I'll really only be using that pricing field once per entry, so I shouldn't run into any issues with adding that text over and over. 

      Thank you again!

    • Mel_Charles
    • 9 mths ago
    • Reported - view

     Ah Taylor

    Thank for the sample db. I can  the pricing route you are taking. I guess effectively most of your pricing is service based ! cos its your time as opposed to a product and the low/med/high pricing structure will clearly work for you.

    Just a small comment ( and you may have already dealt with this ) but you have small table error in  project details - The date field - Interim Invoice Date is highlighted to show the error and this is also confirmed as a warning triangle on the table when you go into table edit mode.

     

     

    When you open the field up you can see this.

     

    D1 means you have lost the reference to something ie maybe you deleted the other field etc.

    so your true condition won't get triggered until corrected.

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       I did see that, thank you! Appreciate the flag.

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    So what line 3 does is depending on if there is already data in Details, it will add a dash so you can visually separate the data and if there is isn't data then it doesn't add anything.

    What if you price "low"  and then  changed  your mind?. Would it not be better to have 'Details' field clear when you select low/med or high ?

      • Taylor_Hanson
      • 9 mths ago
      • Reported - view

       I can still go into the pricing field and adjust the price if/when needed.

      The details field will look something like this once fully populated (a mixture of the auto populate function above, plus my details added in):

      • Logo Design: Based on existing brand, draft 3 options to choose from, XYZ parameters, 1 version, print and web-ready deliverables

      Then, in the price field, I'd select 'Low' 'Mid' or 'High' based on the client. Or, if none of those fit, I can put in my own price.

      The benefit to having the pricing table is two fold- it provides a means of standardizing, and is an easy place to reference. What I'm doing now is going through all my old jobs to get an idea of the types of jobs and their various prices so I can fine tune the price points. Unfortunately all those old job details are in basic text blocks, and I'm not sure there's a way to easily extract that data. The jobs look a lot like above, but with pricing right after. Something like this:
       

      • Logo Design: Based on existing brand, draft 3 options to choose from, XYZ parameters, 1 version, print and web-ready deliverables.......$1250

      Lots of work now, but future me will be thankful :)

    • Mel_Charles
    • 9 mths ago
    • Reported - view

    Taylor Hanson What I was referring to is this

    In you project I select a job type and also select low price option. The details field has been filled in.

    I then think - oops that should have been set for Med prices - so i click the med price option.

    now look at details field - its doubling up the text (easy to fix)

     

    Okay re your text blocks where do these exist

    are they in emails, excel, word etc;

    cos you could always pull them into a temp table and link them, or work from there.

Content aside

  • 9 mths agoLast active
  • 14Replies
  • 170Views
  • 3 Following