7 Replies Latest reply on Oct 9, 2014 3:50 PM by ezellkendrick

    Separate Field Contents On One Layout To Another Layout Creating A New Record For Each

    ezellkendrick

      Title

      Separate Field Contents On One Layout To Another Layout Creating A New Record For Each

      Post

      I have a field (Surnames) in a record on Layout 1, which can contain any number of words, there is no limit considering that the information in this field is imported from another source via excel. I need to separate each name into a new record on Layout 2.

      Usually, but not always, included after each name there is a location. Ideally I would like to separate the location into a “Location” field on the same new record with the corresponding “Surname,” which is in its own field. 

      Following are three different formats that I have imported and there could be other formatting variations separated by commas or other punctuations:

       

      1st formatting scenario:

      Mason (VA / KY) / Renick (WV / KS / KY) / Lewis (VA) / Cochran (KY) / Scott (KY / VA) / Walker (VA / PA / Scotland) / Rutherford (VA / PA / Scotland)

       

      2nd formatting scenario:

      Neal / Thomas / Gill / Jordan / Brown/ Sr / Fry / Pierson / Waters

       

      3rd formatting scenario:

      Thompson (Alabama) / Walpole (Louisiana) / Taylor (Louisiana) / Moore (Arkansas) / Wren (Louisiana) / Lindsay (Louisiana) / Purifoy (Alabama) / Wood (Alabama) / Stallings / Cook (Arkansas)

       

      Please advise.

        • 1. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
          philmodjunk

          The extreme inconsistency in data format will make this a challenge. I suggest you set up a different parsing algorithm for each format.

          And parsing data from "layout 1" to "layout 2" leaves out key details. You can have many layouts all based on the same table or the layouts can be based on different tables. Will Layout 1 be based on one table and Layout 2 based on another?

          And with the first scenario, will all the locations associated with the same surname be placed in the same field of the same record with that surname, or do you need one record for each unique combination of surname and location?

          and there could be other formatting variations separated by commas or other punctuations:

          And without a specific format to work from there will be no way to handle those "other punctuations" given that the surnames may or may not be paired with locations.

          • 2. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
            ezellkendrick

            Based on the inconsistency in data, if I have the algorithm template, I agree that I will setup different parsing algorithms for each scenario.

            Yes, Layout #1 is one table and Layout #2 is another table. 

            With the first scenario all locations with the same surname will be placed in the same field of the same record. It's not imperative that I have a separate record for each. However, the flexibility to do so would be a plus.

            I understand about the other punctuations, and would have to adjust that data accordingly to fit the parsing algorithm that is the most compatible or create another algorithm.

            • 3. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
              ezellkendrick

              I need help with the parsing algorithm to separate field contents on one layout to another layout based on the previous information in this string of posts.

              Thanks!

              • 4. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
                philmodjunk

                1st scenario only:

                SetVariable [$TheList ; value: Substitute ( Table1::Surnames ; ")" ; ¶ ) ]
                Go to Layout ["Table2" (Table2) ]
                Loop
                   Set Variable [$K ; $K + 1 ]
                   Exit Loop If [$K > ValueCount ( $TheList ) ]
                   Set Variable [$SubList ; Value: Substitute ( GetValue ( $TheList ; $K ) ; "(" ; ¶ ) ]
                   New Record/Request
                   Set Field [ Table2::Surname ; Trim ( Substitute ( GetValue ( $SubList ; 1 ) ; "/" ; "" ) ) ]
                   Set Field [Table2::Location ; Trim ( GetValue ( $SubList ; 2 ) ) ]
                End Loop

                All of the functions I've used can be looked up in FileMaker Help if they are unfamiliar.

                • 5. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
                  ezellkendrick

                  Thanks very much...works great!!! 

                  I would like to understand a couple sections of the script.

                  Set Variable [$K ; $K + 1 ]  Does this just set a variable that adds the number 1 to another section of the script as in the next step...

                  Exit Loop If [$K > ValueCount ( $TheList )  Is this saying the variable 1 is greater than the number of the items in the list and if so exit the loop?

                   I am asking this because, I would have just skipped the variable and just set it to exit loop if the 1 > ValueCount ( $TheList ) I am not sure if that works or not.

                  Also the next section: 

                  Set Variable [$SubList ; Value: Substitute ( GetValue ( $TheList ; $K ) ; "(" ; ¶ ) ] what does the variable $K do here? 

                  • 6. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
                    philmodjunk

                    Set Variable [$K ; $K + 1 ] adds one to $K each time it is executed. Since it is inside a loop, it increments $K by 1 each time the script loops through the steps enclosed in the Loop and End Loop steps. The fact that you didn't realize that the value of $K changes each time this part of the script repeats itself appears to be the main source of your questions on the other parts of the script.

                    Exit Loop If [$K > ValueCount ( $TheList ) ] will exit the loop when the value of $K is greater than the number of values present in $TheList. Keep in mind that $K increases by 1 each time that the script loops through these steps. It only has the value 1, the first time around. In other words, if there are 5 values in $TheList, the loop will execute 5 times before it exits. If there are 100 values, it will execute 100 times before exiting the loop.

                    Also the next section:

                    The very first line of the script, SetVariable [$TheList ; value: Substitute ( Table1::Surnames ; ")" ; ¶ ) ], replace the right parenthesis with a return character, ¶. This puts a list of values separated by returns into $TheList. Using your example data, the first part of the values in $TheList will look like:

                    Mason (VA / KY
                    / Renick (WV / KS / KY
                    / Lewis (VA
                    / Cochran (KY

                    GetValue returns the specified value from a list. So GetValue ( $TheList ; $K )  will return the text:

                    Mason (VA / KY

                    the first time the script loops ($K will be 1)

                    / Renick (WV / KS / KY

                    the second time the script loops ($K is now 2)

                    and so forth.

                    Note that the substitute and Trim functions used in the later lines that refer to $SubList remove all the unneeded space and / characters.

                    And I will repeat from my last post:

                    All of the functions I've used can be looked up in FileMaker Help if they are unfamiliar.

                     

                    • 7. Re: Separate Field Contents On One Layout To Another Layout Creating A New Record For Each
                      ezellkendrick

                      Thanks again. I did research on the scripting etc. I understood most of it, as you gathered  it was the function of $K + 1. That part of my research was not clear to me. The explanations were not as clear as yours...much thanks!!!