3 Replies Latest reply on Mar 27, 2009 11:57 AM by philmodjunk

    Assign Same ID Number to Multiple Cases and Getting Found Set List

    Jerten81

      Title

      Assign Same ID Number to Multiple Cases and Getting Found Set List

      Post

      I have two problems I'm looking for help with both coming from the same scenario.  For the sake of an example let's say I have a database of NFL players and I want to send an email regarding all players in a current found set.  I want to be able to automatically assign a serial number "EMAIL ID" to each player in the found set i.e. my found set contains the players Tom Brady and Peyton Manning and I want to send an email to regarding both.  Is there a way that I can automatically assign the same ID number to all records in a given found set?  Secondly, I'm having a problem getting the email itself formatted the way I want it.  I want the email to look like the below example:

       

      RE:

      Tom Brady

      Peyton Manning

       

      \\Email Text\\

       

       

      I have no problem pulling the email text from a global field but i can not figure out how to list the entries in a found set as formatted above.  I've tried using the List(Fieldname) function but it only lists the current record when I use this i.e. just Tom Brady.  When I tried to modify it to use a related table i.e. List(Related::Fieldname) it lists all values in the field and not just the ones from the found set.  Is there a way I can get a list of the players in the current found set listed one after the other with carraige returns as above? 

       

      Thanks. 

        • 1. Re: Assign Same ID Number to Multiple Cases and Getting Found Set List
          philmodjunk
            

          Step 1:

           

          Look up Replace Field Contents in the on line help file. This will do what you want and can be done manually or in a script. It has several different options, so it's a good idea to read up on it before you use it.

           

          You can simply put a constant like "1" in all the fields of your current found set or use a more sophisticated option.

           

          Step 2:

           

          There are several options for building a text list of values from multiple records while separating the values with carriage returns. Learning these tricks can be very useful to the developer since these fields can enable very powerful relationships.

           

          Here's the method I prefer:

           

          In your case, let's call your Field from step 1, EmailGroup and make it a number field.

          Create a self join relationship linking EmailGroup to EmailGroup of the same table.

          Call the new Table Instance "SameGroup".

          Now define a value list with the following options:

           

          Specify Field: OriginalTablename:: PlayerName

          Show only related values: SameGroup

           

          Name this value list GroupNames.

           

          Now the function: ValueListItems ( Get(fileName) ; "GroupNames" )

          Will return the list you want of all players with the same email group number as the current record.

          You can use this expression in a calculation field that returns text or use it in a Set Field step to assign the names to a global field.

           

          Make sense?

          • 2. Re: Assign Same ID Number to Multiple Cases and Getting Found Set List
            Jerten81
               Thanks for the help PhilModJunk.  Your solution for the value list works well.
            • 3. Re: Assign Same ID Number to Multiple Cases and Getting Found Set List
              philmodjunk
                

              The methods described have their draw backs also.

               

              Replace is not a "safe" method to use if the records it tries to change are being edited. If a user has their cursor in a record's field, it's locked and Replace will skip that record and tell you that one or more records were locked, but you have no way to know which records didn't get the update.

               

              ValueListItems() will return an empty string if either the filename or the value list name is even one character different from their actual names. Get(Filename) let's you avoid problems if you change the name of your database file, but if you change or mis-type the name of your value list, you won't get an error message, just an empty list.

               

              Still, it's elegant and it works, you just have to be careful.