Very simple lookup problem

[CHD]

Hi, i have a very basic question, it is so basic that no one bothers disscussing it here!

I am very new to DB creation, i am trying to create a DB for a community phone directory.

Basically what i need to know is: How do I make a price lookup? I have a pop up list of different ad sizes in the "Ad size" field and would like it to automatically insert the price of that size into the "Price" field. I assume i have to create a separate DB or table but then how do i define the relationship to the price field?

Also, an unrelated question, we have been using Quickbooks until now, is there any way to import the previous data. and if yes, can i combine it to update the balance field (if there is an outstanding balance in the old records)?

Thanks for any help, and sorry for asking such basic questions, but you gotta start somewhere!

[Himitsu]

ok... let's work this one out. First off, you have to think about what eryour DB is going to do. Is it disigned around the customer, inventory, serial numbers, invoicing,ect... your discription is a little vague.... I am trying to guess that you are using it for customers who buy ad spacing and need to keep track of their account and give them invoices?

Let's start there.

[CHD]

Yes, sorry about that.

We sell ad space in the directory, kinda like the yellow pages.

I would like to keep track of the prices for the ad plus any additonal charges or discounts in order to create an invoice (and just to simplify the process)

Lets say the price for a qtr page ad is $100, how do i make the price show up in the "Price" feild as soon as i select "Quarter Page" from the pop up menu in the "size" field.

Once i get that - i will have to work out how to update that "price" field or "Total" field to reflect any addtional charges or discounts. But we'll get to that later!

Thank you so much for your help, i think i will get the hang of it soon!!

[barbaras]

Hi,

Is it what you want ?

Ad_Price.fp7.zip

[Himitsu]

I think he is looking for more of selling type design.

CHD, what you will have to do then, is make four tables. One is for the customers, one is for the products, another for the sales, and the last for, what we call, lineitems. Now you can kind of see what fields you will need for each of these table.

Customer:

Customer ID

Cust_name

address

phone_number

ect...

And you can kind of guess what you need for the products page.

Products:

Product ID

prod_name

prod_description

prod_price

Now the sales table, you need to sell the product/service to the customer so you need to design it around that.

Sales:

Sales ID (or invoice ID)

invoice date

customer ID

customer name

inv_subtotal

tax

discounts

inv_total

paid_amount

balance due

paid_date

Now the lineitems table can get a little tricky. I think of it as have almost the same fields as the products and sales table combined. What this table does is make a record of that sale, and all the conditions at that time of sale. It will make sense when you start working on it.

Get those made, then we will go through how each field should be set. To start, you can see that any ID fields should be set as an auto enter serial number in the option area and you can just keep that to a text type.

[CHD]

Why cant i combine the customer table and the sales table in one?

Attached is a creen shot of what i am playing with now, this is the general idea of what i am trying to do.

I also have a separate table with the rates - how would i set that up? and how do i link that with this table?

What barbaras posted is what i am looking for, except i want them to be calculations to keep track of sales, invoics etc. like i said above.

Thanks again

[Ender]

Why cant i combine the customer table and the sales table in one?

Well, is it possible for a Customer to have more than one Invoice? And is it possible for an Invoice to have more than one item on it (Ads)? This is typically the case, which is why you probably should have a related table for Invoices and a related table off that for Invoice Line Items.

[Himitsu]

Ender said it. CHD, you can, and it may do fine now for what you need. However, when you business grows and the same customer comes back and wants something else, and you have to make him an invoice, then you have a whole other can of worms on your hand. You will want to lookup the customers info, so you will have to make a new relation to itself so you can pull in the customers info, and then, you might want to show all the items that customer has purchased over time. With, what you think is easy, would start to become a big pain in the A#$... eventually, you would have to design it again from the ground up, import all the records (which in that case, would just be a hugh nightmare). The system I will help you build, will keep track of your customers, there sales and each item they have bought, in one circle relationship. It looks tough with all those fields I asked you to make, but as you do it, you will see it is actually not bad at all. And I think it is probably the clearest way to understand relationships. Filemaker has a lot of power behind it, and as you learn, you will be able to add so many cool features by getting into scripts. But if your DB design is made from throwing everything into the blender, you will spend most of your time wondering why it is a mess and how to get that mess to work the way you want it to.

Just add those fields, honestly, shouldn't take more than 30 min. Most could add those in about 5 min. I know what you are thinking, you like the look of the template. Don't worry about the GUI (graphic user interface) now, that can be done anytime later. Infact, the one you have, looks like it is based around the customer. So you could use start with that file and add those other tables I said above.

