8 Replies Latest reply on Nov 23, 2009 1:09 PM by philmodjunk

    Trickly Field Possibly Portal Question

    mgxdigital

      Title

      Trickly Field Possibly Portal Question

      Post

      Hi!

      I currently have a layout that works as my packing slip for rolls of material.

      We have 20 fields (10 for qty & 10 for length of rolls)

      Our packing slips are filled out by our press operators and are filled in as the qty of roll that the length is the same in each field.

      For instance, if our customer orders 5 rolls and all the lengths are the same, the packing slip reads:

            QTY                LENGTH

              5                    1000'  

      (field:qty roll 1) (field:length roll 1)

       

      Some times there are 50 rolls per order, so they try to group as many numbers as possible with same length before moving to the next field.

       

      I'm purchasing a new program called Labeltraxx that requires me to have a line for every single roll that I ship out.

      So i'm looking to take the field in Filemaker that has 5 rolls @ 1000' and make it 5 different lines (maybe in a portal) that show 1 roll @ 1000'.

       

      Is this possible?

        • 1. Re: Trickly Field Possibly Portal Question
          philmodjunk
            

          That's also a better table structure for filemaker. A portal would seem a very natural fit on your layout for this and adds the flexibility of having more than 20 items in your packing slip if you want.

           

          In order to move the data from your current table into a new related table, it looks like you'll need to script the operation.

           

          If your current table is called Orders and has a serial number ID field called OrderNumb, you might do this.

           

          Define a new table, LineItems with at least the following fields:

          OrderNumb (number)

          Qty           (number)

          Length       (number)

           

          New relationship:

          Orders:: OrderNumb = LineItems:: OrderNumb (Allow creation of related LineItem records, delete Lineitems if Related Order is deleted.)

           

          Place a portal to LineItems on an Orders based layout and run this script from it:

          Show All Records

          Go To Record [first]

          Loop

            Go To Portal Row [last]

            Set field [LineItems::Qty ; Orders::Qty1field]

            Set Field [LineItems::Length; Orders::Lenght1Field]

            Go To Portal Row [last]

            Set Field [LineItems::Qty ; Orders::Qty2field]

            Set Field [LineItems::Length; Orders::Lenght2Field]

            #Repeat this for all 20 fields

            Go to Record [ Next ; Exit after last]

          End Loop

           

          If your fields are repeating fields you can import the records into the new table and select the "split repetitions into separate records" option.

          • 2. Re: Trickly Field Possibly Portal Question
            mgxdigital
              

            Hi! Thank you very much for your help.

             

            I think that the most rolls we have are 54 at a time going to one customer.

             

            Sometimes our packing list shows:

             

                 54             5000

            (roll qty 1)   (length 1)

               

                 10             4950

             (roll qty 2)  (length 2)

             

                 3               4750

             (roll qty 3)  (length 3)

             

             

            This portal using your script will then show 67 lines all with the appropraite length? Is this correct? I just have the make maybe 75 lines in the script?

             

            • 3. Re: Trickly Field Possibly Portal Question
              mgxdigital
                

              Hi,

              Also, where is the "split repetitions into seperate fields options"?

              I'm searching but can not find out how to do this.

               

               

              Thanks so much again!

              • 4. Re: Trickly Field Possibly Portal Question
                philmodjunk
                  

                "This portal using your script will then show 67 lines all with the appropraite length? Is this correct? I just have the make maybe 75 lines in the script?"

                No. In your last example, you'd have 3 lines in the portal showing the three rows you describe with 17 blank rows as I forgot to suggest code to Skip any blank lines.

                 

                Show All Records

                Go To Record [first]

                Loop

                  If [not IsEmpty ( Orders::Qty1field ) ]

                    Go To Portal Row [last]

                    Set field [LineItems::Qty ; Orders::Qty1field]

                    Set Field [LineItems::Length; Orders::Lenght1Field]

                  End IF

                  If [not IsEmpty ( Orders::Qty2field ) ]

                    Go To Portal Row [last]

                    Set Field [LineItems::Qty ; Orders::Qty2field]

                    Set Field [LineItems::Length; Orders::Lenght2Field]

                  End If

                  #Repeat this for all 20 fields

                  Go to Record [ Next ; Exit after last]

                End Loop

                 

                The import option, I mention shows up in a small dialog after you have matched fields in the Import Field Mapping Dialog and then Click the Import button. You'll see a small dialog box pop up labeled "Import Options". In the bottom half of this little dialog, you'll see the question: "Import Values in Repeating Fields by () keeping them in the original record () Splitting them into separate records.

                 

                This second option is by far the easiest way to break up values stored in repeating fields into separate records in a related table.

                • 5. Re: Trickly Field Possibly Portal Question
                  mgxdigital
                    

                  I've tried both ways with the same result. It just shows my qty1 over and over again.

                  Hopefully i'm being clear on exactly what i'm trying to accomplish.

                   

                  If i have:

                       4                  5000

                  (roll qty 1)     (roll length 1)

                       1                  1000

                   (roll qty 2)    (roll length 2)

                   

                  I'm  trying to get to:

                       1                  5000

                       1                  5000

                       1                  5000

                       1                  5000

                       1                  1000

                   

                  I need a portal to show every single roll with the appropriate length.

                  Both suggestions were greatly appreciated, but did not work for what I needed.

                  Any futher help would be amazing.

                  Thank you.

                  • 6. Re: Trickly Field Possibly Portal Question
                    philmodjunk
                      

                    I did not understand that to be what you were requesting. Not sure why you would want it that way, but a modification of the earlier script can do the job.

                     

                    Before we go there, are your fields repeating fields with 20 repetitions? Putting bits and pieces together, that appears to be the case and makes for a much simpler script if it is true.

                    • 7. Re: Trickly Field Possibly Portal Question
                      mgxdigital
                        

                      Hi,

                      I can paste a picture of what i have, I just cannot figure out how.

                       

                      I have 15 fields for roll qty. They are lined up (roll qty 1, roll qty2, roll qty 3 and so on)

                      Then underneath them, I have 15 fields for roll length (roll length 1, roll length 2, roll length 3 and so on)

                       

                      So the roll qty corresponds with the roll length below it.

                       

                      Currenly the guys can just put in 10 rolls of 1000, 5 rolls of 5000' and this works great for us.

                       

                      But we're getting this new system that puts each roll into a line and sends it to a computer for our customer to see.

                      This software is what is requiring us to have each individual roll and length on it's own line.

                       

                      So like I said above, if the guys just type in 10 rolls of 1000'....somehow I need to convert this into 10 seperate lines showing 1 roll of 1000'

                      • 8. Re: Trickly Field Possibly Portal Question
                        philmodjunk
                          

                        OK, you've got two problems here a need to split your data up into a new table where each individual roll has it's own record. You also have a rigid and inefficient structure to your current table that makes splitting that data up, more difficult by far than it needs.

                         

                        Run the script as I described to generate the records I described in the field I described. If you've already done this, then great, keep that table. 

                         

                        Now define yet another table: Rolls

                        with the fields you need for your other program. I'm guessing you'll need something like:

                        RollID, OrderID, Length. (Qty will now always be one in this table so there is no need for a Qty field.)

                         

                        Create yet another script:

                         

                        Go to Layout [LineItems (LineItems)]

                        Show All Records

                        Go To Record [first]

                        Loop

                          Set Variable [$OrderID; Value: LineItems:: OrderID]

                          Set Variable [$Qty; Value: LineItems::Qty]

                          Set Variable [$Length; Value: LIneItems::Length]

                          Set Variable [$count; Value: 1 ]

                          Loop

                             Go To Layout [Rolls (Rolls)]

                             New Record/Request

                             Set Field [Rolls:: OrderID; $OrderID]

                             Set Field [Rolls::Length; $Length]

                             Set Variable [$count; Value: $Count + 1]

                             Exit Loop If [$Count > $Qty ]

                          End Loop

                          Go To Record [Next; Exit After Last]

                        End Loop

                         

                        From here, you have a design/user question. You can modify your layout to use a portal to the Rolls layout instead of LineItems and you can then discard this table, or you can continue to use it, but use a script modified version of the above script to split the LineItems records up each time an operator completes an Order Record.