8 Replies Latest reply on May 11, 2010 4:32 PM by LaRetta_1

    Creating a sequence

    WebFlys

      Title

      Creating a sequence

      Post

      In a table I have 3 Fields:

       

      Order Number | Item Code | Unique

       

      There are duplicate records I am trying to identify and sequence at the same time to create a simplified relationship. For example, here is what a set of records might contain:

       

      1234  SKU1  XX

      1234  SKU2  XX

      1234  SKU3  XX

      3456  SKU1  XX

      3456  SKU2  XX

      5678  SKU1  XX

      5678  SKU2  XX

       

      I am trying to create a sequence for the X's above - I need the data to look like:

       

      1234  SKU1  1

      1234  SKU2  2

      1234  SKU3  3

      3456  SKU1  1

      3456  SKU2  2

      5678  SKU1  1

      5678  SKU2  2

       

      So basically for each UNIQUE ORDER NUMBER, I need to count in each record which number of X it is. I am just stuck! Any help would be appreciated. FMP 10 Advanced, local.

        • 1. Re: Creating a sequence
          philmodjunk

          Can you expand a bit on what you are trying to do here? There are ways to script what you want, but I'm not sure it's really necessary.

           

          What does this sequence represent? Why is it necessary to start over from one with each new order?

           

          If you just need a unique number that increases with each item added to the table, you could do this with an auto-entered serial number.

           

           

          • 2. Re: Creating a sequence
            WebFlys

            Thanks Phil - This is for orders - each order comes over as a separate record every time another SKU is ordered. So, one customer order may be 7 records. I have everything else figued out, and just started playing around with the script to do this. It's ALMOST working but it'et getting caught up in the LOOP. What I have it doing is (in short):

             

            Setting the first record field to "1"

            Copy & Paste the ORDER NUMBER to a GLOBAL field

            Copy & Paste the SEQUNCE to a GLOABL SEQUENCE field

            LOOP

            Goto next record

            If order number = global order number

            set field SEQUENCE +1

            ELSE

            Set Sequence to 1 (to start over)

            Copy & Paste Order Number & Sequnce (again)

            END LOOP

             

            I really wish I could just copy & paste the script in here so you could see - thanks so much for listening.

            • 3. Re: Creating a sequence
              raybaudi

              "So, one customer order may be 7 records."

               

              But it must be ONE record in the "orders" table and seven related records in the "order line items" table: doesn't it ?

              • 4. Re: Creating a sequence
                WebFlys

                YES - I JUST (literally a second ago) figured it out. In summary I was trying to mark order numbers in a numbered sequence 1,2,3,4... which had the SAME ORDER NUMBER. So this works...

                 

                • Go to Layout [ “Line Items” (Line_Items) ]
                • Go to Record/Request/Page [ First ]
                • Set Field [ Line_Items::Unique; 1 ]
                • Copy [ Line_Items::Order Number ] [ Select ]
                • Paste [ Line_Items::Global ] [ Select ]
                • Copy [ Line_Items::Unique ] [ Select ]
                • Paste [ Line_Items::Unique Global ] [ Select ]
                • Loop
                • Go to Record/Request/Page [ Next; Exit after last ]
                • If [ Line_Items::Order Number = Line_Items::Global ]
                • Set Field [ Line_Items::Unique; Line_Items::Unique Global + 1 ]
                • Else
                • Set Field [ Line_Items::Unique; 1 ]
                • End If
                • Copy [ Line_Items::Order Number ] [ Select ]
                • Paste [ Line_Items::Global ] [ Select ]
                • Copy [ Line_Items::Unique ] [ Select ]
                • Paste [ Line_Items::Unique Global ] [ Select ]
                • End Loop

                Also, I created a SIMPLE way to delete DUPLICATE records when done:

                 

                • #Mark Duplicates for Deletion
                • Copy [ Import Fields::Order Number ] [ Select ]
                • Paste [ Import Fields::Global ID ] [ Select ]
                • Loop
                • Go to Record/Request/Page [ Next; Exit after last ]
                • If [ Import Fields::Order Number = Import Fields::Global ID ]
                • Set Field [ Import Fields::Duplicate Record; "Dupe" ]
                • End If
                • Copy [ Import Fields::Order Number ] [ Select ]
                • Paste [ Import Fields::Global ID ] [ Select ]
                • End Loop
                • #Find and Delete Duplicate Records
                • Sort Records [ Specified Sort Order: Import Fields::Duplicate Record; descending ] [ Restore; No dialog ]
                • Go to Record/Request/Page [ First ]
                • Loop
                • If [ Import Fields::Duplicate Record = "Dupe" ]
                • Delete Record/Request [ No dialog ]
                • End If
                • Exit Loop If [ Import Fields::Duplicate Record ≠ "Dupe" ]
                • End Loop

                 

                • 5. Re: Creating a sequence
                  philmodjunk

                  Daniele, that's my point exactly.

                   

                  What's your table structure look like?

                   

                  Do you have an "orders" or "invoices" table that this table can be related to? That's the typical structure for this type of set up and it eliminates the need for this kind of sequence.

                   

                  On a side note, there are better ways to move data from one field to another than copy/paste. Look up set field and set variable. These options let you move data without putting data in the clipboard--which can annoy users when they lose data they've copied to the clip board due to a scripted "copy/paste" operation.

                  • 6. Re: Creating a sequence
                    WebFlys

                    Thanks Guys - really appreciate the help.

                     

                    YES - there are 3 tables and several relationships - that part is good. YES, I agree I will probably set vs copy & paste. Just imported 100 orders vis this new method and the data is perfect. Whew!

                    • 7. Re: Creating a sequence
                      LaRetta_1

                      I am sure there are much simpler ways to achieve what you are doing but it isn't clear what you are doing.

                       

                      "There are duplicate records I am trying to identify and sequence at the same time to create a simplified relationship."

                       

                      You are describing a lineitems table, as mentioned above.  Do you have an Orders, Products and Customers table?  It sounds like you want to create the parent Invoice  If you have an Invoice and Product table, you can create a simple relationship to LineItems and the count of each unique SKU/Invoice combination is automatic.

                       

                      If this is an import received from external sources and you are attempting to 1) create the parent invoice or 2) count the products per invoice ... or any other number of combinations, we can assist you easily.  It is normal to receive order lines from online sources as flat file (one record including customerID, orderID, ProductID, qty etc) and it is up to the DBA to massage these types of transactions and turn them into normalized relationships with orders (customer related to invoice related to lineitems related to products) in a seemless package. 

                       

                      The issue is protecting from importing twice and getting duplicates, having the script fail and NOT creating an invoice (and a host of additional possible issues).  And the goal is to assign the unique-generated FM auto-enter serial (the IDs) for each table and link them properly).  The attachment  Transactions.fp7  provides a way to handle it.  You may not want to also create the customer or products but some businesses need to do just that.  You can use just the parts you need.

                       

                      It also provide you with the downloaded table of the online transactions so you can view (and track) transaction fees, credits against a credit card and you can even import it twice and never get duplicates.   If I guessed wrong on the purpose, please fill us in but your description of identifying unique points to structural issues. :smileyhappy:

                      • 8. Re: Creating a sequence
                        LaRetta_1

                        Your data may be perfect but I would still suggest considering an alternate approach. :smileyhappy: