13 Replies Latest reply on Jun 13, 2017 4:07 PM by micky888

# Multiple prices each product

Part#1

now i have these following tables:

•Product

•Price

•Customer

•Sale

•Sale line item

*i'm not sure that all tables i have are all necessary or not

-each product has many unit of measure (UOM).

-each UOM has its own price

-i would like to have many prices on each UOM (retail, wholesale,...)

For example

•ProductA

Unit: piece

-----------------------

UOM: 1piece = 1piece

retail price: \$10

whole sale: \$8

special price: \$6

-----------------------

UOM: 1pack = 6pieces

retail price: \$50

whole sale: \$48

special price: \$46

-----------------------

UOM: 1dozen = 12pieces

retail price: \$90

whole sale: \$88

special price: \$86

-----------------------

Part#2

I would like to assign types of price to each customer

I tried many ways to relate my tables together but i found nothing work

Any helps or suggestions would be appropriate

Thanks

• ###### 1. Re: Multiple prices each product

You need not have UOM differences specify different prices, though you can if you want to. You can specify a unit prices and then also specify a calculation that converts a single price into different unit prices depending on the UOM specified by the user when filling out the order. This would greatly reduce the number of prices that you have to maintain and update for a given product. That might, in fact, simply limit your prices for a given product to two:

One retail and one wholesale price.

Some basic design work: Most sales or ordering systems start from this basic set of tables and relationships. Your names may differ but the basic structure is nearly always the same:

Customers----<Invoices----<LineItems (sometimes called invoice data)>-----Products

---< is my notation for one to many with an auto-entered serial or UUID on the "one" side of each such relationship.

Do you have such a basic structure in place?

From there, you can put a field in Customers to designate a customer as either "retail" or "wholesale" that can then be auto-entered into a field in Invoices each time you select a customer for an invoices. (and which you can then change if exceptions need to be made in specific cases). Most of the heavy lifting would be in LineItems where you'd select a product, select a UOM, and enter a Qty. Auto-enter calculations can then use the Wholesale/Retail field in Invoices, the Qty and UOM in LineItems and the wholesale and retail base unit prices in Products to calculate the line item cost.

1 of 1 people found this helpful
• ###### 2. Re: Multiple prices each product

Keep in mind that prices (should) fluctuate, and maintaining 9 different prices per product can be a nightmare, depending on how many products you have. Did you think about implementing a per-client discount ?

And if I buy 19 ProductA, will you bill me

- 19 X piece, or

- 3 packs and 1 piece, or

- one dozen, one pack and one piece ?

1 of 1 people found this helpful
• ###### 3. Re: Multiple prices each product

I have one solution that is both. Discount on product and per customer. This can happen in eCommerce (web) solutions as well.

Sent from miPhone

• ###### 4. Re: Multiple prices each product

I have no doubt about that, Bev

My question was whether it can be simplified or not - it's something I ask all my clients that bring a dream on the table and utter "implement it" to me.

Usually people want this and that, to which I simply say "don't forget that for each piece of information you want to see, somebody's going to have to input it, is it really an important piece of info ? Is it easy to have ? Is it really worth it ?"

Recent example: client wants to see the reason why a patient didn't show up.

I told him:

