need help with calculation for inventory

[destaroyah]

I am working on an inventory database to track medical equipment for patients. I started with the filemaker 10 template (even though I'm using 8.5) included with the filemaker downloads and modified it. One thing about the provided template is that it doesn't actually count your inventory. You must do it yourself and then input that number as the item total. Why bother with a program if you have to go over and count every time you need to know something about your inventory?

I want it to count and give me a total for each item number - brand, but we also input serial numbers for these high-price items (we have very few separate inventory items). So I am also needing to be able to have it look at the serial number and count unigue sn's for that item number and manufacturer. I'd also like to be able to verify serial number so that the same sn doesn't get input again for a specified item number and brand.

Then too, I have a radio button set for sold, loaned, returned and retired. A sold, loaned or retired item would no longer be IN inventory. I need it to count IN inventory items for my "amount in inventory" per item number, brand and SN. I fear this part may not be handled the best way it can be. I'm not really knowledgable of the ins and outs of inventories or databases for that matter (this is my second fmp database project). We have units we sell. Some that are loaned out for the patient to decide if they would like to buy one and units that have been returned from being loaned. We really don't normally get a returned purchase unit. I actually think this area may need more thought. Any suggestions would be most appreciated. The loaner units are added to if all other loaners are currently out, but will become the borrower's purchase unit if they decide they want one.

Due to our small number of inventory items, I have only one table for it. A Patient table is tied to it so that we know who received what.

I hope I've explained that so its understandable. Yes, I'm somewhat verbose. :

[comment]

Lots of issues all at once - let me pick this one to begin with:

I have a radio button set for sold, loaned, returned and retired.

This is not a good method, IMHO, as it leaves no trail. There should be a related table to track an item's movements. This could have a separate record for each movement (either in or out), or a single record could handle both in and out (e.g. a loan) - this is largely a mater of convenience.

[destaroyah]

Not sure I follow you, but if I do how could I do that and have only one option available at a time. I had the radio button list because I want them to only be able to choose one. Or would/could I use the radio button set with the other table as well and just have a portal to show it?

I think you're saying the radio buttons will change the record to show its current state only and you're suggesting another table would allow multiple records to show, for the loaners especially, their coming and going. That right?

[comment]

could I use the radio button set with the other table as well and just have a portal to show it?

Something like that. The problem with your current method is that if you accidentally change the radio button value, you have no way of knowing what it should be.

[destaroyah]

ok. so i know now what to do about that. what about the rest?

[comment]

Assuming you can now calculate each item's IN status by referring to the 'log' table, the next step would be to summarize the results by the item's group. This can be be done in several ways, depending on how you intend to use the results.

The simplest way is to produce a report, sub-summarized by the group. A summary field defined as Total of IN will provide the count of items in each group (note that I am assuming that the IN calculation returns a Boolean value: 1 if true, 0 or empty if false). However, the results can only be viewed in Preview mode, or if the report is printed out (version 10 excepted).

To view the summaries "live" in Browse mode, you could define a self-join relationship of the Equipment table, joining on the group. A calculation field defined as Sum (Equipment 2::IN) will return the total count of items in the same group as the current item.

A better method would add another table for Groups and relate each item to its parent group. This way you can get an overview of the count of available items in each group by calculating Sum (Equipment::IN) in the Groups table.

[destaroyah]

Assuming you can now calculate each item's IN status by referring to the 'log' table, the next step would be to summarize the results by the item's group. This can be be done in several ways, depending on how you intend to use the results.

About that log... how does it know which entry is the current state? Will it automatically look at the date and pick the most recent?

To view the summaries "live" in Browse mode, you could define a self-join relationship of the Equipment table, joining on the group. A calculation field defined as Sum (Equipment 2::IN) will return the total count of items in the same group as the current item.

Sorry, the tables are Inventory, Patient and a new Transaction table to handle the log. IN is not a field, just the state of the equipment such that if its in inventory, I referred to it as being IN. What I have is this: There is a separate record for every item to be tracked. In that record I have an Amount In Stock field to hopefully show that amount. I also have a field for a re-order level which will show an alert when the Amount In Stock level reaches that amount set by the users. I want the Amount In Stock field to show just that. Item entries will include an item ID number, item name, brand and serial number. So, I need a count that reflects those identifiers.

A better method would add another table for Groups and relate each item to its parent group. This way you can get an overview of the count of available items in each group by calculating Sum (Equipment::IN) in the Groups table.

I don't want to overcomplicate this databse. Its really meant to track a few items... like under 20 separate, high-priced items. A grouping could turn out to be ONE item is what I'm getting at I suppose. So I'm not sure a Group table would apply here, but I may just not understand your reasoning. Sorry if I seem particularly dense. I had brain surgery almost 2 years ago and I can get confused or a little lost sometimes. I very much appreciate your help.