Warehouse stock count problem

[djeans]

I have an inventory related problem.

I have two simple databases for verifying stock counts in a warehouse. The first has the fields "Barcode", "Description", "Size", and "Cost".

The second has the fields "Barcode" "Description", "Stock Count", "Stock Count Total", and "Cost Value".

What I would like to do, is be able to set up the first database with every item in my warehose, and then use the second to walk the warehouse with may laptop and barcode scanner, scanning each items barcode, enter the "Stock Count" number, then have it update the "Total Stock Count".

I have managed to relate the databases so that looking up the barcode number works to pull up the item description. Where I am having problems is making the "Stock Count Total" give me a running total of stock count based on the barcode number. For instance, I may have the same item several places in the warehouse. Each time I create a new record, scan the barcode, and enter the stock count, I would like the database to find all other records with the same barcode number, then give me a running total of the Stock count numbers.

Can anyone help an extreme novice out?

PS

If I am going about this the hard way, please let me know if there is a better way.

thanks

Darron

[-Queue-]

Since you already have a relationship based on barcode, create a calculation field of Count(relationship::barcode) to determine how many related barcodes there are.

[djeans]

Thanks for the reply.

I tried this, but I must be doing something wrong. When I put that into the calculation formula, I get an error that says "Field not Found" with the (relationship::barcode) part highlighted.

Can you be a little more specific in your instructions?

Thanks

[djeans]

OK,

I got a little further. I figured out that (relationship) is the related database (Ok, i am an uber novice, I know), but I'm stuck again.

My two databases are "RetailCatalog" which contains every item in the warehouse indexed by barcode number, and "Retail Inventory" that is what I am going to use to count the warehouse.

I created a calculation field in "Retail Inventory" with the formula "Count(RetailCatalog::BarCode)" but that isn't doing anything for me.

What i want it to do, is give me the total running count for the field "Stock Count" for all the records that have the same Bar Code number.

I tried "Count(Stock Count, RetailCatalog::BarCode)" , but that didn't work either.

Thanks in advance for your help

[Ugo DI LUCA]

Hi,

I suspect you're confusing yourself with how a relationship is created and/or named, and how to use it in a calculation.

You must create, define and name each relationship through the 'Define Relationships Menu', as FileMaker allows for multiple relationships from one file to another.

While any new relationship created will default to the Parent FileName when created, you should rename it according to the nature and goal of this relationship, for instance here "CatalogToInventoryByBarcode".

The calc now should read Count(CatalogToInventoryByBarcode::Barcode) where 'CatalogToInventoryByBarcode' is your relationship with Barcode as the key for both sides of the relationship (on the left and right side of your define relationship box)

While you may directly type it in the calculation box, you'd better :

- Create your calc field

- Select Count in the functions list

- While "field" is highlighted in the calculation box, select your relationship from the drop-down on top of the fieldnames, and pick the desired field (in this case "Barcode")

I hope I understood your troubles...

[djeans]

Thank you for your reply.

I am very confused about the relationships and formulas. Your explanation helped me understand a little, but I still am not getting what I was hoping for as a result of the calculation.

I followed your intstructions, but the formula is resulting with 1 every time. Also, it doesn't seem to be addressing the stock count field at any time.

What I need it to do, is take the "Stock Count" number for every record that has the same "Bar Code" number and give me a running total.

Thanks again.

[Ugo DI LUCA]

Hi,

Then it is a Sum(YourRelationship::StockCount)

[djeans]

I am running into another problem.

