Price books and ninox

if you have the following number fields:

price

discount <-- data is stored as decimal, so you would type .1 instead of 10

then a formula field called 'final price', or whatever you want to call it, would be created that has the following formula:

if discount != null then

price * discount

else

price

end

Very simple as we don't know much about your structure.

Hi Danny

I too have a FileMaker background. Where you can be quite specific about the nature of a join in FileMaker this is not so in Ninox, but then Ninox is much better at extracting records and tables that do not have any joins

So I would treat your PriceBook as a join table in a many-to-many relationship between Customers and Articles . The PriceBook also holds the Price. Create the Customer table first, then the Artice table and finally the PriceBook table. While you are creating this add the two relationships to Customers and Articles. This way you get the 1:Many relationship the right way round.

Keep this arrangement seperate to your Invoicing hierarchy: Customr -> Invoice -> Invoice Line. At the Invoice Line level you can use a select statement to find the price for that Customer and Article in the PriceBook and update the Price. In FileMaker you might have used a Table Occurence to get your price but this won't work in Ninox, unless I am mistaken.

Regards

John

So let us say you have the following tables:

Customer

Article

Pricebook reference field to Customer and Article

Sales Order reference field to Article and Customer

You want to create a new Sales Order for a customer so you create a new record, then you select a Customer, then you select an Article. If a customer can have multiple Articles then you will need another table (i.e. Sales Order Items) to store all those relationships.

Then you would create the following formula fields in your Sales Order table:

Article Price : Article.Price <--where you pull the price of the article through the Pricebook table

Discount : would be a if statement that looks in the Pricebook table for any records matching on Customer and if it is null you put 0 or else it would be Pricebook.Discount.

Final Price : 'Article Price' - ('Article Price'*Discount) <--here you take the two fields you created in Sales Order and do the math to find the final.

Again, this is a very simple description but should point you in the right direction.