[Himitsu]

I put a little thinking in the pic you should me. It is the same thing, but what you will do, is, through portals, show the information at the bottom. you still need to keep track of what items that company/customer has purchased and payments they have made. If you wanted the invoices to compound, and have the customers make payments on the total balances of all invoices, then you can see you will need another table that will keep track of payments for combined invoices. Again, it doesn't matter what you call it or what it does. You have to ask yourself, what is the center of that table. Then, what relationship does that table have to the others!

[CHD]

Well, i made the 3 tables, i substitued some fields for ones i need, so it is not exactly as you specified, but i think i got the point!

Also, i made a relationship between the sales table and the rate table, and was able to copy Barbaras' pricing effect, is that what we will be doing?

Can the old table and GUI (the one i posted earlier be used as the lineitems table? (still not sure what that is yet).

What would the next step be?

[Himitsu]

There shouldn't be a relationship between the sales table and the rate table (products table) other than to use it for a kind of popup box. We will get to that later. But I think it would work better if you had four tables. Customer, Sales, products, and lineitems.

I would actually use the picture you posted for the customers table. The lineitem one you don't hardly ever look at. It just stores all the data of that sale into one record.

Remember, the lineitems table needs to have the fields the sales table has, but this is where you will make the fields for product ID, description, qty, unit price, total price. The sales table doesn'T need those fields. You will just use a portal connecting the sales table to the lineitems table through the relationship of invoice ID.

Now, first you have to see the relationship, what does these tables have in common to each other? That is where the relationship is. So, the customers should have an ID and the sales table should have it's own field, customer_id, so you want to go the relationship tab and and find the customer table, click on the field customer_id and drag it over to the sales table's customer_id field. What that does is link that sale to that customer. Then you want to see that the sales invoice ID is related to the lineitems invoice ID so you want to connect that one as well. But now, when you see the box with the equal sign in it, you want to double click on the one that connected the sales table to the lineitems table. On the bottom, left and right there are some check boxes. What you want to do is click on the box that says allow creation of record through this relationship. Both sides have it. You want to check the one on the lineitem side. Now, can you see what that does?

As for the fields, we should start setting up what options and things they need to function. As you probably have done, most of the ID fields in the tables, except the one in the lineitems, need to have the radio button selected for the serial number in the options and the autoenter area. The customer name field, you could set the validation to be unique. That will stop somebody from entering the same customer twice. On the Customer table, you might have made a field "date_created" and that can be autoenter date created. Make sure that field is a date field and not a text field.

And the products page, which you can call it rate table, but it does the products you are selling, is very similar to the customer table. ID can have an autoenter, but normal it doesn't because you usually have some kind of product ID specific. So that can just remain a text field.

Now the sales field, again, the invoice ID should be an autoenter serial number. The customer ID can just be a text nothing special. But the customer name, will be a look up, as with any fields you want to add about the customer. Some people like to have the address, phone number, ect. of that customer on the invoice. If you have any of those, they will be a look up too. Just click on the autoenter tab and at the bottom, there is a lookup area. Click on that. Then on the pull down menu at the top, there are two, the top is where you start and the second one is where to lookup, find the cutomers table there. THen all the fields will show up. Click on the matching field name you want to lookup. So customer name field on the sales table should equal the customer name field on the customer table.

[Himitsu]

Here is what you should have come up with by now. Take a look at some of the field options, the relationships ect... is it what you have?

CHD_ad.zip

[Lee Smith]

CHD

Please do [color:"red"]not change the Subject. It makes it hard to follow the thread when reading the Forum via the "All Posts" method.

It is not unusual for a Thread to change directions more than once during the process of working through the problem. If you want to show a thought such as "OK - Got that done!", just make it your first sentence in your next post, or use the Instant Gremlins, Instant UBB Code, or the Font color available to make it stand out.

If you think your Thread has changes so much that it is deserving of a Subject, consider making a New Thread in a Topic Area matching the sprit of your question or problem.

Thank in advance you for your cooperation.

Lee

[Himitsu]

ok, here is some more steps I added. Take a look around and see the difference from the first to the second then to the third.

CHD_ad02_03.zip

[Himitsu]

ok, here is the fourth. Remember, this is just showing you how set the feilds and relationships. The flashy parts are the GUI. The pic you showed can be used. But I suggest getting the hang of understanding the reasoning behind how this works. Once you understand, you can use that flashy file to create the same thing just by changing some names, doing some lookups and valuelists. Let me know how your going.

CHD_ad04.zip

[CHD]

Sorry Lee, i didnt realize it would effect your browsing.