locked
Formatting Phone Numbers From SQL / Great Plains Table To Lightswitch HTML5 Application RRS feed

  • Question

  • I have a very simple Lightswitch HTML5 application that dumps customer information from the RM00101 table in Great Plains to a webpage for easy, read only access for my employees. I've set it up in Lightswitch so it only shows what they need when they go into a details screen. 

    One complaint they always have is that phone numbers are in a 00000000000000 format and look ugly. 

    With the assistance of a trusted friend, he came up with this:

    SELECT
    CASE RTRIM(PHONE1)
    WHEN '00000000000000' THEN ''
    WHEN '' THEN ''
    ELSE '(' + SUBSTRING(PHONE1, 1, 3) + ') ' + SUBSTRING(PHONE1, 4, 3) + '-' + SUBSTRING(PHONE1, 7, 4)
    + CASE WHEN RIGHT(RTRIM(PHONE1), 4) <> '0000' THEN ' x' + RIGHT(RTRIM(PHONE1), 4) ELSE '' END
    END AS PHONE
    FROM RM00101
    

    The problem I am having is where do I put this in the Lightswitch project?

    Thanks...

    Monday, August 22, 2016 9:18 PM

Answers

  • Brian, since you are only displaying the phone number (not editing them) it should be fairly easy to try one of the following methods:

    1. Create a database view using the above SQL and then consume that view from LightSwitch in the same way as you consume the Table at present - the difference being that you now have the phone number in a formatted way. 

    2. The following might be even better, provided you can find someone to help you write a bit of javascript to format the phone number in the same way as your friend above did using SQL:

    - select the display field node in the screen designer (probably a Label in either in the Table or List or Details screen)

    - in the properties on the right side click the 'Edit PostRender Code' link (just below the Description field) and change the implementation of that event to something like the following:

    myapp.MyScreen.MyField_postRender = function (element, contentItem) { contentItem.dataBind(contentItem.bindingPath, function (value) { // 1. value will contain the unformatted phone number as a string // 2. you have to write some javascript to take the unformatted number and // reformat it into your desired format and then display that if (value) var unformattedPhone = value; var formattedPhone = ... do some javascript phone formatting to unformattedPhone... $(element).text(formattedPhone); } }); };


    Hope this gives you some more options.


    Regards, Xander. My Blog

    • Edited by novascape Thursday, August 25, 2016 6:30 AM
    • Proposed as answer by Angie Xu Thursday, September 1, 2016 8:38 AM
    • Marked as answer by Angie Xu Thursday, September 1, 2016 8:38 AM
    Thursday, August 25, 2016 6:17 AM

All replies

  • Hi,

    For Phone Number you can supply additional formats it should validate against and in what order, is this available for you below?

    Regards,

    Angie


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, August 24, 2016 8:36 AM
  • Hi,

    For Phone Number you can supply additional formats it should validate against and in what order, is this available for you below?

    Regards,

    Angie


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Yes...this is one of the first things I tried...but it didn't work...phone number fields in Great Plains look like this:

    Wednesday, August 24, 2016 6:02 PM
  • Brian, since you are only displaying the phone number (not editing them) it should be fairly easy to try one of the following methods:

    1. Create a database view using the above SQL and then consume that view from LightSwitch in the same way as you consume the Table at present - the difference being that you now have the phone number in a formatted way. 

    2. The following might be even better, provided you can find someone to help you write a bit of javascript to format the phone number in the same way as your friend above did using SQL:

    - select the display field node in the screen designer (probably a Label in either in the Table or List or Details screen)

    - in the properties on the right side click the 'Edit PostRender Code' link (just below the Description field) and change the implementation of that event to something like the following:

    myapp.MyScreen.MyField_postRender = function (element, contentItem) { contentItem.dataBind(contentItem.bindingPath, function (value) { // 1. value will contain the unformatted phone number as a string // 2. you have to write some javascript to take the unformatted number and // reformat it into your desired format and then display that if (value) var unformattedPhone = value; var formattedPhone = ... do some javascript phone formatting to unformattedPhone... $(element).text(formattedPhone); } }); };


    Hope this gives you some more options.


    Regards, Xander. My Blog

    • Edited by novascape Thursday, August 25, 2016 6:30 AM
    • Proposed as answer by Angie Xu Thursday, September 1, 2016 8:38 AM
    • Marked as answer by Angie Xu Thursday, September 1, 2016 8:38 AM
    Thursday, August 25, 2016 6:17 AM
  • If you aren't sure how to do the "do some javascript phone formatting" line in Xander's example, You could probably replace that line to do a regular expression replacement with a JavaScript regex like this:

    var regexObj = /([0-9]{3})([0-9]{3})([0-9]{4})([0-9]{4})/i;
    if (regexObj.test(unformattedPhone)) {
       if (unformattedPhone.slice(-4) == "0000") {
         var formattedPhone = unformattedPhone.replace(regexObj, "($1) $2-$3");
       } else {
         var formattedPhone = unformattedPhone.replace(regexObj, "($1) $2-$3 Ext. $4");
       }
    } else {
        // Invalid phone number
    }

    Hope this helps a little bit

    Regards,

    RT Watkins


    • Edited by RT Watkins Friday, August 26, 2016 1:56 PM typo
    Friday, August 26, 2016 1:55 PM