5 Replies Latest reply on Jun 9, 2015 8:47 AM by Stu412

    Delete a record if Field A is text or blank

    Stu412

      Title

      Delete a record if Field A is text or blank

      Post

      I'm working on a data import routine and it needs to be built to the lowest denominator of ease of use.  On the import file, which I don't want to have users changing, one of the columns I will rely on contains a mix of codes (which are numeric and needed) and text (which can be removed).

      It so happens that each record on the import with a text entry in this particular field is removable.

      I'm thinking of a calc which would evaluate this column on import, something like "If Code is not number, then delete record".

      Simple question - in a data field that could contain text or a number, how do I test for either?

      Thanks

       

        • 1. Re: Delete a record if Field A is text or blank
          philmodjunk

          If by "numeric", you mean only the digits 0 thru 9:

          Length ( self ) = length ( Filter ( self ; 9876543210 ) ) and not IsEmpty (self )

          Will only be true if the characters in self are only those digits. You can add a - and a decimal characterif other numeric values are possible and you can use trim if you need to ignore leading or trailing spaces.

          You can set this up as a validation rule with the "validate always" option and then these records will be automatically blocked from import and will thus never appear in your found set of newly imported records.

          • 2. Re: Delete a record if Field A is text or blank
            Stu412

            I like that, very slick.  Couple of questions:

            How can it be amended to also ignore blanks?  

            The import summary says there are 4 errors (which relate to the rows not imported) - does this mean I've got something set wrong somewhere?

            Thanks

             

            • 3. Re: Delete a record if Field A is text or blank
              philmodjunk

              The "four errors" means that it's working. Those are the 4 records omitted from the import due to the data failing the above validation test. If you look at my last post again, you'll see that it also omits records when self is empty. I added this detail shortly after my first response was posted so if you are reading my responses by email, you would not have seen that correction.

              • 4. Re: Delete a record if Field A is text or blank
                Stu412

                Sorry to ask a further question - I've got this working nearly how I want it, although there is an exception use case.

                I need to exclude all blanks, all text EXCEPT the word 'Code' (this record on each import, which will always be the same, contains information which is required.)

                I've tried amending the calculation above and also adding an OR to it to allow the record containing 'code' to be imported, but this has not worked so far.

                If I can get this done, I'll have a basis for a near idiot proof import routine 

                Thanks in advance

                 

                • 5. Re: Delete a record if Field A is text or blank
                  Stu412

                  Sorted - thanks anyway!