advice needed for design

[djeans]

Does anyone have any suggestions on how to solve this problem?

I want to create a database to inventory liquor bottles. I want to scan the barcode on the bottle, and enter a count for full bottles, or weight of the bottle for partial bottles, and have the total amount tallied in a report of all bottles at the end of the counts. I have the liquors all entered in a table with their barcodes, full weights, and empty weights.

The idea is to scan the barcode, perform a lookup to see what the bottle is, and if it is a full bottle (or multiple full bottles) enter the number for the bottles. If it is a partial bottle, set it on a usb scale and enter the weight of the bottle, which would then be calculated to let me know how much is in the bottle. I use a plugin that creates a new record after data is entered into a specific field so that the count can progress without me having to stop in between scans to create new records.

My problem is in how to be able to switch between counting full bottles and partial bottles. Most of the full bottles are going to be in the same area, and partial bottles in another area, so I tried creating two different tables, one for full bottles, and one for partial bottles, but I am having a hard time combining the counts for both barcodes that way. I also tried creating two different layouts for the same table, one with the calculation fields for calculating the weights, and one with just the full bottle count fields, but that isn't a very elegant solution because I would prefer to only see the records for the full bottle counts in that particular layout, but it shows all of the records created when doing the partial bottle count as well, they just appear to be empty records.

Does anyone have a suggestion for the most elegant way to achieve this?

Thanks

Darron Jeans

[mr_vodka]

How about something like this?

BTW you do not have to use a plugin necessarily. If you program your scanner to to enter a carriage return after each scan, and then you set your tabs to be your return key then it should go to the next available portal record for new enteries.

liquor.zip

[ThatOneGuy]

Hi Darron:

What a cool project! Only some hasty pudding ...

• A single Bottles table, regardless of balances, that serves as a "parent" to ...

• A single Readings table, where each scan/weigh transaction is created as a "child" to its related Bottle parent, and

• It may be useful to consider a "super parent" table to Bottles, something like Spirits, from which you could see all child Bottles and all grandchild Readings.

Maybe ... Spirits --> Bottles --> Readings

With a solid structure (I can't promise the one above is the best model), you'd have a crucial piece to a slick Inventory/Purchasing component. Coupled with the hardware you mentioned, this would create numerous options ... some of which seem a little "out there," but are undeniably intriguing.

P.S. It's great your post regards FM8.x.Advanced. Your profile's reference to FMP6 made me pause for just a moment.

[djeans]

Thank you for the reply.

A couple of questions:

I do have the scanner set to send the carriage return, which goes to the next field, but is there a way to set filemaker to create a new record after leaving a certain field without a plugin?

Also, looking at your file, I am not sure which layout is for which, other than liquor. What is the j-line items layout for?

Thanks again for the reply.

Darron

[djeans]

Thanks for the reply.

I will give it a shot.

I tried to find where my client version is located in my profile, but wasn't able to find it.

darron

[Lee Smith]

Hi darron,

You entered everything correctly.

If the version in the profile is differnt than the one the question is regarding, you put the right version for the question (as you did) in the New Topic Widow.

Anyway, you did it right, Good Job.

Lee

[mr_vodka]

If you relationship is set up to 'Allow creation of records...' then when it tabs to the blank line and a new UPC is entered, it will create a new record automatically for you.

As for the layouts, the only one you really have to use is the order layout. In this basic inventory/orders structure, there is a product table (liquor), order details (order), lineitems (j_line_items) table.