12 Replies Latest reply on Nov 26, 2014 2:17 PM by ShunichiMuto

    Remove first four characters from front of a text field

    dannyred

      Title

      Remove first four characters from front of a text field

      Post

      I need to remove the first 4 characters of a text field. Specifically, "<li>" ie.  the html code for a "list item"

       

      I cannot use find/replace because the same "<li>" characters also appear later in the same field and those are needed. Just the first occurrence needs to be stripped (removed)

       

      I have struggled with substitute and failed and looked into the character parsing functions like 'left' - but these remove all the text after the first 4 character and I need to do the reverse.

       

      Data in the field (product specifications) varies from record to record

       

      Data is typically entered into the field via csv file import. Sometimes data is just entered from scratch

       

      Thanks in advance

        • 1. Re: Remove first four characters from front of a text field
          raybaudi
            

          Hi dannyred

           

          what about using the Middle ( ) function ?

           

           

          Middle ( product specifications ; 5 ; Lenght ( product specifications ) )

          • 2. Re: Remove first four characters from front of a text field
            KIDO
               <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

            Try the following

             

            Select your field in MANAGE DATABASE and in the Auto-Enter define Calculated value and enter the following calculation:

             

            Case ( Left( YourFieldName; 4) = “<li>” ); Right( YourFieldName; Length( YourFieldName ) - 4)

             

            Click OK and deselect the box “Do not replace field contents (if any) and click OK

             

            When you import your records only the leading “<li>” will be removed.

             

            • 3. Re: Remove first four characters from front of a text field
              LaRetta_1
                

              A small thing but ... your calculation won't work as written, Kido.  You have a closing Case() parenth  at the end of the "<li>" and you have no default result if there isn't <li>  in the field.  I would suggest a small adjustment:

               

               Case ( Left ( YourFieldName ; 4) = "<li>" ; Right ( YourFieldName ; Length ( YourFieldName ) - 4 )  ; YourFieldName )

               

              It doesn't appear that there will be a space between that leading <li> and the start of text but if there is that possibility, I would wrap the entire calculation with Trim() as well so the line will never start with a space.

               

              UPDATE:  I like this calculation approach better than using Middle() because that approach always assumes every line starts with <li> which may be true but any time we can bypass an assumption, the better the calculation.

               

              UPDATE2:  If using this on import, consider showing all records first (if necessary) and you would 'Update and AddTo' and remember to check 'Perform Auto Enter' as well.

              • 4. Re: Remove first four characters from front of a text field
                KIDO
                   <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                Thank you LaRetta for your observations, my mistake.

                 

                The right expression is:

                 

                Case ( Left( YourFieldName; 4) = “<li>” ; Right( YourFieldName; Length( YourFieldName ) - 4)).

                I do apologise to dannyred and the forum for that. Usually I use extra care when typing as I'm disabled and press too many keys at a time.

                The default result is implicit – it's the field itself. If the calculation evaluates to FALSE nothing happens. There's no need to rewrite the field contents.


                Best Regards.

                 

                • 5. Re: Remove first four characters from front of a text field
                  LaRetta_1
                    

                  Kido said, "If the calculation evaluates to FALSE nothing happens. There's no need to rewrite the field contents."

                  Not quite true, I'm afraid.  If the calculation evaluates to FALSE, something indeed happens.  The Case() calculation will produce a NULL result (or blank) because you have 'Do not Replace Existing value' unchecked.  You must explicitly specify the field again as the default so the value remains if it does not meet the first test.

                   

                  Of course dannyred will discover this when they apply your calculation (versus mine).  But I hope they back up first.

                  • 6. Re: Remove first four characters from front of a text field
                    KIDO
                       <!--            @page { margin: 2cm }           P { margin-bottom: 0.21cm }        -->

                    Dear LaRetta

                     

                    If the box “Do not replace existing values (if any)” is unchecked, and the Case() evaluates to NULL,the importing data still occurs, right?  - And data will be kept unchanged.

                     

                    I don't think that you were suggesting that the field contents would be replaced with a blank string in case the calculation evaluates to FALSE.

                     

                    My calculation has been tested. It works!

                     

                    • 7. Re: Remove first four characters from front of a text field
                      LaRetta_1
                        

                      Kido said, "...evaluates to NULL,the importing data still occurs, right?  - And data will be kept unchanged."

                       

                      WRONG.   Auto-enter applies after the data hits the field.  So the data is imported but then auto-enter is applied and the data will be removed!!

                       

                      I do not know what to say.  If 'Do not Replace' is unchecked and there is not a default value, it will wipe out what is in the field - whether you are importing (and applying 'perform auto enter' ) or simply typing into the field!!

                       

                      I will be happy to PM you and send you a file.  Or you can send me one (since we can't attach here).  Or maybe others can confirm the behavior of an auto-enter calculation (with 'do not replace' unchecked) on field data.  It is a known behavior and not something I am simply making up.

                       

                      Try this:  Create a text field and attach an auto-enter calculation (your calculation) of:

                       Case ( Left( text; 4) = "<li>" ; Right( text; Length( text ) - 4)  )

                       

                      Uncheck 'Do Not Replace Existing Value' and then try typing directly into the field.  The data will keep being removed.  Import is no different because auto-enter is applied to a field after the data imports.

                       

                      I will not continue to try to convince you.  We are not here for you.  Truth will speak for itself.

                      • 8. Re: Remove first four characters from front of a text field
                        LaRetta_1
                          

                        I think you are misunderstanding that we might have existing records (or data in that field) and we might be updating.  If we are always ONLY ADDING new records, then you are correct but ONLY if the field begins with <li>.  If it does not, the field will blank, as will any incoming new records without the <li> at the beginning.

                         

                        And it is guaranteed that if you manually make any correction to the field (with YOUR auto-enter attached), the field will blank on you.   Might this be the inconsistency we are bumping against?  In general and ALWAYS, the field should include itself as the default value or it will blank out.

                        • 9. Re: Remove first four characters from front of a text field
                          KIDO
                             I understood from dannyred post that data is imported via csv file. If dannyred needs to amend records on that field, then the box "Do not replace field contents (if any)" must remain checked.
                          • 10. Re: Remove first four characters from front of a text field
                            LaRetta_1
                              

                            You are unbelieveable.  If dannyred leaves 'Do not replace' CHECKED, then the auto-enter will not work and the calc will NOT remove any beginning <li> when imported or manually so you have no solution whatsoever!!   Here are some facts for you:

                             

                            1) dannyred said, "Data in the field (product specifications) varies from record to record."

                            2) dannyred said, "Data is typically entered into the field via csv file import."

                            3) But he also said, "Sometimes data is just entered from scratch."

                            4) We can not guarantee that all imported data will contain <li> at the beginning of every record.

                            5) There may be a 'Perform auto-enter' performed on these records in the future, aside from this import.

                            6) In all instances except new incoming records WITH a leading <cl>, the field will blank itself out any (and EVERY time) the data is changed) and this is VERY VERY BAD!!

                             

                            Using MY calculation (which enters the field again as the default) will WORK because it follows a basic principle of understanding FileMaker.  I guess this is difficult for you to grasp because it seems that you aren't even creating a test file and certainly haven't tested my calc nor my premise but here's an important detail:


                            PRINCIPLE:  If you have an auto-enter (with 'do not replace existing' unchecked), then you MUST put the field again as the default result to keep the data from wiping out (producing null) if the prior test(s) fail.  Use Auto-Enter (uncheck 'Do Not Replace Existing...') and the calculation of:

                             

                            Case ( Left( text; 4) = "<li>" ; Right( text; Length( text ) - 4) ; text  )

                            • 11. Re: Remove first four characters from front of a text field
                              dannyred
                                

                              wow.... looks like i may have contributed to a political debate....

                               

                              On a serious note - I want to thank everyone for taking the time to respond to my query and I apologize for the delay in reviewing all the contributions.

                               

                              I just tried the last formula that appears ie: Case ( Left( text; 4) = "<li>" ; Right( text; Length( text ) - 4) ; text  )

                               

                              I created a 'test' database where I can play. Created 1 input field for the raw data and a second field to test the calculation. Instead of an import via csv - i just copied / pasted the data for one record into the input field and - sure enough - the calculation field result was exactly what I needed. 

                               

                              When I have a minute - I will experiment with some of the other formulae - as it will help me to understand the principles behind the solutions.

                               

                              I also did a simple workaround in excel.

                               

                              Thanks again for spending this amount of time on this puzzle.

                               

                              Dan 

                              • 12. Re: Remove first four characters from front of a text field
                                ShunichiMuto

                                Just use Substitute function to change "<li>" into blank.

                                 

                                http://www.filemaker.com/help/html/func_ref3.33.84.html

                                 

                                The script should be:

                                 

                                Substitute ( Your Field ; Left ( Your Field ; 4 ) ; "" )

                                 

                                This should erase "<li>" in your field of all records.