6 Replies Latest reply on Jan 22, 2015 1:16 AM by Stu412

    Script step to go to next unique record

    Stu412

      Title

      Script step to go to next unique record

      Post

      Hi there

      I've somehow achieved my lookup on sub summary values - it's working and I'm not touching it!!

      The final thing I need to do is instruct FM to move through the data table which is being sorted and summarised so that it can get to the next distinct record.  The reason a simple Goto Next Record won't work is because the data table has many rows of data per customer, per year.

      What I need to do is to change layout, check the customer ID, is it the same as a variable I already have set?  If yes, goto next record, if no, return the sub summary figure.

      That's the context.  The data table looks something like this, just on a bigger scale:

      CustomerID | Value

      1010           | 10,000

      1010           | 15,000

      1010           | 5,000

      1020           | 15,000

      1020           | 25,000

      So, after FM has summarised customer 1010 and thrown the result of 30,000 elsewhere, it needs to go back to this table, check the next line, if it's 1010, goto the next line, if it's 1020, perform a task.

      That's where I'm struggling because if I understand correctly, the IF statement needs a boolean response rather that a 'go off and do something else' response.

      Sorry if this seems garbled somewhat, this is pretty much the final piece on this task (I HOPE!!), a task which has defied logic throughout :)

      Thanks

       

        • 1. Re: Script step to go to next unique record
          philmodjunk

          Do you have a customers table with one record for each customer? And this table is the source of your customer ID's?

          You could loop through a list of customers there with your script using either Go To Related Records or a scripted find to pull up the group of records for just that customer on the other layout.

          A very rough possible outline:

          Loop
             Go to related records
             Set Variable [$Subtotal ;
             Go to Layout
             New Record
              Set Field
              Go To layout [original layout]
              Go to record/request/page [Next ; exit after last ]
          End Loop

          • 2. Re: Script step to go to next unique record
            Stu412

            Hi Phil

            The answer to your question is Yes and Yes, but there's a 'but'.  For the particular task I'm doing here, not all customers have data within the found set, so using the customer table (I don't think) would prove reliable.......unless there's something I'm missing in the relationship.

            I've come up with similar to what you have so far but because of the above, I've focussed on using the data table which has multiple rows per customer.

            Using that, I have:

            Set variable [$CustomerID;Data:CustomerID) << This is what I use to create a new record on the other table

            Set variable[$CustDataID;Data:CustomerID) << This is a tracking variable used in a loop later on

            # Do the main loop of the task here, create new records etc...

            Goto layout [Data]

            Loop

               Goto record [next]

               Exit loop if $CustDataID <> $CustomerID

            End loop

            # The idea of the loop is that is checks the data records to see if the rows match the current $customerID variable. If so, skip to the next.  If there's a difference (ie, when the next customer's batch of data is reached in the found set), exit the loop

            Set variable [$CustDataID;Data;CustomerID]

            Forgive the pigeon code syntax!

            • 3. Re: Script step to go to next unique record
              philmodjunk

              not all customers have data within the found set,

              That need not be a problem if you use the proper steps. How you do that depends on how you pull up those records. This could be as simple as performing a find for the customer's records and then doing nothing but go to the next customer if the resulting found set is zero. If you use Go TO Related Records, the relationship has to include appropriate match fields and values to match to just the correct set of records. If you do that, you can check for the existence of any related records and move to the next customer instead of doing the GTRR if there are no related records to "go to".

              • 4. Re: Script step to go to next unique record
                Stu412

                I've taken a look at this and come up with a routine which searches through the found set while holding a variable of the last created record.  It compares this variable to the records on the found set and when it reaches a difference, creates a new record and updates the first variable.  Then it does it again until the end of the found set.

                I'm certain as you say there are other ways to do this!  This looks to be working now so thanks for your help :)

                 

                • 5. Re: Script step to go to next unique record
                  philmodjunk

                  Nothing "wrong" with what you are doing, it just might be a bit slower to complete--depending on the number of records to process each time you run the script.

                  There are quite a few approaches to how you solve this.

                  In another project, I set up a loop that found all "unsummarized" records, then found matching records based on the current record and created a summary record in another table before using replace field contents to "mark" them as "summarized". The loop then repeats to find all unsummarized records again. It exits the loop when the find fails to find any records that have not been summarized.

                  • 6. Re: Script step to go to next unique record
                    Stu412

                    That's a good way to look at it particularly when the summarised set is larger than the remainder.  Need some official training on this now!

                    The good news is that now I've cracked this little task...X number of new records are created depending on the number of staff with a customer and the profit share calc from the summary is written against each record.  In turn I can analyse this data any way I want.

                    Thanks very much for your time and patience with that one!