Ok, so here is my problem... We have a 3 database inventory/sales solution. There is one big problem though. Our first database (Order Form) is where we input items that customers order into a portal that is in relationship to our second database (Line Items), and then from line items it brings the item information and current inventory on hand from our products database. Now here is the problem, some of the items we sell in dozen and single quantities and what we need is a method of when the dozen item number is inputted into the order form portal, have it take 12 singles off the single item product record, in the products database, instead of taking one off of the dozen record. Is there such a way to do that? Help, questions or comments can be sent to me through the private im or e-mail. Thanks in advance.

Hi,

Starting from the end of it, what would be your invoice form structure ?

1 Pepsi Cola at 0,99$

1 Heineken at 1,25$

1 pack of 12 Pepsi Cola at 11,88$

1 pack of 6 Heineken at 7,50 $

What does this mean ?

Qty------Unit------Product_Id-----------Product--------Price

___________________________________________

1 ------Can------ID0001 ------------Pepsi Cola------ 0,99

1 ------Can------ID0007 ------------Heineken-------- 1,25

1 ------Pack-----ID0008 ------------Pepsi Cola ----- 11,88

1 ------Pack ----ID0005 ------------Heineken-------- 12,50

Packs could be defined as single products in your Product File.

Depending on your business, you could add some more flexibility on the quantities and prices.

When it comes to count, you'll need a relationship from the Pack to the Individual Product.

Packs could be defined as single products in your Product File.

Depending on your business, you could add some more flexibility on the quantities and prices.

When it comes to count, you'll need a relationship from the Pack to the Individual Product.

Ugo,

Thanks for replying. We have the current items listed in our products file like like this:

TT168 Choose: (-1) Ea. or (-12) Dozen Pack A-#1 Flower Lei (ea.) <--Identifier

TT168-1 1 Single Flower Lei $1.50 <-- where we would like inventory count

TT168-12 1 Dozen Flower Leis $14.95 <-- Dozen pack item #

Each item has a unique item number for the single, dozen and just a title record basically and we would like the accurate inventory count to be on the single item. How would you set up this relationship?

Also I found out that our database structure is FileMaker 4 standard order form sales solution that came stock with the software, we just have modified it to fit our business

Well,

You'd need this kind of file structure

Unique_ID TT168-1 1

Parent_Name : Single Flower Lei

Parent_ID : TT168-1 1

Price : 1,50$

Unique_ID : TT168-12 1

Parent_Name : Dozen Pack Flower Lei

Parent_ID : TT168-1 1

Price : 14,95$

As you can see, the common ID is the Parent ID.

Now, you'd also need an external file for the Pack defintion.

Very simple, with these fields

Pack_ID

Parent_ID

QuantityperPack

You'd be entering the records in this Join File through a portal set to "allow creation of related records" with relationship Parent_ID::Pack_ID.

The Parent_ID field (in the portal would be choosen by a value list of all Parent_IDs from your Product file).

When this is done, it would be some kind of cross-relationship from the Joinfile to the ProductFile to determine the Counts.

I still keep a version of FM4.1 if you want a test over a sample of your files.

Also I found out that our database structure is FileMaker 4 standard order form sales solution that came stock with the software, we just have modified it to fit our business

Are you using FM4 or just a template you have since FM4 ?

If you have 5.5, check this thread and sample test file

sample

Ugo,

Thanks for the help so far. The sample test file is pretty much what we have as our database with the Order Form, Line Items, Products, and Purchase Orders database. I cannot even begin to think on how to make that relationship between the dozen packs and the singles. We have a parent ID, which would be the manufacturers reference number. I am soo confused... any help is greatly appreciated though.

-Austin

Hi,

Check the sample back.

You are getting confused about words....

The file CatalogToSet is the file to look at.

There, you would find a Book_ID (the Parent_ID) and the new Catalog_ID (the package ID).

In fact, they both would stay in the Main file as a Catalog_ID !

Product File

RecordN