5 Replies Latest reply on Mar 17, 2016 9:37 AM by DamianKelly

    Sub-summary for postcode region(s)

      I'm looking to create a sub-summary when sorted by definition on a list view that I have, relationship:

       

      Clients::pkClientId ---------< Requests::fkClientId

       

      Essentially, I have a list view which I'd like to sort by postcode region

       

      Client postcode example

      • BA2 5RQ
      • BA3 4SM
      • BS13 0WL

      I only want to sort them by the region so (BA2, BA3, BS13) - is there a way to achieve this?

       

      BA2

      Request 1

      Request 2

      Request 3

      Request 4

       

      BS13

      Request 1

      Request 2

      Request 3

      Request 4

        • 1. Re: Sub-summary for postcode region(s)
          johnbuckingham

          Hey Kyler

           

          Did you consider a calculated field like; _cOutbound = getvalue ( substitute ( postcode ; space ; pilcrow ) ; 1 ) to get the outbound component of the postcode, then sort by that? It may need a little more than stated in case the postcode was incomplete or badly keyed, or was for an address in a country which has a more logical zip coding than we endure in the UK. But in principal, I think that would do what you require!

           

          JB

          • 2. Re: Sub-summary for postcode region(s)

            Thanks johnbuckingham

             

            So in full my calculation would be: _cPostcodeOutbound = GetValue ( Substitute ( Postcode ; " " ; " ") ; 1)

             

            What would you put in the fields you've titled (space) and (pilcrow) ?

            • 3. Re: Sub-summary for postcode region(s)
              johnbuckingham

              Hey Kyler

               

              The 'space' would be just that; " " (double inverted commas, space, double inverted commas).

               

              The one entitled pilcrow is a little more complex; 'pilcrow' is the the name for the 'return' character; its in the calculation operators in the Script Workspace; looks like a reversed uppercase 'P'. I believe that you don't always need to enclose it in inverted commas, but I always do.

               

              It would be prudent to check that there is at least one space in case its a non-UK zip code in which case don't do the truncation; so a better calculation might be:

               

              Let ( cleaned = upper ( trim ( postcode ) )  ; if ( patterncount ( cleaned ; space ) > 0 ; getvalue ( substitute ( cleaned ; space ; pilcrow ) ; 1 ) ; postcode ) )

               

              I hope that's a bit clearer? I'll bet some of the results will be weird to start with - usually this may be down to picnic (PICNIC - Wiktionary)...

               

              Let me know how you get on!

               

              JB

              • 4. Re: Sub-summary for postcode region(s)

                This worked a treat - thanks johnbuckingham

                 

                I only ran into a few problems thanks to PICNIC!

                • 5. Re: Sub-summary for postcode region(s)
                  DamianKelly

                  Just a slight heads up, that works fine with the space in the right place. The outward code, the bit you want is everything bar the last three characters (the inward code) so you could try:

                   

                  Let(

                  postcode = table::yourPostcodeField

                  ;

                  Trim(Left(Postcode; length(postcode) - 3))

                  )

                   

                  All you need to do is swop out the table::yourPostcodeField for the one in your solution

                   

                  That might fix the PICNIC issue.......