3 Replies Latest reply on Nov 11, 2009 7:23 AM by SaterInc

    Repeating field or Portals w/seperate table?

    SaterInc

      Title

      Repeating field or Portals w/seperate table?

      Post

      Here is my latest dilema,  In my job database each record contains about 10 sets of 3 fields that stores my outgoing shipments to that record, they are currently related to a seperate table that identifies outgoing shipments with a ID number.  The way I set this up has obvious limitations though, namely that each record can only have 10 outgoing shipments assigned to it before the user is prompted to ask there system adminstrator to add more.

      What I'm trying to do now is be able to store a near limitless amount of shipments to one record and still be able to view/extract/move that data between tables.  One route I was thinking was using the repeating fields option so I could have 1 set of 3 fields with 20+ repetitions. The other thought was using another table so i can store a record for each shipment, bearing in mind that several of the job records could be tied to my outgoing shipments table with the related ID number, then also have to be tied to this new table to retrieve the outgoing qty.  I can't store the qty to my outgoing shipments table because I could have several different parts with different qty going out on one ID number, and I would run into the same problem of having to either create a repeating field, or several fields to store different qty, and also the part number and job# so I can relate it to individual records in the Job Database.

      Well if any of that nonsense made sense to anyone, what are your guys thoughts? New table? repeating field? or if anyone else a better solution i'm open to ideas

        • 1. Re: Repeating field or Portals w/seperate table?
          raybaudi
            

          Hi

           

          think at repeating fields like at nice tools to act a looping calculation or to show buttons and pictures; never think at them to store data.

           

          So my thought is: use related table/s.

          • 2. Re: Repeating field or Portals w/seperate table?
            ninja
              

            Howdy SaterInc,

             

            It sounds like you know what you're after and how to make it, so I'll not give Table/relationship details about how to do it unless you want them...but what you're describing simly BEGS for a ShipmentLineItems table.

             

            Each good shipped (assume defined by product & Lot#) is shipped from an inventory record (key=InventoryID) against an order (Key=OrderID#)  The ShipmentLineItems table thus becomes your join table, joining the inventory you're shipping from and the order you're shipping against.  Unlimited line items per shipment, unlimited inventory transactions per product/lot, traceability to inventory, traceability to which order it went out on.

             

            If you've already built in the function to see the sysadmin to add more shipments, it means you've already outgrown the repeating fields...use the join table approach instead.  You can then even use the join table to print your paking slips/invoices/inventory reviews/etc.

             

            Is this enough to start?

            • 3. Re: Repeating field or Portals w/seperate table?
              SaterInc
                

              Thanks guys, I actually already have this tied to a template for our pack slips so it auto generates them, really got our company out of the stone age in the past few weeks.  Anywho, I appreciate the info, i wasn't to firmilar with repeating fields but I heard about them used as an array in older versions. 

              Thanks to you ninja for the idea of putting my shipmentline table joined between the other two, didn't even think of it that way, was just going to have them all relate to the main table but your thought sounds alot more functional.