3 Replies Latest reply on Jan 3, 2014 12:56 PM by philmodjunk

    Correct placement of LOOP command

    AnthonyTesoriero

      Title

      Correct placement of LOOP command

      Post

           Hello All,

           I am having difficulty with a script that includes a loop command. My goal is to get the script to write a classified AD using data from multiple fields from multiple related tables. I've gotten close but continue to fail to get the ad to write correctly.

           There is a very specific format to this legal AD. It begins with a standard sentence "AUC 0935861 sells on 1/30/14 at" the variable being the date... then proceeds to find data in related tables to name the time, vin number of a car and then to another related table to get the proper lienholder data as it relates to the record then it should loop back to the next record in the found set to get the time of the next auction, next vin number and lienholder information and loop again until all the records in the found set have been exhausted. It should please the data into the ad in the chronological order of auction times and end.

           It seems that I have been able to get it to do the first part but when it loops it's not pulling the time of the next auction or the year make and vin number of the next related record. It does however seem to be pulling all of the lien holder information for all records in the found set.

           The result is I get an ad for one Vin number with all lien holders for all records in the found set following... It ignores the hour year make and vin number of the related record.

           I have included a screen shot of my work...

            

           Thank you in advance for your help

      Screen_Shot_2014-01-03_at_1.13.05_PM.png

        • 1. Re: Correct placement of LOOP command
          philmodjunk

               If the value in $Vehicle is different each time the script loops, that set variable step must come after the Loop step so that it is assigned a new value each time your script loops. The same would be true for any other such values.

               But I do not know how you have set up your tables and relationships so that is just a bit of a 'stab in the dark' as to what is going wrong here. A number of other parts of your script don't look like they are correct but that could just be my ignorance of how your database is structured and how the final text produced by it is supposed look.

               Frankly, this looks like something that might not need such a complex script to produce in the first place.

          • 2. Re: Correct placement of LOOP command
            AnthonyTesoriero

                 Thank you Phil,

                 I have played around with the placement of the loop command in the script as you suggested but to no avail. I completely understand how difficult it is to thoroughly diagnose without really knowing the way the DB is set up.

                 I've begun to think the error is in the set field command...

                 "AUC 0935861 sells on " & $aucDate & " at " & Left(GetAsText( Lien::Auction_h );Length(GetAsText( Lien::Auction_h ))-3) & " " & $location & $vehicle & " Re: " & Left ( $name ; Length ( $name ) -1) & "."

                 Look at the ad in the bottom right corner of the screen shot it seems like it is setting the feld with the proper text for the first record, but since the calculation ends at the variable  $name it just continues to fill in the additional lien holders instead of repeating the text requested in the set field command... 

                 Do you think that could be the issue and if so can you help with with the proper calculation language to complete the loop....

            • 3. Re: Correct placement of LOOP command
              philmodjunk

                   It begins with a standard sentence "AUC 0935861 sells on 1/30/14 at" the variable being the date... then proceeds to find data in related tables to name the time, vin number of a car and then to another related table to get the proper lienholder data as it relates to the record then it should loop back to the next record in the found set to get the time of the next auction, next vin number

                   This is the part that I could clearly see was not happening in the original version of the script. Your original script appears to be pulling up a found set of lienholder records for a single vehicle. But there is no code shown for looping through a set of vehicles to auction.

                   I'm guessing that for every vehicle to auction, there is one to many lienholders and thus you need two loops, one outside of the other. The outer loop would loop through vehicle records and the inner loop would loop through the lienholder records for each vehicle.

                   But note that I am guessing here and also note that I can think of ways to pull all this data together in one field, at least for one vehicle, without any script at all.

                   Some more guesses:  I think that your relationships may be similar to this:

                   Contact Management-----<Lien---<cust_lienholders

                   Contact Management::__pkContactID = Lien::_fkContactID
                   Lien::__pkLienID = cust_lienholders::_fkLienID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   And I would guess that Your script is performed on a layout based on Lien and you want to loop through a set of lien records to produce either a group of ads, one for each auctioned vehicle or a single ad that combines all the data from all the auctioned vehicles in to a single field.