11 Replies Latest reply on Feb 26, 2009 11:00 AM by Jade

    calculation help with listing a repeating field

    Thicks

      Title

      calculation help with listing a repeating field

      Post

      I am trying to list a repeating field in a form letter that I am creating. The fields that I need to use are : "EmployeesFirstName" and "EmployeesLastName" I only want to show repetitions 1,2, and 3. I have 15 repititions in all. Someone helped me come up with this calculation by how can I choose the repetitions

       

      Substitute ( List ( RepeatingField ) ; ¶ ; ", " )

        • 1. Re: calculation help with listing a repeating field
          raybaudi
            

          Substitute ( TrimAll ( RepeatingField[1] &" "& RepeatingField[2] &" "& RepeatingField[3] ; 1 ; 1 ) ; " " ; ", " )

           

          But think at least 2 times before using repeating fields to store data.

          • 2. Re: calculation help with listing a repeating field
            Thicks
              

            The comma si still not showing after the eployees name, everything else works.

             

            Also why do you warn against using repeating fields?

             

            Thank you

            • 3. Re: calculation help with listing a repeating field
              Jade
                

              To get your commas back, remove the "TrimAll".

               

              Briefly, repeating fields are not used because:

              • they are difficult to access and manipulate as you are experiencing;

              • they are unstructured (that means that only you know what and how many things they contain);

              • they are not normalized (that means that data is probably duplicated).

               

              In the future, whenever you are tempted to use them, then you should consider creating another related table instead. 

               

               

              • 4. Re: calculation help with listing a repeating field
                raybaudi
                  

                1) I do not know why you do not see the comma... my computer let me see it.

                2) repeating fields aren't good to store data, but they are a beautifull tool for programming purpose.

                You have to store your preciosus data into single fields, one information into one field.

                 

                • 5. Re: calculation help with listing a repeating field
                  raybaudi
                    

                  Jade wrote:

                  To get your commas back, remove the "TrimAll".


                  No, TrimAll is needed. ( it Trim only more than a space )


                  • 6. Re: calculation help with listing a repeating field
                    Thicks
                      

                    Substitute ( TrimAll ( EmployeeFirstName[1] &" "&EmployeeLastName[1] & " " & EmployeeFirstName[2] &" " & EmployeeLastName[2] & " "& EmployeeFirstName[3] & " " & EmployeeLastName[3] ; 1 ; 1 ) ; " " ; ", " )

                     

                    It is giving me a comma after EployeeFirstName and EmployeeLastName

                     

                    Ex: Bill, Waters

                     

                    Im trying to get a comma AfterEmployeeLastName

                     

                    ex: Bill Waters,

                     

                    The reson I am doing this is because I am having the database create a letter that is to be mailed out and it meeds to list parties involved.

                    employee repeating field 1-3 are my complainants

                    4-6 complaining witnesses

                    and 7-10 respondents

                    • 7. Re: calculation help with listing a repeating field
                      raybaudi
                        

                      Uhhh... 2 repeatings ??

                       

                      Live all and go on with a related table NOW that you are in time.

                      • 8. Re: calculation help with listing a repeating field
                        Thicks
                          

                        Actually I think my problem uis with the FullNameEmployee field that I created I made it a calculation

                        EmployeeFirstName  &" "  &   EmployeeLastName

                         

                        Then I placed it in thecalculation you gave me

                         

                        Substitute ( TrimAll (NameFull[1] &" "& NameFull[2] &" "&NameFull[3] ; 1 ; 1 ) ; " " ; ", " )

                         

                        And I am getting a comma between every word

                         

                        Ex: Bill, Waters, George, Washington

                         

                        Any suggestions

                        • 9. Re: calculation help with listing a repeating field
                          Jade
                            

                          Ok, if you promise us that you will do as Daniele rightly suggested (i.e. change this to related table) later:

                           

                          NameFull[1] & if(isEmpty(NameFull[2]) ; " " ; ", " ) &

                          NameFull[2] & if(isEmpty(NameFull[3]) ; " " ; ", " ) &

                          NameFull[3]

                          • 10. Re: calculation help with listing a repeating field
                            Thicks
                               what do you mean a related table? How?
                            • 11. Re: calculation help with listing a repeating field
                              Jade
                                

                              Thicks wrote:
                              what do you mean a related table? How?

                              This is not trivial and it will take some time to do…
                              Simply put, all the names you now have in the repeating fields on your first table (I'll call it table 'A' ) should be stored in a separate table (I'll call this one table 'B').
                              Each record in table B should identify one person (regardless if he/she is a plaintiff, witness, or respondent).  In addition to the first and last name you should also add a field to uniquely identify that person (like person_id that is auto incrementing and unique).  You can also put their addresses, etc. here.
                              Now you must join these two tables (A <=> B) together somehow using the Manage Database…Relationship.  
                              When you think about it a while, you might have situations where one person is a plaintiff on one case and a respondent on another (in counter-suits).  So you need to create a many-to-many relationship.  This takes a third table ('C').  In table C, you will need to add 3 fields: the person_id from table B; the case_id from table A; and the status of the person (plaintiff, witness, or respondent).  So the final relationship is (A<=>C<=>B).
                              Hope this helps…