4 Replies Latest reply on Feb 24, 2011 3:35 PM by johnsimms

    Linking Multiple Sales and Production Orders

    johnsimms

      Title

      Linking Multiple Sales and Production Orders

      Post

      Hi There,

         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?

      Thanks!

        • 1. Re: Linking Multiple Sales and Production Orders
          philmodjunk

          I'm not sure I follow all that, but I think you want to be able to link multiple "cuttings" records to multiple orders records. I think a "cuttings" record records how many cuttings you made for a specific plant on a specific day--but could be wrong here.

          If so, I'd treat the cuttings records as my inventory of stock that's avaialable for sale. A classic Invoice system in FileMaker has this setup:

          Invoices---<LineItems>----Products

          Where a portal to LineItems on the invoices layout is used to list the items purchased and Line Item fields such as unit price and product descriptions look up that data from Products.

          I suggest this modification of that schema:

          Invoices----<LineItems>----Cuttings>-----Products

          You can use a portal to Cuttings on a Products layout to manage all your cuttings records for each plant you sell. LineItems can list info such as price from Products, but list also cutting specific details from cuttings.

          If a farmer orders 3000 faye elberta peach trees from you, it might be listed like this in your lineItems portal on the invoices layout:

          Qty   ProdID   CuttingDate Desc                            Price   Cost
          2500  234      6/1/2010    Bare Root Faye Elberta   1.00   2500.00
          500    234      6/3/2010    Bare Root Faye Elberta   1.00    500.00

          This is not the only option available.

          • 2. Re: Linking Multiple Sales and Production Orders
            johnsimms

            Thank you for responding phil. I know my original post wasn't very clear and you do seem to understand the function of my "cuttings" and "line items" tables.

            I'm not sure that your solution quite matches my needs though. I think that I actually need line items and cuttings to be linkable. I can't exactly treat cuttings like inventory, because many of them may fail. At times as few as 70% may succeed. From a user interface standpoint, we need to know when a sufficient quantity of cuttings has been taken so that it's likely that an order will be filled (in which case we can remove it from the "to do" list), and we also need a reference so that when we are sorting through a batch of cuttings we know how many of those successful cuttings have already been pledged for a specific order.

            I am starting to implement this solution by using repeating fields. cuttings have a reapeating field "related line items" which contains the recordIDs for all associated line item records. Line items have a repeating field "related cuttings" which contains the recordIDs for all associated cuttings. My plan is to create a relationship based on these repeating fields. I think this solution is workable, but I hear that using repeating fields is a faux pas. And using this type of fields also creates additional scripting headaches (how to "Deassociate" records for example), So I'm still open to hearing alternate solutions but so far I think this is the best.

            • 3. Re: Linking Multiple Sales and Production Orders
              philmodjunk

               I think that I actually need line items and cuttings to be linkable.

              By my original suggestion, they are indeed "linkable". That's what this means:

              LineItems>----Cuttings

              and that's why my example line items entries are able to display two different cutting dates for the same type of tree.

              I can't exactly treat cuttings like inventory, because many of them may fail. At times as few as 70% may succeed.

              Good point, but not an insurmountable problem. You can add fields to the cutting record that report the number planted and also the number that are actually growing (I have several tree cuttings in the ground in my back garden right now. Wink) A sum function that sums the quantity sold in LineItems can compute the actual number of plants from these cuttings that have been put on order. Comparing the number alive to the number ordered can tell you how many are still available to place on an order and whether you have more ordered from that cutting than you have available so that you can update some orders to use different cuttings.

              As I said before, there's many approaches possible here. Listing separate line items for the same plant but for different cuttings isn't necessarily the way this has to be done here. You can use a join table so that you can link one LineItem record to many cuttings, but still link one cutting to many line items. That's a more complex structure, but it does offer some more flexibility.

              • 4. Re: Linking Multiple Sales and Production Orders
                johnsimms
                You can use a join table so that you can link one LineItem record to  many cuttings, but still link one cutting to many line items. That's a  more complex structure, but it does offer some more flexibility.

                Ah. This is the first time I've heard of this structure. (Like I said, I'm new). Doing some cursory research, this looks like just the thing. Thank you!