Auto-Populate ProductID and Name fields in Inventory Table

[AndyCan]

Hi everyone,

My apologies if this isn't the right forum for my question, but i wasn't sure where exactly to post.

My situation is as follows:

I am setting up an inventory system for a bar and have and INVENTORY table and an INVENTORY LINES table. The INVENTORY table has the details of the employees that took the manual inventory and a foreign key with the InventoryLineID that references the INVENTORY LINES table.

The INVENTORY LINES table contains a primary key InventoryLineID, a foreign key for the productID (a separate PRODUCT table contains the necessary data), a date field and a Quantity field.

I view the INVENTORY LINES table via a portal in the main Inventory layout where i'm planning on filling in the quantities for each product in stock.

The idea is to compare the results of the weekly inventory with the data from the previous inventory, deliveries received and sales made to find out the percentage of shrinkage (loss via theft or wastage) for each product.

My main question is how do a automatically fill the INVENTORY LINES table with all existing product in the PRODUCTS table so that only the quantities need be completed as one works through the table. This is the only way i can be sure that every product was accounted for even if the inventory amount for a certain product that week was zero. I'm worried that if i tried to manage the inventory with a form input than some products might slip through the net.

My second and related question is: Am i on the right track doing things this way. If there is another, perhaps more elegant solution, i would be happy to hear about it.

Many thanks in advance for your time.

Andy

[comment]

I would suggest you study the following thread:

http://www.fmforums.com/forum/showtopic.php?tid/183639/

Although it might seem strange to refer you to a thread that deals with surveys, the similarities outweigh the differences. Think of each product as a question (how many of me are there?), each employee as a subject, and each inventory-taking as a survey. The only difference is that your questions are the same each time, so here it is the responses that need to be related to a particular survey instance, instead of the questions.

[AndyCan]

Hi,

Thanks for pointing me in the right direction. I've done the necessary translations and setup my tables with a similar script to be run for every new Inventory, but i'm stuck at one point.

For some reason i can't fathom, i'm not able to copy the contents of the global field g_InventoryID (which is the current InventoryID) into the foreign key _fk_InventoryID. Both fields are located in the INVENTORY LINES table. The _fk_InventoryID field is setup to contain the result of an Auto-enter Calculation (see image below).

http://www.chacal.co.uk/Filemaker/AutoEntryCalculation.png

I have also included a relationship diagram so you can see at a glance the table setup and a snap of the New Inventory script.

http://www.chacal.co.uk/Filemaker/Relationships.png

http://www.chacal.co.uk/Filemaker/Script.png

I've literally spent hours trying to find what might be causing this value not to be copied, but with no luck.

Any suggestions would be gratefully received.

Andy

[Søren Dyhr]

I think you have taken the wrong template to study, it's the one Bruce made... that really rocks!

http://www.fmforums.com/forum/showpost.php?post/149069/

Which by the way have one of the coolest ways to make use of this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

--sd

[comment]

I cannot really figure out what your script does - why not attach a file?

I agree with Søren that the method shown in Bruce's demo is preferable to scripting.

[Søren Dyhr]

Well if the desires goes in the direction of some kind of scripting could it simply be:

Commit Records/Requests [ Skip data entry validation; No dialog ] 

Set Field [ TestItems::zPatientID; Case(0;0) ] 

Set Field [ Patients::First; Get ( CurrentTimeStamp ) ] 

Commit Records/Requests [ Skip data entry validation; No dialog ] 

Making firstname of the patient the time the of the counting.

--sd