Formatting database fields

Using Excel,SQL,Oracle, and other ODBC Compliant Databases
Post Reply
TGElkland
Regular Contributor
Regular Contributor
Posts: 8
Joined: Tue Apr 23, 2013 3:14 pm

Formatting database fields

Post by TGElkland »

I am linking text boxes to database fields. My question is, and maybe I have missed it somewhere, how do I format those fields once I have pulled the data in. For example, I am linking to an Excel spreadsheet and some of the fields contain dates. In Excel, it appears as 7/12/13, but in Noventri I end up only seeing the decimal equivalent, which will not work. These dates are not always equal to the current date. In addition, numeric values in Excel that are numbers such as 76, end up being 76.00 in Noventri. I need them to look like they would in Excel.
Thank you.
Sergey K

Re: Formatting database fields

Post by Sergey K »

Hello TGElkland,

You can reference the post below when working with different types of database projects. The FORMATTING TIME AND DATE DATA section goes over ways to set the clock to a desired style.

http://www.noventri.com/forum/viewtopic.php?f=61&t=225

Working with Database Time/Date fields:
We have an option in Noventri Suite to select multiple time formats or even create your own, this way the original data can be preserved in the data source. Once the date is properly pulled from the database and shows up in Suite, select the Text tab in the properties panel and click the Time Format drop-down. You will have a few preset data formats to choose from. If you cannot find a format you would like to use, select the closest one, then click the drop-down again, and select Custom this time. A new text-box will appear near the Time Format drop-down to allow you to customize the output anyway you like.

Take a look at the pages here and here to see a list of time formatters that can be used in the custom text-box.

Removing the 0's after whole numbers:
Once the date is properly pulled from the database and shows up in Suite, select the Advanced tab in the properties panel and click the Select button to bring up the Select Table dialog. Click the Advanced radio button in the top Database section to enable the Select Statement text-box. This will allow you to type custom select statement and add additional formatting to your data before the it gets pushed to the screen.

Examples:

This will display all fields in the table "Events".

Code: Select all

SELECT * FROM [Events$]
The example below will remove any 0's at the end of whole
numbers while leaving decimals when they need to be there.

Code: Select all

SELECT *, cStr(TotalCost) AS RemovedDecimalFrom_TotalCost FROM [Events$]
Once the select statement has been modified, click the green Refresh button to the right to see the results. A new column will be added to the table called "RemovedDecimalFrom_TotalCost" (from the example above) that list the converted numbers and leave the "TotalCost" column with the original data. At this point, just use the new formatter to output the data to the screen.

If you have any further questions or need any assistance, please let me know.

Kind Regards,
Sergey
jbarnes
Forum User
Forum User
Posts: 2
Joined: Thu Jun 25, 2015 8:54 am

Re: Formatting database fields

Post by jbarnes »

I am having the same problem. All my numbers in my excel sheet are showing up in Noventri with two decimal places. However, your solution does not work for me. I have numerous columns and rows with this same issue. If I format the select statement text box for each column that would take forever. Do you have any other suggestions?
WellbornForest
Forum Contributor
Forum Contributor
Posts: 4
Joined: Wed Mar 30, 2016 11:44 am

Re: Formatting database fields

Post by WellbornForest »

I found the solution for the decimal places using the ODBC for Excel
Select Schedule AS Scheduled FROM [Sheet1$]
gave me 564.00 when the excel sheet had no decimals

My co-worker suggested using the LEFT function in the statement

Select LEFT(Schedule,4) AS Scheduled FROM [Sheet1$]


gives the value of 564

I spent hours on this, even trying the LEFT function once to no avail.
I hope this saves someone some time.
Post Reply