- In your agenda you have right now one million appointments (it's a clinic with many doctors).

- I will have to create another table to put failed appointments data into (create an extra field in the app table is nonsense)

- Your desk people will not know why the patient did not show up until it does not show up. They have to call and ask, then enter that data.

- You will be using that data only once, it's not relevant for the future (we already show how many failed appointments a patient has)

etc etc.

Client ended by saying, "ok, you're right, drop it".

1 of 1 people found this helpful
• ###### 5. Re: Multiple prices each product

Assuming that you have a table for appointments:

Why is it nonsense to add a note field for failed appointment?

Doesn't the table include other info about the appointment?

• ###### 6. Re: Multiple prices each product

One idea might be to use mutiple linking fields for your relationship.

Product ID

UOM

In both tables.

Now you can create relationships based on the uom and that uom can be whatever is needed for the product id.

Instead of linking just by product idea, try a test TO creating the links with the two fields.

• ###### 7. Re: Multiple prices each product

it's nonsense because when you have 0.001% of records with an info in a field, while the rest have no info, and the table has 1 mil records, you don't add a field, you add a separate table with app UUID and Note. I call it hygiene.

The app table has a numeric field 0/1 , App_Missed

• ###### 8. Re: Multiple prices each product

Consider the following calculation set up as an auto-enter calculation in the line items table:

Let ( UnitCost = If ( Invoice::Type = "Wholesale" ; Products::UnitWholesale ; Products::UnitRetail ) ;

Case ( Unit = "LB" ; UnitCost ;

Unit = "oz" ; UnitCost/16 ;

Unit = "Ton" ; UnitCost * 2000 )

) // Let

This handles all issues in the original post with only two unit price fields in the Products table, one for WholeSale, one for Retail.

1 of 1 people found this helpful
• ###### 9. Re: Multiple prices each product

or "I can do that, time is money, you know..."

Beverly

• ###### 10. Re: Multiple prices each product

that's sound good thank you for that.

actually I need it to be like this (maximum 5 prices fixed)

*from the top part of my photo (product data entry), I would like to be able to add as many as sale unit as i want by setting them up (the bottom of my photo)

*but i dont really know how join relationship between my product table and my unit table, i tried many ways but it still wont work

• ###### 11. Re: Multiple prices each product

Careful, you need one set up for calculating line item costs for an invoice and a different one for managing units of measure possible for a given product. I would not put unit prices for each product's UOM into a table unless absolutely necessary. If there are no price discounts for certain UOMs or price discounts can be defined via a formula, the method that I outline can be modified to use a UOM table.

Let ( UnitCost = If ( Invoice::Type = "Wholesale" ; Products::UnitWholesale ; Products::UnitRetail ) ;

UnitCost * UOMTable::ConversionFactor )

ConversionFactor then stores values such as:

unit      Factor

Lbs         1

oz           0.0625

Ton         2000

The relationships for line item cost calclations would be:

LineItems::UOM = UOMTable::UOM

The relationships for Products would be more complex as you have a many to many relationship. Each product can have many UOM's and each UOM can be used for many products.

UOMTable----<UOMProduct>----Products

UOMTable::UOM = UOMProduct::UOM

Products::__pkProductID = UOMProduct::_fkProductID

To get and manage a list of UOM's for a given product, you'd put a portal to UOMProduct on your Products layout. UOMProduct::UOM can be formatted with a value list of UOM's so that you can select a given unit of measure for a given product.

Note however, that if you choose not to use UOM's to calculate unit prices, you can add a field to UOMProduct for that price and then you can use this relationship to get the UOM based price from UOMPRoduct instead of the Products table:

LineItems::UOM = UOMPRoduct::UOM AND
LineItems::_fkProductID = UOMProduct::_fkProductID

The main reason that I am recommending that you not put individual prices in for each UOM, is that this represents a log of data entry labor with an increased chance of data entry errors creeping into your solution.

1 of 1 people found this helpful
• ###### 12. Re: Multiple prices each product

Since you are using barcode, consider the 3D barcode which allows up to 4000 or 8000 characters. Read this and get a lot of text in return. The container for a barcode will return the info in the barcode for you.

Insert this barcode into a container field and use as a calculated text field

GetContainerAttribute ( Barcode::Barcode Container; "barcodetext")

Or use an iPhone app that reads barcodes such as QR Reader or CNS Barcode (great for FileMaker)

1 of 1 people found this helpful
• ###### 13. Re: Multiple prices each product

Thank you for recommendations and lead me to the new way which seems better. Sometimes I forget to think about the consequences