PRICE LISTS WITH DISCOUNTS.-

For HanDBase related topics that are not specific to one platform.

PRICE LISTS WITH DISCOUNTS.-

Postby fperezlandaeta » Wed Mar 31, 2010 2:23 pm

Hi,
I am trying to setup an "ordering database" that will allow me to do the following :

1.- have a database with a price lists containing different discounts, example

STOCKID BASEPRICE PRICEDISCOUNT25 PRICEDISCOUNT25 PRICEDISCOUNT35
010101 32.95 24 20 15


first of all, type the information of the client by an id, that is no problem
second, generate an order that will look for the pricediscount and whenever i type 25 it will lookup the price for the item---stockid ( in this case 24 ).
third, it will ask me for the quantity and i will type whatever number example, 3.
fourth, it will total the line 3 x 24 = 72

i will be very happy if anyone help me determine how to approach this.

i am using and itouch, and mac osx
I cannot understand how to be able to lookup / the price based upon the discount..

any help is appreciated....

thanks...
fperezlandaeta
 
Posts: 6
Joined: Wed Mar 31, 2010 2:12 pm

Re: PRICE LISTS WITH DISCOUNTS.-

Postby dhaupert » Thu Apr 01, 2010 10:00 am

Hi there,

Assuming you aren't tied to that exact setup, may I suggest something that may be a bit easier to pull off? Here goes:

1. Use only these fields you have existing: STOCKID BASEPRICE
2. In your order database, have fields for stockID, baseprice that are DB Popups pointing to this database. Set the Group # to 1 for both of these fields.
2. Add a popup field named DISCOUNT with the values 0.0,.25, .30, .35, etc (this is the percentage discounts you offer). I'd set the discount default popup value to be 0.0.
3. Add a calculated field called Price and have the calculation be: BASEPRICE - (BASEPRICE*DISCOUNT)

Now when you fill out an order, you select the popup list for the StockID field, and it shows you the list of products. Pick one and the StockID and BASEPRICE get brought back into your order.
Select your discount by choosing from the popup list. The calculation will be done to determine the price for that amount.

Let me know if there is any reason why the above wouldn't work. There are other ways to do what you want using a series of conditional fields, but the above seems to be the most efficient setup.
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: PRICE LISTS WITH DISCOUNTS.-

Postby fperezlandaeta » Thu Apr 01, 2010 11:09 am

Thanks what a prompt reply. I will try this but i will like to be able to be able to use The discount table. I am not sure how to do it using relations (linked, link and The other conditionals) but i look forward to implementing your solution.. In The meanwhile if you can help me with The other setup it will be great.

If possible give me a light to do it the other way.

Thanks

Frank
fperezlandaeta
 
Posts: 6
Joined: Wed Mar 31, 2010 2:12 pm

Re: PRICE LISTS WITH DISCOUNTS.-

Postby dhaupert » Thu Apr 01, 2010 3:01 pm

Hi Frank,

If you'd like to use the table, what you'd need is a group of DB Popups to retrieve all of the prices for an item along with the itemID. Let's name these fields the same names as they are called in the other database, but keep them hidden, and set them all to group #1. Now in your order database, you will still need a popup list for the discount (let's call this DISCOUNT). The list entries can be worded however you like, ie, you don't need to use .35, etc. But they have to be shorter than 8 characters.
For this example, let's name the options: BASE, DISC25 DISC30 DISC35
Then you'd add a series of conditional statements like this:

Conditional field named: Subcalc1
If DISCOUNT == No Field (then write in DISC25 in the field below)
Output is Field PRICEDISCOUNT25
else output is field BASEPRICE

Now the next conditional field will be like this:

Conditional Field named: Subcalc2
If DISCOUNT == No Field (then write in DISC30 in the field below)
Output is Field PRICEDISCOUNT30
else output is field Subcalc1

Next one is the last:
Conditional Field named: Price
If DISCOUNT == No Field (then write in DISC35 in the field below)
Output is Field PRICEDISCOUNT35
else output is field Subcalc2

Once you've tested it all out, you can then hide Subcalc1 and subcalc2 from view.

Hope this makes sense!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: PRICE LISTS WITH DISCOUNTS.-

Postby fperezlandaeta » Mon Apr 05, 2010 8:00 am

Hi Dave,

I studied your solution and tried developing the database. I arrived to the following conclusions :

a) I decided to keep name, last name, client id in one database
b) I have an ORDER database and a order detail database ( i used something called link and linked fields in both databases ) i also, added a field called discount in the ORDER database so that when it takes me to the order detail it uses the discount to perform the calculations in the order detail.
c) I created a master database where i keep the information of stock, stockid and base price.

BUT !!!!, I dont know how to pull the baseprice from the ORDER database, and the DISCOUNT, from the database to perform the calculations in the ORDERDETAIL database.

I simply stuck !!!

I thought of this scenario as the most practical one after reading your replies, however, i still have trouble understanding the link / linked / relationship, relationship and calculated fields.

I am not sure if you can use / and how to use the calculated fields with other databases.

If you can give me another hannd !!! it will be great !!

thanks

i use mac osx 10.4.11
Itouch

thanks !!
fperezlandaeta
 
Posts: 6
Joined: Wed Mar 31, 2010 2:12 pm

Re: PRICE LISTS WITH DISCOUNTS.-

Postby dhaupert » Mon Apr 05, 2010 9:46 am

Can you explain what's in the order detail database? I'm unclear on what you separated into that database. If you'd prefer, you can email me a copy of the databases (my address is listed below) and I can take a look.
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am

Re: PRICE LISTS WITH DISCOUNTS.-

Postby fperezlandaeta » Tue Apr 06, 2010 7:12 am

this is the setup of my database...

i want to be able to pull up the order, be able to see all the items of the order. Have summarized all the articles sold, reflect the amount of discount taken away and show the payment.

the first issue i have is to be able to pull up the PV from the master for calculations, i am not sure how to do it. What do i need to do a relationship, a lookup, a link, a linked field...I am not quite sure i understand these fields...

In the calculate field can you perform calculations from fields of other databases lets say = total = master(pv) x suborder1(quantity) ?
i have not tried it yet.. Do you have to set a field to maintain or copy the value and then perform the calculation ?

i look forward to solving this puzzle !!!

thanks again !

master ( stockid ------ text
description ------- text
PV ---- text

order1 ( order ---- unique
date ---- date
id ---- pop-up
suborder -----link
discount ---- pop-up

suborder1

( suborder --- linked
lineorder --- unique
stockid --- db popup
quantity --- pop up
total pv --- calculated
pv --- float

client

( name --- txt
clientid ---integer
lastname -- txtd )
fperezlandaeta
 
Posts: 6
Joined: Wed Mar 31, 2010 2:12 pm

Re: PRICE LISTS WITH DISCOUNTS.-

Postby dhaupert » Tue Apr 06, 2010 10:43 am

Hi there,

I see you sent me some info via email. I will take a look and respond there. But to answer a few of these questions..

You do indeed need to use two fields for the calculations from other dbs, one for a relationship or DB popup to grab that other value, and one for the calculation itself.

The DB Popup is likely the best candidate for pulling items and the base price. I'll take a look at what you have to be sure and get back to you!
dhaupert
 
Posts: 4111
Joined: Tue May 26, 2009 11:51 am


Return to HanDBase General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests