What do you mean by "store"? Is each 'store' a group of records in the same table? Or do you have a different table for each 'store'?
There was one storage before and one table "Products" with all our products, but now we created an other storage to store pieces and the table name is "Product_Pieces".
I have tried to create a new table "Locations" and it is parent of above both tables. Both above tables have one linked field "Location ID" which is a global field. But next how to connect it with Transactions table to appear it in portal where we can select "Location ID".
One other idea is coming in mind as you mentioned to create two tables as child of same "Products" tables for quantities of these two stores. You can guide better which option will work more efficiently and how"
I haven't shared any ideas with you at all as I am still trying to confirm what design you have. I am still guessing a bit, but it sound like by the word "store" you mean "table". If so, then this is not the best design option to use and is the source of the trouble you are having. I see no reason not to put all of the data, both the pre-cut lengths and the remnants produced by custom sizes into the same table. You can use a category field to tell one group of records from the other for reports and for data entry tasks such as this one.
A conditional value list can then be used to produce a list of just the values from one category or the other once the records are stored in the same table.
agreeing with Phil, I think that you should just use the Products table.
I would add a number field to Products, called flag_CutOff, and set it to be zero for your standard lengths and 1 if it's a cut-off. In the parent table, you could create a matching global number field and use it to filter your portal (or in a relationship).
Hi Phil and David
I did not say that it is your idea but actually your question "Is each 'store' a group of records in the same table?"
was turning me to think that way. Actually I am not a IT person but a simple maintenance technician working in a big oil company where we use an enterprise Oracle database. There we use variety of ways to putting records. That's why it comes in mind to do this database I am creating might have this and that way.
Thanks for your suggestions. I will create new records for cut-off pieces in the same table and they would have a distinguished ID and also a field showing that they belong to which group.