locked
LightSwitch Phone Number Formats From An Imported Table RRS feed

  • Question

  • I have a simple Visual Studio LightSwitch Application that uses a copy of a Dynamics database that contains customer information. Dynamics formats the phone number as 00000000000000 so it can have an extension. Even selecting Phone Number as the data type in LightSwitch, it has no effect.

    The old way we viewed this database on the fly was a dump to a webpage and someone had made some code for me as follows:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CustomerDatabase01 %>" SelectCommand="SELECT [CUSTNMBR], [CUSTNAME], [CNTCPRSN], [ADDRESS1], [ADDRESS2], [CITY], [STATE], [ZIP], 
    case when len(ltrim(rtrim(PHONE1)))&gt;='10' AND len(ltrim(rtrim(PHONE1)))&lt;='13' then '('+SUBSTRING(PHONE1,1,3)+')'+' '+SUBSTRING(PHONE1,4,3)+'-'+SUBSTRING(PHONE1,7,4)
    when len(ltrim(rtrim(PHONE1)))&gt;='14' AND SUBSTRING(PHONE1,11,4) != '0000' then '('+SUBSTRING(PHONE1,1,3)+')'+' '+SUBSTRING(PHONE1,4,3)+'-'+SUBSTRING(PHONE1,7,4)+' ext.'+SUBSTRING(PHONE1,11,4)
    when len(ltrim(rtrim(PHONE1)))&gt;='14' AND SUBSTRING(PHONE1,11,4) = '0000' then '('+SUBSTRING(PHONE1,1,3)+')'+' '+SUBSTRING(PHONE1,4,3)+'-'+SUBSTRING(PHONE1,7,4)
    else PHONE1 end AS 'PHONE1'
    , 
    case when len(ltrim(rtrim(PHONE2)))&gt;='10' AND len(ltrim(rtrim(PHONE2)))&lt;='13' then '('+SUBSTRING(PHONE2,1,3)+')'+' '+SUBSTRING(PHONE2,4,3)+'-'+SUBSTRING(PHONE2,7,4)
    when len(ltrim(rtrim(PHONE2)))&gt;='14' AND SUBSTRING(PHONE2,11,4) != '0000' then '('+SUBSTRING(PHONE2,1,3)+')'+' '+SUBSTRING(PHONE2,4,3)+'-'+SUBSTRING(PHONE2,7,4)+' ext.'+SUBSTRING(PHONE2,11,4)
    when len(ltrim(rtrim(PHONE2)))&gt;='14' AND SUBSTRING(PHONE2,11,4) = '0000' then '('+SUBSTRING(PHONE2,1,3)+')'+' '+SUBSTRING(PHONE2,4,3)+'-'+SUBSTRING(PHONE2,7,4)
    else PHONE2 end AS 'PHONE2'
    , 
    case when len(ltrim(rtrim(FAX)))&gt;='10' AND len(ltrim(rtrim(FAX)))&lt;='13' then '('+SUBSTRING(FAX,1,3)+')'+' '+SUBSTRING(FAX,4,3)+'-'+SUBSTRING(FAX,7,4)
    when len(ltrim(rtrim(FAX)))&gt;='14' AND SUBSTRING(FAX,11,4) != '0000' then '('+SUBSTRING(FAX,1,3)+')'+' '+SUBSTRING(FAX,4,3)+'-'+SUBSTRING(FAX,7,4)+' ext.'+SUBSTRING(FAX,11,4)
    when len(ltrim(rtrim(FAX)))&gt;='14' AND SUBSTRING(FAX,11,4) = '0000' then '('+SUBSTRING(FAX,1,3)+')'+' '+SUBSTRING(FAX,4,3)+'-'+SUBSTRING(FAX,7,4)
    else FAX end AS 'FAX'
    , [PYMTRMID], [COMMENT1], [COMMENT2] FROM [RM00101] ORDER BY [CUSTNMBR]
    ">
    	</asp:SqlDataSource>
    This brought in the phone numbers with dashes and left off the last four for the extension.

    Since the original code was in ASP, I assume I need to modify it for LightSwitch (which is a Visual Basic project.) Someone graciously write that ASP code for me and looking for a little help now.

    Under the properties for the phone number validation, I see an option for customer formatting and it creates the start of a sub procedure:

    Private Sub PHONE1_Validate(results As EntityValidationResultsBuilder)
                ' results.AddPropertyError("<Error-Message>")
            End Sub
    So, I assume what I need to do goes in here but not sure how to get started to put it in a (000)-000-0000 format and leaving off the last 4 digits.

    "Novice" thanks in advance!

    Thanks...
    Brian

    Friday, February 7, 2014 4:29 PM

All replies

  • I will have a quick guess, if it works great, if not we can try again:

    PHONE1 = PHONE1.SubString(0, 10);

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Saturday, February 8, 2014 11:45 AM
  • I will have a quick guess, if it works great, if not we can try again:

    PHONE1 = PHONE1.SubString(0, 10);

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Thanks for the suggested. At this time, it had no effect. Number still displays as 00000000000000

    But, if I go to try to create a new customer in debugging mode, it will fail on that line of code you had me enter by saying NullReferenceException was unhandled by user code. 
    • Edited by Brian Wolters Monday, February 10, 2014 2:39 PM more info.
    Monday, February 10, 2014 1:55 PM