Live Inventory Database

[Database Administrator]

Hi all

I am using FMP 8.5 adv on mac osx.

I am currently trying to create an inventory database as part of a
larger system. But for now I am creating the inventory part separately
until I can figure it out. I am looking for advice on how I can start!

So far I have created three tables:

- Goods In
- Inventory
- Invoiced Items

All three tables have the same fields:

- Item Number
- Brand
- Quantity
- Unit Price

I want to upload new items via an excel file into the "Goods In"
table. This will be done each time a new shipment is received. Within
the "Goods In" table there could be repetitions of the item number and
brand, however with different quantities. This is fine as I want to
track what we received in each shipment (will add more fields such as
shipping ref later).

Initially (as there are no sales yet) I want the "Inventory" table to
reflect the "Goods In" table. However, when the item number and brand
are matching just to sum the quantities and if the record isn't
existing in the "Inventory" table to create the record for this item.
**I am really puzzled as of where to begin with this.**

After I can figure this out I can perform the same thing on the
"Invoiced Items" table and just subtract that from goods in to get my
quantity in inventory.

This task is too time consuming to do manually as we hold more than
50,000 different line items ... and we don't know what will arrive in
the next shipment. If anyone can help me get off the starting blocks
it would be a great help

Martin

[Database Administrator]

On 2008-11-04 06:42:23 -0800, "martin.a.john@gmail.com"
said:

> I want to upload new items via an excel file into the "Goods In"
> table. This will be done each time a new shipment is received. Within
> the "Goods In" table there could be repetitions of the item number and
> brand, however with different quantities. This is fine as I want to
> track what we received in each shipment (will add more fields such as
> shipping ref later).
>
> Initially (as there are no sales yet) I want the "Inventory" table to
> reflect the "Goods In" table. However, when the item number and brand
> are matching just to sum the quantities and if the record isn't
> existing in the "Inventory" table to create the record for this item.
> **I am really puzzled as of where to begin with this.**
>
> After I can figure this out I can perform the same thing on the
> "Invoiced Items" table and just subtract that from goods in to get my
> quantity in inventory.
>
> This task is too time consuming to do manually as we hold more than
> 50,000 different line items ... and we don't know what will arrive in
> the next shipment. If anyone can help me get off the starting blocks
> it would be a great help

First of all, I would recommend setting fields in the Inventory table
through scripts. With the number of items you're carrying, and the
number of transactions as time goes on, displaying summary calculations
based on related data is going to sllloooowww your performance
dramatically. Your files will become unuseable. Having stored data in
the fields below will assist in really speedy
reporting/searching/summarizing.

I'm going to presume you have a relationship from Inventory to Goods In
and to Invoiced Items based on Item number.

So, in the inventory table, make three number fields:

1. Total Items Received
2. Total Items Invoiced
3. Items in Stock

Given practical considerations, you might also later include:
5. Physical inventory count and
6. Shrinkage

Now in a script, set field #1 above with a

Set Field [TotalItemsReceived; Sum(GoodsIn_ItemNumber::Quantity)
Commit Records

Now you've got the sum of all related Goods In records. Wrap that puppy
in a looping script, and you can run through all records in the db. Put
the commit records after the loop.

Do the same to summarize the related invoiced items. Then your Items in
stock are Field1 - Field2.

You can put all these scripts in one, just use Script Parameters to
distinguish which one runs. If SP's are more advanced than you want to
get into, separate scripts are fine.

As new records are created in Goods In, you can isolate just those
records that are changed in that table (Newly imported records always
form their own found set) then go to the related Invoice record set
(all the related records to the ones that were just imported) and run
that looping script, so you don't have to do all 50,000 each time.

As Invoiced Items records are created, do the same to keep the
Inventory records are current.

Once you have fields #5 & 6, and do a physical inventory, you can
create scripts to incorporate those numbers into actual Inventory
totals.

This should get you started.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

[Database Administrator]

Lynn

Thanks for this.

I have got as far as creating the new records in the inventory table
and making a sum of the qty. However, how can I make the sum only add
up the quantities only if the Item Number and Brand match (not just
the item number)?

Martin

[Database Administrator]

On 2008-11-07 06:20:29 -0800, "martin.a.john@gmail.com"
said:

> Thanks for this.

You're welcome.
>
> I have got as far as creating the new records in the inventory table
> and making a sum of the qty. However, how can I make the sum only add
> up the quantities only if the Item Number and Brand match (not just
> the item number)?

FM now allows multi-predicate relationships.

In the relationship dialog (reached by double-clicking on the
relationship line between table occurances), you can add Brand::Brand
to the relationship in addition to ItemNumber::ItemNumber.

Then it will only total the correct items.

If your item number isn't unique to a single brand, then you'll need to
do this anyway.

Your other option is to make each item number unique to a brand as well
as a part/item. If your business doesn't run that way, never mind.

Anyway, I recommend reading more about relationships in FM in the help
or in a good third-party reference. Amazon has a bunch of them.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

[Database Administrator]

On 7 Nov, 19:29, Lynn Allen wrote:
> On 2008-11-07 06:20:29 -0800, "martin.a.j...@gmail.com"
> said:
>
> > Thanks for this.
>
> You're welcome.
>
>
>
> > I have got as far as creating the new records in the inventory table
> > and making a sum of the qty. However, how can I make the sum only add
> > up the quantities only if the Item Number and Brand match (not just
> > the item number)?
>
> FM now allows multi-predicate relationships.
>
> In the relationship dialog (reached by double-clicking on the
> relationship line between table occurances), you can add Brand::Brand
> to the relationship in addition to ItemNumber::ItemNumber.
>
> Then it will only total the correct items.
>
> If your item number isn't unique to a single brand, then you'll need to
> do this anyway.
>
> Your other option is to make each item number unique to a brand as well
> as a part/item. If your business doesn't run that way, never mind.
>
> Anyway, I recommend reading more about relationships in FM in the help
> or in a good third-party reference. Amazon has a bunch of them.
> --
> Lynn Allen
> --www.semiotics.com
> Member Filemaker Business Alliance
> Long Beach, CA

Thanks again. I added the second relationship, works amazingly