1 Topic by joshuA (edited by joshuA 2019-12-27 14:02:32)

  • Registered: 2019-12-24
  • Posts: 169

Topic: Output for phone numbers and other text formats

Hello all,


This is my first post, and I want to thank everyone for their contributions in this community. This is excellent software compared to other alternatives that I have used.


I already searched the forum for help dealing with this topic...


[img]phone-format.png[/img]


The input mask works fine, but what is the best way to reformat it for output?

Post's attachments

Attachment icon phone-format.png 54.44 kb, 121 downloads since 2019-12-27 

"Energy and persistence conquer all things."

2 Reply by derek (edited by derek 2019-12-27 13:03:00)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: Output for phone numbers and other text formats

Hi JoshuA,
Welcome on board!
As always with MVD, there are different ways to do it.
I'd suggest using a calculated field configured to use the same display format as you've defined for your phone number on your data input form, which then gives you
1.  The data input form uses the display format you defined.
2.  The tablegrid uses the calculated field.
3.  The report uses the calculated field.
4.  The database stores the phone number unformatted.
Calculated fields are set up as part of your database table definitions but don't actually get 'stored' - they exist only while your program is running.
Please have a look at the example and give a shout if anything isn't clear.
Derek.

Post's attachments

Attachment icon displayformat.zip 337.61 kb, 277 downloads since 2019-12-27 

  • Registered: 2019-12-24
  • Posts: 169

Re: Output for phone numbers and other text formats

Okay, so after searching a little more... any references to formatting seem to be only for integers or currencies.


I have tried the following:

TNxNumberColumn(frmTree.tgPhone.Columns[2]).FormatMask := '!\(999\) 000-0000;0;_';

and this:

TNxNumberColumn(frmTree.tgPhone.Columns[2]).FormatMask := '###-###-####';

I'm thinking that this type of formatting needs to be applied before it gets to the form. And a calculation field does exactly that.


Thank you derek, both for the solution and for the welcome.

"Energy and persistence conquer all things."
  • Registered: 2019-12-24
  • Posts: 169

Re: Output for phone numbers and other text formats

So, I noticed when viewing the data from SQLite Studio, that calculation field `cfphone` was missing. This was causing the error for a custom SQL query. After searching, this seems to be a limitation with SQLite.


This may be obvious to some, but I wanted to share this observation for those less skilled with both MVD or SQLite.


derek's solution still works, but it must be applied in a different place when a custom query is necessary.


SELECT
    test.name,
    '(' || substr("phone", 0, 4) || ')' || ' ' ||
        substr("phone", 4, 3) || '-' || substr("phone", 7, 5) AS phone
FROM
    test
Post's attachments

Attachment icon s12.301.png 103.34 kb, 121 downloads since 2019-12-30 

"Energy and persistence conquer all things."
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: Output for phone numbers and other text formats

Hi,
'cfphone' isn't missing because it doesn't actually exist (calculated fields don't get stored and are simply the temporary product of a calculation based on other (stored) fields, as mentioned in my previous post). 
A simple example would be actually storing someone's date of birth but their age is computed every time the program runs and made available as a calculated field.  When the program closes, their 'age' no longer exists (you could, of course, choose to actually store someone's age but it is data inefficient and will be wrong within 24 hours and so makes little sense).
And because calculated fields don't actually exist, you can't reference them in an sql query (for the same reason, you can't reference a calculated field in another calculated field either).
If you need to reference the same piece of information in an sql query, you simply need to replicate the syntax of the calculated field in your sql query 'select' statement (as you've described in your post).
Hope that clarifies things rather than confuses - LOL!
Derek.

  • Registered: 2019-12-24
  • Posts: 169

Re: Output for phone numbers and other text formats

Awesome! Thanks again derek for the analogy and extra explaination.

"Energy and persistence conquer all things."