Newb question

[gmiller1977]

Everyone,

I must admit, I'm new to Filemaker... I've played with some databases in the past, but, I'm starting to get a real appreciation for them and what they can do.

I need some help, and I'd appreicate if people could post some ideas on how to achieve what I want to do

I'm building a point of sale system. I'm not building it for any reason other than I want to see if I can do it, and I've reached my first hurdle

Here is what I want to do:

I have a field called sku1 (part number) and a field named inventoryname1. I want to enter a part number (either typed in, bar coded in, etc) and have the inventoryname1 appear:

SK1981 Golf Balls 10 Pack

So, if someone typed SK1981 in the sku1 field in the inventoryname1 field "Golf Balls 10 Pack" would appear.

If someone could help me figure out how to do this (please be kind), I would greatly appreciate it. I'm sure I'll have lots of other newb questions, but, that's my first one.

Many thanks all.

[LaRetta]

Hi gmiller, welcome to the Forum!!

Is the data in Inventory1 in a related file? To properly advise you, we need to know where the data "Golf Balls 10 Pack" resides. Either way, it can be accomplished pretty easily.

Please give us a bit more information about your existing structure and whether there is a related file (and what they are joined on). We can step you through it!

LaRetta

[gmiller1977]

LaRetta,

Thank you so much for the quick reply.

Well, I would prefer to keep it all in one fp5 file. Then the application is self contained, but, I have no objections to doing it 2 files either.

I guess my question is, what are the benefits of doing it one way vs. another?

If I kept it all in one file, would I create 2 other fields that would hold the SKU and Item description?

I might need a bit of a walk thru for this.

Thanks again!

Hi gmiller, welcome to the Forum!!

Is the data in Inventory1 in a related file? To properly advise you, we need to know where the data "Golf Balls 10 Pack" resides. Either way, it can be accomplished pretty easily.

Please give us a bit more information about your existing structure and whether there is a related file (and what they are joined on). We can step you through it!

LaRetta

[LaRetta]

Hi gmiller,

Well, I don't know enough about what you want to accomplish with your db to direct you properly. Consider this:

If, within this one db, you have to repeat anything (enter it again), you should consider using a related db. In other words, if any part of your dB is a 1:n (one to many), go related. Check out the samples section here ... there's a basic related demo which will help you understand relationships.

It sounds like you have an Inventory. And it sounds like you'll need Invoices (sales, purchases, whatever). And won't you need a Customer db? If you attempt to track all data from within one db, it could get quite complex. I suggest you read the FM manual on creating relationships (and scan the Forum sections RELATIONSHIPS and also RELATIONAL DATABASE THEORY) for ideas.

If you can give us a walk-through of what you want to accomplish and what data is one-to-many, we can suggest whether you need related dbs. Sometimes the answer is yes, sometimes no.

Bottom line is this ... if you are doing this just so you can see if you can do it ... then do it right!! Learn to use related dbs - because that's where the power of relational design shines! You will learn the most. To start, think through what you want to accomplish and picture the structure (one-to-many), then create your dbs, join them and add a few records to play with. Create a customer db, a product db, an invoices db, etc. and watch how they interact as you learn them. With only a few records, you will quickly see how they interact.

It's like learning to drive an automatic (flatfile, one db) but then you can't drive a stick. Learn to shift gears (relational dbs), then you can drive anything!

LaRetta

[gmiller1977]

OK, well, let me explain how *I* thought it would work:

I created 2 databases

one called pos.fp5

one called inventory.fp5

There are 5 lines in the pos.fp5 database:

5x sku

5x item description

So it looks like this on screen:

SKU1 ITEMDESCRIPTION1 QTY1 PRICE1 EXTPRICE1

SKU2 ITEMDESCRIPTION2 QTY2 PRICE2 EXTPRICE2

SKU3 ITEMDESCRIPTION3 QTY3 PRICE3 EXTPRICE3

Now, EXTPRICE1 is calculated QTY1 * PRICE1

In the inventory.fp5 database, I have 3 fields:

inventorysku

inventorydescription

inventoryprice

In the pos.fp5 database, I want the following to happen:

When I enter SKU1, I want Filemaker to look in the inventory.fp5 database for the same SKU (obviously a keyfield). However, I then want it to load:

inventorysku into SKU1

inventorydescription into ITEMDESCRIPTION1

inventoryprice into PRICE1

But I can't figure out how to access inventory.fp5 and read the data into pos.fp5

Does that make more sense?

[LiveOak]

LaRetta is absolutely correct. You will end up with a lot more files. I just complete an Order/Invoice/Inventory system for a client and we are currently at 30 files, pretty typical. To get the greatest advantage you want each "entity" to be in it's own file (table) and with relationships to other entities as needed. The objective is to have each piece of data stored in only one file and diplayed as needed in other files.

In some cases this objective will be intentionally violated, such as a customer address on an invoice. The invoice is a historical document and the customer address shouldn't change when it is updated in the Customer file.

You probably need to read a couple of good FM books: Scriptology by Petrowsky and Osborne is one. Using FileMaker Pro by Coulombre and Price is another. There is also a fairly new book by Chris Moyer and Bob Bowers I have not heard feedback on, but all of the authors mentioned are main players in the FileMaker business. I think it's out of print, but if you can find a copy of The Relational Database Advisor by Kimberly Maughan Saunders, it is a very good tutorial on relational databases.

-bd

[LaRetta]

Hi gmiller,

You need to study up on relational theory. It's just not the kind of thing that can be explained in a post. But you've given me enough to answer your orignal question, and I'm happy to do that

Go to your POS field definitions. Go to your ItemDescription field and select options to auto-enter. Select 'Lookup' and point to your Inventory Item Description field.

Now the Item Description (pulled from Inventory) will pre-fill when you enter a new record in POS (and type the SKU). That's the power of relational

Create and join and enter data in a few test dbs and you'll see what we mean!

LaRetta

[gmiller1977]

LaRetta,

Thanks for your post. This is what I was trying to do in the first place, but I keep getting an error message when I was trying it, so, I assumed I was doing something wrong. Let me list my steps that I have performed:

Opened my pos.fp5 database

FILE, DEFINE FIELDS

selected INVENTORY1 (this is the inventory part name field)

clicked OPTIONS

on the AUTO ENTER tab

clicked LOOKED UP VALUE

a window appears to make a relation:

In the pull down menu (upper right hand corner) I selected DEFINE RELATIONSHIPS

another window appears to define relationships for pos.fp5, so I click NEW

I select my inventory.fp5

In the relationship name window, I type "test"

In the left hand window (pos.fp5), I select Inventory1. In the right hand window (inventory.fp5) I select inventorypartdescription

I click OK

To close the "define relationship window" I click DONE

When I get back to the window that is labelled "Lookup field for inventory1" I select ::inventorypartdescription (if I don't select something, I can't click OK).

I click OK and get:

This lookup uses field "inventory1" in it's definition. Choose another field.

Am I not following your instructions correctly, or I have missed a step, etc?

Thanks.

[gmiller1977]

Oi, nevermind, I was making a circular relation. Thanks for all the help, got it working perfectly.

[LaRetta]

Hi gmiller,

Glad you got it working! As soon as I read that it brought up the Define Relationship box when defining the lookup, I knew you hadn't established your POS to Inventory on SKU# yet I thought you had already done that and I should have clarified that that needed to be in place first.

Just to clarify - your join relationship should be on SKU#. Then, when you set the lookup on your POS:ItemDescription, it will allow you to point to that join's ItemDescription field.

Cool, isn't it And it just gets better!

LaRetta