Number format pattern / format string

Hello,

Good day. May I know how can I change the format pattern of my tag ?
My tag type is double but due to certain reason, I am using a table with string column type to display the data. I am using cell update binding for my table.

My objective is just to show 2.35 and 197.64.

Whatever your reasons, you’ll need to do the formatting at the point where you are converting to string.

Is this only way to do it ? I try changing the format string on the tag and it doesn’t work.

Do the formatting on table, not in tag.

how do I do that ?

How is the tag being brought into the table? Is it part of a SELECT query? Are you creating the table data with a script? However you are generating the data for the table - that is your opportunity to format the string as you need it.

In fact, the table is in the template.
The reason i set the column to be string due to I have string data on the same column.
I am using Cell Update binding for the table. Each specific cell is bind to the the template custom properties. I then bind the table custom properties to the indirect tag.

image

Consider using a custom property on the table to hold the raw data (your cell bindings would also be in there), then process the raw dataset to the format you want and write it to the data property.

One way would be to use a try/except block to test for a float.

# Create a sample dataset
sampleHeaders = ['Col1', 'Col2', 'Col3']
sampleData = [['abc', '12.3456', 'def'],
              ['789.012', 'ghi', '34.5678'],
              ['jkl', '987.54321', 'mno']
             ]

sampleds = system.dataset.toDataSet(sampleHeaders, sampleData)

#----------------------------------------------#

# Get column names and number of rows and columns
headers = list(sampleds.getColumnNames())
nRows = sampleds.getRowCount()
nCols = sampleds.getColumnCount()

# Process the dataset
data = []
for i in range(nRows):
	# Start with a blank row
	newRow = []
	for j in range(nCols):
		value = sampleds.getValueAt(i,j)
		# Try to make value a float.
		# If we can, append a formatted string.
		try:
			newRow.append('{:.2f}'.format(float(value)))
		# Otherwise, just append the original string.
		except:
			newRow.append(value)
	# Append the row to the data
	data.append(newRow)

# Create the new dataset
dataOut = system.dataset.toDataSet(headers, data)
raw dataset
row | Col1    | Col2      | Col3   
-----------------------------------
0   | abc     | 12.3456   | def    
1   | 789.012 | ghi       | 34.5678
2   | jkl     | 987.54321 | mno    

updated dataset
row | Col1   | Col2   | Col3 
-----------------------------
0   | abc    | 12.35  | def  
1   | 789.01 | ghi    | 34.57
2   | jkl    | 987.54 | mno  

I see this is Vision.
In your Table or Power Table component you should add some script.

Examples
Here is my demo dataset where I’ll work with “Col 3”:
image

If you are using a Table:
Find getDisplayTextAt Extension Function and add the script:

if col == 2: #Col3
	try:
		return "%.2f" % round(float(value),2)
	except:
		return value
else:
	return defaultText

Here is the result:
image

If you are using a Power Table:
Find configureCell Extension Function and add the script:

if colIndex == 2:
	try:
		return {'text': "%.2f" % round(float(value),2)}
	except:
		return {'text': value}

Here is the result:
image

Hmm… sounds rather familiar. :wink:

Totally,
I work with display options on tables,
You rebuild the dataset for tables,
How we transform data → the same!

1 Like

Thanks jespinmartin. great solution as it saved me lots of hassle.
Thanks to JordanCClark too

How about converting it to whole number ? instead of 12.60, would like to convert to 12 instead.
Do you mind to share where can I refer to ?

If you’re looking to truncate it to 12:
"%0d" % float(value)

If you’re looking to round it to 13:
"%0d" % round(float(value))

thanks JordanCClark

Fwiw, all of this stuff is just Python which a quick google will invariably get you what you need to know