12 Replies Latest reply on Sep 2, 2011 2:44 PM by philmodjunk

    Populating field with (text) data from another field in same dbase

    TXCiclista

      Title

      Populating field with (text) data from another field in same dbase

      Post

      I have a rather large database with a particular field (call if Field B)  that is a long chunk of text. Within that text is nestled a line titled "SUBJECT: " That's followed by, appropriately enough, the subject of the text. The Subject line is never at a fixed location in the text, and the subject itself can be of any length.

       

      I would like to search Field B for "SUBJECT: " and then duplicate whatever comes after it (or even the word SUBJECT as well) to Field A. This way I could sort the table based on the Subject of the text (which I can't durrently do since it's so deeply nestled).

       

      Google searches, forums, even FM's own help pages, appear not to work, as the scripts I've run do nothing. I am not tied to any particular approach, so long as in the end I have one database, and Field B is "undisturbed" while Field A has only the text from the SUBJECT line.

       

      Thanks in advance.

        • 1. Re: Populating field with (text) data from another field in same dbase
          philmodjunk

          Does the word "subject" (in capitals or not) ever appear in the text that precedes this text marker? (That will greatly complicate what is otherwise a very simple calculation if it is possible.)

          Assuming that this is not possible:

          Let ( [ L = Length ( Field B ) ;
                    start = Position ( Field B ; "Subject" ; 1 ; 1 ) ] ;
                    Right ( field B ; L - Start + 1 )
                )

          Will include the word Subject as the start.

          Let ( [ L = Length ( Field B ) ;
                    start = Position ( Field B ; "Subject" ; 1 ; 1 ) ] ;
                    Trim ( Right ( field B ; L - Start - 7 + 1 ) )
                )

          Will omit the word subject. (trim removes leading and trailing space characters.

          There's quite a rich assortment of text functions available in FileMaker. You can look up "text functions" in FileMaker help to learn more.

          • 2. Re: Populating field with (text) data from another field in same dbase
            TXCiclista

            Thanks for the reply.

             

            Yes, it can be a bit more complicated (as you've intuited) because the phrase "SUBJECT: " can appear multiple times in a field. (If it didn't, I could fix this before importing the data). That said, when it does, it is identical. The line composed of "SUBJECT: <text fo subject here>" is identical throughout the field. Pulling the first ocurrence will always yield the correct result.

             

            Also, I believe the length in this case is the line ($line, no?) rather than the entire field. The field itself has many lines of text.

             

            Fictional example: Think of a database of e-mails, with fields "From, "To," "Subject," "Body." And in the "Body" field, a particular phrase "say, "Page x of yyy" occurs multiple times. The hope is to create a 5th column that would pull everything after "Page x of " and populate a new field named "Total pages" that's sortable. Let's also pretend (wouldn't hold in real life) that the "of yyy" is always unique to that field. While it may occur multiple times in the record, it will never occur again outside the record in the same form.

            • 3. Re: Populating field with (text) data from another field in same dbase
              philmodjunk

              The length function returns the length of the entire field, not just the line. The expression I gave returns all text from the first instance of "Subject" to the end of the field.

              I'm afraid I I can't picture your follow on description of what is really in the field. How about posting an actual sample of the text instead of trying to describe it in general terms?

              First you describe "Subject" and then your describe page x of YYY and those just aren't the same type of text patterns at all here.

              • 4. Re: Populating field with (text) data from another field in same dbase
                TXCiclista

                The data itself is not something I can share online (legal restrictions) but here's a random exmaple. I bolded "SUBJECT: " line so it stands out.

                 

                ---

                Cell A:

                wisi, aliquam dictum eu,

                iaculis ante, luctus et

                nunc sit amet, consectetuer

                SUBJECT: vulputate at, bibendum vitae, congue quis, commodo turpis velit,

                a venenatis nisl. Fusce porttitor, arcu quis arcu. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Etiam nunc sem, lobortis volutpat. Pellentesque nunc. Donec sodales eu, pretium cursus. In hac habitasse platea dictumst. Sed euismod nec, ligula. Fusce sed tortor. Ut bibendum sem, lobortis volutpat. Curabitur feugiat dui, eget wisi id eros. Pellentesque ante. Vivamus nibh. Morbi tellus dolor ut congue orci dui, accumsan faucibus. Aliquam fermentum orci mauris turpis, molestie justo elit, sit amet

                ---

                Cell B:

                Pellentesque ac tortor.

                Praesent quis leo.

                Quisque augue. Nam urna.

                Donec nec dui.

                SUBJECT: Pellentesque nunc fringilla eget,

                congue orci luctus id, congue eget, rutrum sollicitudin ac, ante. Lorem ipsum pretium justo. Pellentesque felis. Fusce purus consectetuer viverra sodales. Aenean dictum sem. Sed euismod non, tristique tempus nunc neque, malesuada leo in enim. Aliquam ultricies odio,

                SUBJECT: Pellentesque nunc fringilla eget,

                in metus. Integer mi facilisis neque, condimentum velit. Aliquam semper. Sed feugiat congue, turpis vitae wisi eu eros. Morbi ut quam ut leo eu nunc semper auctor. Nam ut eros pellentesque non, arcu. Etiam ut diam. In vehicula enim pharetra eget, ultricies odio,

                SUBJECT: Pellentesque nunc fringilla eget,

                fermentum libero

                ---

                 

                Notice the repeated Subject in Cell B. As they're identical, if the script only looked for the first occurence of "SUBJECT: " than moved on to the next field, it would be fine. Heck, even if it stayed in the field and kept overwriting "SUBJECT: " until the end, it would be OK as it would still be the same Subject.

                 

                Assume both cells are in the field "Body" and I want "SUBJECT: " line copied to field "Subject." It's even ok of if the "SUBJECT: " is copied as well, as I can later do a find & replace to remove it.

                • 5. Re: Populating field with (text) data from another field in same dbase
                  philmodjunk

                  Additional instances of "Subject" after the first aren't problem. It's figuring out how to skip over any that might need to be ignored is what would have complicated this issue.

                  Assuming you want only the text in bold and including Subject, but please note that omitting it requires a very minor tweak:

                  let ( [ Text = YourTextField ;
                           L = Length ( Text ) ;
                           Start = Position ( Text ; "Subject:" ; 1 ; 1 ) ;
                           End1 = Position ( Text ; ¶ ; 1 ; start ) ;
                           End = If ( End1 ; End1 ; L )
                          ]
                           Middle ( text ; start ; End - start + 1 )
                       )

                  If I haven't introduced a typo somewhere, you should be able to copy and paste this expression into your calculation dialog and then just change YourTextField to the name of you text field.

                  Note the End1, End calculations allow for the possibility that the line in bold might be the end of the text in your field and with no return at the end of the line.

                  • 6. Re: Populating field with (text) data from another field in same dbase
                    TXCiclista

                    I keep getting the "An operator is expected here" error at the final bracket (before "Middle").

                    • 7. Re: Populating field with (text) data from another field in same dbase
                      TXCiclista

                      I tested removing the two endo conditionds and only using one and the operator error went away, but then I got "List usage is not allowed in this calculation."

                      • 8. Re: Populating field with (text) data from another field in same dbase
                        philmodjunk

                        needs an semi colon after the right bracket...

                        let ( [ Text = YourTextField ;
                                 L = Length ( Text ) ;
                                 Start = Position ( Text ; "Subject:" ; 1 ; 1 ) ;
                                 End1 = Position ( Text ; ¶ ; 1 ; start ) ;
                                 End = If ( End1 ; End1 ; L )
                                ] ;
                                 Middle ( text ; start ; End - start + 1 )
                             )

                        • 9. Re: Populating field with (text) data from another field in same dbase
                          TXCiclista

                          OK, great. I'll post back if anything else goes wrong. So far, calculation is accepted.

                          • 10. Re: Populating field with (text) data from another field in same dbase
                            TXCiclista

                            Well, it's finding the "SUBJECT:" text just fine, and pasting just fine, but it's insisting on pasting the entire rest of the cell. Using my hypothetical above, it would paste cell A as such:

                             

                            SUBJECT: vulputate at, bibendum vitae, congue quis, commodo turpis velit,

                            a venenatis nisl. Fusce porttitor, arcu quis arcu. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Etiam nunc sem, lobortis volutpat. Pellentesque nunc. Donec sodales eu, pretium cursus. In hac habitasse platea dictumst. Sed euismod nec, ligula. Fusce sed tortor. Ut bibendum sem, lobortis volutpat. Curabitur feugiat dui, eget wisi id eros. Pellentesque ante. Vivamus nibh. Morbi tellus dolor ut congue orci dui, accumsan faucibus. Aliquam fermentum orci mauris turpis, molestie justo elit, sit amet

                             

                            It's not stopping at the end of the line. :(

                            One thing that might help is that I don't need to check to see if the last line is "SUBJECT:" Even if it was, I would want to know that as it would be an anomaly. A blank cell would be a good sign something was wrong.

                            • 11. Re: Populating field with (text) data from another field in same dbase
                              TXCiclista

                              I think the problem is likely that the paragraph mark is at the end of the cell and it's just line returns in between. Is "$line" the command to go to the end of the line and stop?

                              • 12. Re: Populating field with (text) data from another field in same dbase
                                philmodjunk

                                No, $line, in filemaker would be the name of a variable.

                                ¶ in FileMaker is the operator FileMaker uses to identify carriage return characters. It's certainly possible that a different character marks the end of the line.

                                Try this experiment. Select the text after the comma in velit, and before the start of the next line. Copy and paste it into a text field. Use this calculation to identify its character code:

                                Code ( textfieldyoupastedinto here )