8 Replies Latest reply on Feb 25, 2010 12:09 PM by mark_d2x

    not allowing commas to be used in text

    mark_d2x

      Title

      not allowing commas to be used in text

      Post

      Hi another one I am afraid.

       

      I have a text field that is used for a csv upload.  If someone enters text with a comma in the field it will make uploading the data impossible.  Is there a way of not allowing commas to be used in that particular field?

       

      mark 

        • 1. Re: not allowing commas to be used in text
          mrvodka
             You can use a script trigger with OnObjectKeystroke to skip the character or you can allow the comma but use an auto-enter by calculation ( Do not Replace unchecked ) to sustitute it out for nothing. Substitute ( Self; ","; "" )
          • 2. Re: not allowing commas to be used in text
            comment_1
              

            Mark_d2x wrote:

            I have a text field that is used for a csv upload.  If someone enters text with a comma in the field it will make uploading the data impossible.


            Why is that? The csv format is perfectly capable of handling in-field commas.


            • 3. Re: not allowing commas to be used in text
              mark_d2x
                

              Hi, the reason is that the commas are used by the system i upload the data to. I am still doing the csv manually as it isn't quite a straight forward data dump everything also needs to be within "" eg "Fred","Bloggs","Male" etc it justs rejects the whole file otherwise as the data items are then out of sequence.

               

              I have to admit i am struggling with mr_vodkas suggestions;

               

              You can use a script trigger with OnObjectKeystroke to skip the character or you can allow the comma but use an auto-enter by calculation ( Do not Replace unchecked ) to sustitute it out for nothing. Substitute ( Self; ","; "" )

               

              With, OnObjectKeystroke it wants a script and I am confused by the auto enter - do you mean set up a second field?

               

              Mark 

              • 4. Re: not allowing commas to be used in text
                comment_1
                  

                Mark_d2x wrote:

                am still doing the csv manually


                I would concentrate on this part of the problem, instead of forcing restrictions upon the users (it can be quite distracting to see all your commas disappear as you exit the field!). But I still don't understand the problem: the built-in csv export does put quotes around field data - that is precisely how in-field commas are made possible.

                 


                • 5. Re: not allowing commas to be used in text
                  mark_d2x
                    

                  Have tried and the upload wont work; they are very specific about not having any other commas in the text......

                   

                  I agree about seeing commas disappear - not sure what else to do

                   

                  mark 

                  • 6. Re: not allowing commas to be used in text
                    philmodjunk
                      

                     "I agree about seeing commas disappear "

                     

                    A slightly friendlier variation on Mr Vodka's suggestion might be to substitute ; for , instead of making them disappear.

                     

                    You can also set a validation rule that makes the user edit out the commas instead of doing it for them.

                    • 7. Re: not allowing commas to be used in text
                      comment_1
                        

                      Mark_d2x wrote:
                      not sure what else to do

                      I'll tell you what I would do: I would leave MY data as is, and not conform to some wise guy's idea of putting out a contract on a perfectly legitimate character.

                       

                      Then I would devise an export routine to satisfy their limitations; this can be done by:

                      (a) exporting calculation field/s instead of real data; or

                      (b) exporting as XML with a XSLT stylesheet doing the cvs AND elimininating in-field commas*; or

                      (c) post-processing the exported file using AppleScript.

                       

                      Finally, I would tell the receiving party what I think of their "solution".

                       

                       

                      ---

                      (*) Normally, this would be my preferred solution and the only one I would consider; however, due to Filemaker's poor implementation of XSLT there are some limitations when doing a custom cvs export.

                      • 8. Re: not allowing commas to be used in text
                        mark_d2x
                          

                         

                        Ok will tell Her Majesty's Government!

                         

                        Think a calcualtion field that replaces the commas with semicolons may be the best trick then use the calculated field for the export.

                         

                        Mark