© 2019 by Kirkness Associate Ltd

Ask The Experts (and each other)

May 20

Products, Order line items, Multiple pricing points


So I'm trying to figure out the best structure for this scenario:


We have products, which we manufacture and sell.


We sell these products at multiple price points (distributor, wholesale, retail, and custom price points for certain customers).


I'm trying to figure out how I could have multiple price sheets, linked somehow with products.


Realistically, we can get away with price sheets being a separate table and not linked in with anything, but I feel like that makes adding it to the DB redundant.


If it could be linked, the idea would be that when quoting or "invoicing"* we could select the price sheet which would then populate the cost based on the product we select.


I thought I almost figured it out but then it fizzled into a pile of 'not going to work'.



*we do our actual invoicing in an accounting software, but setting up pseudo-invoicing would be beneficial as it could act as a packing slip as well as an invoice displaying our product by the pack or carton instead of individual unit.

Hi Sarah


I'm glad that you're enjoying Ninox - I'm a great fan! And I saw that Sakshi from Ninox has responded to you - I would take her up on her offer - and maybe start to attend some of their webinars.


Re your data structure, I think I would probably do this with a table linked to Products and a Price List table (say called the third table Product Prices) so that you could hold multiple prices (by type - e.g. retail, wholesale etc) for each product. You could even set up a price list for particular customers. From a UI point of view, you could either enter prices via a Price List page or via the Product page (or both).


Then, when selecting a product for an Order Line, you could use the Ninox Language to look up the correct value for that type of customer (you could hold which price list to use for the customer on their record).


Hope this helps.

May 21Edited: May 21

Hi Julian


That's what I had a vague idea of in my head. Good to know I'm on the right track.


I'll update this thread with my results to benefit anyone else wanting to do the same.


I am having to check if all of what we want to do can be done with netsuite though, as we are hoping to have it implemented to allow for an account system that can be used globally.


edited to amend

Looking at netsuite, it may suite our needs, but there's a chance it wont be approved so going forward I'm going to go ahead with Ninox as both an on-boarding process and contingency plan in case net suite doesn't happen for a while.

New Posts
  • With the addition of the latest Process Manager app, I now have 3 free Ninox applications available to download and use from our sister site, Online Database Solutions : KasPer Pro - a Human Resource Management application covering People Management, Absence and Vacation, Training, Appraisals, Benefits, Onboarding and other Checklists, Qualifications and Skills, etc. KasCur Pro - this is a CRM application covering Leads, Opportunities, Sales Forecasting, Support Management and includes Activity management with personal task lists and Kanbans. KAS Process Manager - with this latest app, you can manage and standardise all the processes and projects in your business. Create templates for each process from which you can create live processes and monitor the progress and performance of your team against deadlines. All of these apps are free to use with no restrictions on the number of records or users. You will need a Ninox licence to run them. Each app is supplied 'locked' but an upgrade licence is available for each which will allow you to customise the app using Ninox's low code tools and also enable automated email alerting features which are disabled in the free version. See Online Database Solutions for more details.
  • Objective: Orders will automatically update stock levels accordingly. Stock levels do not require an order to change (eg, withdrawing stock for internal use, or adding stock when new stock arrives at a warehouse) ad so manual entry needs to be possible to update stock levels Products can exist without stock (stock levels) Stock movement can exist without orders (manual adjustments) but cannot exist without Products One stock movement record can only be linked to 1 order, and 1 product A graph view is required to show all products current stock QTY (thinking a graphic view in products, with a formula field that calls on info from stock movement to show the current stock levels - not sure how to calculate this though as it would need to sum all records for that SKU, and those records could be + or - ) There are 2 types of product: bags and resin. Resin is sold by the kg, and each resin product is made up of 25kg bags. Bags are sold by the bag, pack, or carton - the number of bags per pack/carton varies by product. So! Questions: Is this my best data model to achieve what I want? If so, how can I calculate current stock based on all incoming/outgoing stock movement per SKU? Keeping in mind that once this is done I want to try and create a pricing and inventory table where there are various price variations per product (retail, wholesale, distributor, custom pricing for certain customers) which orders would call on (based on select fields for pricing type and the product) and I will also be working on a quoting tool that will call on products and pricing unless its creating a new price sheet which would be determined by a select field
  • Ninoxus today launched new Global Functions for Ninox which are free to copy and load into your own databases - the initial release includes various conversions and a new Age function which calculates age in years and months. You can access these at https://www.ninoxus.com/login/post/275