"Stock Count" field is only in the inventory database, not the catalog database that the file is relating to. When I do SUM(Relationship::: there is no "Stock Count" field to select.

What am i doing wrong?

Thanks again

[djeans]

OK,

Some sleep helped. I related the database to itself, and then I was able to select the fields that I needed.

Thanks for the help

I am sure I will have more questions soon

[djeans]

Ok,

Next question has already popped up.

Once I have counted the entire warehouse, is there a way to have FMP print a list of every item sorted by Bar Code and show the Total Stock count for each item? In other words, if there are 5 records with the same barcode number each with a stock count of 10, can FMP print a report only showing that item once, but showing the total stock count of 50?

Thanks again for the help.

Darron

[-Queue-]

Here you would use a summary field (total of stock count), a layout with a subsummary part by barcode (and no body part) containing the summary field, and a sort by barcode. When you preview or print the found set, you will see only one subsummary for each barcode.

[djeans]

Curse my newbie ignorance!!

I am almost with you on this. Just to clarify, when I create a summary field, am I totaling the field "Stock Count" that is the number counted for that particular record, or "Stock Count Total" that is the running total of all records containing the same barcode?

Next, I am lost on "a layout with a subsummary part by barcode (and no body part) containing the summary field, and a sort by barcode."

I start a new layout, then do i choose "blank layout" or one of the other options?

When I create the subsummary, should I check any of the options at the bottom? In the subsummary part, do I include the barcode field too?

So far all I have made it do, is show one barcode, and whatever the total count for all barcodes was at the time that record was created, not the total for that barcode.

Thanks again for your time helping me.

darron

[-Queue-]

Summary fields total data for each record in the found set, so you would want to total the Stock Count, not the Stock Count Total. I would choose 'blank layout', then select the Body part and delete it. Go to Layouts -> Part Setup, click 'Create', 'Sub-summary when sorted by', and select your BarCode field. Disregard the options at the bottom for now. Put all desired fields in the subsummary part. Sort your records by BarCode, and then create a script to go to the report layout, sort, and preview/print.

[djeans]

I am so close, but not yet.

When I do that here is the result that I get.

I create 4 records

Barcode #12340 with stock count 10

Barcode #12341 with stock count 20

Barcode #12342 with stock count 30

Barcode #12340 with stock count 5

I expected a report showing

12340 = 15

12341 = 20

12342 = 30

But what I got was

12340 = 15

12341 = 35

12342 = 65

Now I am lost again.

[-Queue-]

15 + 20 = 35; 35 + 30 = 65. Deselect 'Running total' in the summary field's definition.

[djeans]

You, my friend, are a GENIUS!

Thank you.

Now I can begin the task of writing the scripts to try to make it all work.

Thanks again

I'm sure I will be bugging you with questions again in the near future

[-Queue-]

My pleasure. Feel free to PM me, too, if you prefer.

[Kirsten]

Hi dgeans,

You will need to make use of the subsummary part in a new layout. In the subsummary part definition box, you specify which field you wish to have your data grouped by, (or summarized by). This would be the Bar Code.

To total all of the records with a stock count for a given barcode, you will need to create a summary field in the field definitions box, say TotalStockSum. Set the summary field to total the stock count field. Place this new TotalStockSum in the new layout in the subsummary part you defined earlier.

If you wish to show all the records with a stock count for each barcode, 5 in the example you've described, you place the Stock count in the body part of the layout. If you don't need to show the detail of all the inventory records, then you can delete the body part and only show the subsummary part in the layout.

To have the report print correctly, sort by the summarized field, which would be BarCode. To view the report, go to preview mode. The data will not show up correctly in browse mode.

HTH,

Kirsten Masse

[djeans]

Thanks for the help,

I have another question to throw out there

I have multiple count areas (warehouse, store shelf, etc). Is there a way to designate what area i am in (maybe a check box or radio button) with out having to select it for each new record. In other words, I would like to have it set up so that I could set it for warehouse, and have all new records automatically set to warehouse until I move to a different location and set it for the new location. Then be able to find items, and see how many are in each location.

I would still need to have the same functionality that we have already created, which is using the sub summary to tell me what the total number of each item is.

I am attaching the two files i have created to maybe help understand what I am trying to do.

thanks

[djeans]

Forgot to attach files

SendToForum.zip

[Kirsten]

At first glance, I would suggest an auto-enter data to the location field. The downside is that each time you change location, you would need to update the field definition to change the auto-enter data option to the new location.

You might also wish to create a custom message dialogue box, then run it from a script each time a new record is created. I've set up custom messages before, and they can retain the most recent entry, which means you wouldn't have to type it each time.

Or just set up a value list of locations and attach it to the location field, so you only need to point and click instead of type.

HTH

Kirsten Masse

[djeans]

I went with the auto-enter idea. I don't know why I didn't think of that. I only have two locations, sometimes 3, where I am counting, and I count the location in entirety before moving to the next, so retyping when changing locations isn't a big deal.

Thanks for the help.

Darron