I am not sure it is only the matter of design which I adopted or it is limitation of Filemaker. Here is the case.
Our product is multi-dimensional. First comes section name (60) then its thickness (4) then colors (4). Total 960 products need to be created. Next comes length size which is challenging. Although we have 3 standard length sizes 14, 16 and 18 feet but some time a customer demand any odd size which need to cut from a standard size. So the size available in inventory can be 1 to 18. So 18 more division need to apply to the product list. Now it will be 17280 products in the list. Each will show its quantity in inventory. There is another approach. We could keep 960 basic products and the length size could be created as fields (18) in the table. But there were many complications in the design. First we need a long formula to calculate the total feet as the product is selling in per foot price. Each field will be multiply to its quantity, then added all these multiplications (18).
When we prepare a bill, we draw required standard length size but when we sell a piece we first draw any available std size in separate table then enter the quantity of 2 sizes in the entry table. For example we need to sell a piece of 7’, we draw a 16’ length, cut into 2 pieces 7 and 9. Now we draw 16’ from main inventory table and enter 7’ and 9’ manually in the main table. After that we can be able to prepare the bill.
Recently I seen a database application on our retailer's shop. It was created in Oracle. The piece cutting process was automatic. In preparing invoice, when select first three categories of product, a list of available length sizes was appear in the bottom. Now we choose 16’ length in a cell to cut and enter 7’ in next cell which is our required cut size, the remaining 9’ length size was appearing automatically in the available length sizes.
Now my question to the experts developers is (which mostly know the oracle coding) that is it possible only in Oracle or is it only the design matter and can be adopted in Filemaker by using some script?