My product is stored in different locations so one product could be in several locations.
What does this mean?
I store product in different locations within a building.
Product is stored at one location for immediate sale. There is back up product in a store room. There is more product stored in refrigerator.
within these location product is sorted in different formats which I can then sort product to match each format. by vendor in one location plu # in another location, and category in another location.
Well, in a basic form, you should have one table with storage locations, plus a join table that combines a specific storage location with an item and lets you specify a quantity. This would be where you add/subtract quantities for sales and orders.
Then you can simply sum up all quantities from tat table by product and e.g. store them with the product record itself, and/or create a replenishments list etc.
Is it possible for me to create a button on the Physical count table. That would create one record for every product from the product table.
If I'm understanding correctly it sounds like you are taking inventory, and you want to prepopulate a physical count record for each product so as you walk around and tally the products there is already a record created for you to enter those counts.
There would be a couple of ways to do this depending on your situation.
One way would be to do an import from the product table to the physical count table. The only field imported would be the product primary key in the product table into the product foreign key in the physical count table. This import would give you one new "empty" physical count record for (and linked) to each product record.
Another way would be to create a looping script that went through your product records and created a physical count record for each product record as it went. Something like:
Go to record/request (first)
Set Variable $productID = Product::ID
Go to Layout - PhysicalCount
Set field PhysicalCount::Product ID = $product ID
Go to Layout - Product
Go to record/request (next, exit after last)
Based on your explaination to erolst it is still unclear to me whether you need to store the data by location, or whether you were explaining the physical process and you just need the total physical count, but are not recording location counts in your system. The methods above are assuming there is not a location table (that's not to say that perhaps you wouldn't be well served to restructure things and have a location table).