Linking Multiple Sales and Production Orders
I'm a bit new to filemaker. I'm developing an order / production tracking system for my wholesale plant nursery. Our production process requires us to take "Cuttings" which become ready a few weeks after we produce them. Because of the lag it's important to us to link sales orders with cuttings that have already been taken so that we know which orders we still need to worry about.
At first I attempted to solve the problem like this:
I have a table with sales order line items
I have a table listing all the pending cuttings we've taken
When entering new cuttings I allowed the user to select "sales orders" to relate to the cutting by displaying a portal which linked to a script that would set a field on the line item called "related cutting id".
Then When we came back to pull our cuttings after 2 weeks we could see which orders were related and fill those orders.
Unfortunately, this system didn't work for a variety of reasons. First, if an order is very large, it may require several days worth of cuttings to fill. Or we may only be able to partially fill it. If this is the case, I'd like to be able to link one order to multiple cuttings (currently one cutting record can be linked to multiple orders but not vice versa). Second, We may sometimes make cuttings that haven't yet been ordered, or that are in excess of our orders. In this case, when entering an order I'd like to see cuttings that are pending that could be linked to that order.
My ideal solution would look something like this:
a "line item" record can be linked to any number of "order" records
an "order" record can be linked to any number of "line" items
If I could set up this relationship I could implement the interface peculiarities that I want to see. Off the top of my head the easy construct would be for "line items" to have a member field that was an array of associated "cutting IDs" and "cuttings" would have a field that was an array of associated "order IDs". But to my knowledge Filemaker pro doesn't allow arrays. Is there a workaround I haven't thought of?