Format the data type for a single dropdown selector

Ignition
1

I have a single column dataset tied to a drop down single input selector. I am returning a dataset of dates from sql and the format is not correct and not showing the correct form of the date. Can you format the options of the selector?

I tried the add transform option but it is not working. The choices for the selector are being populated by a named query.

2

Assuming this is Perspective, based on your use of transform, you probably want to translate your single column dataset from the named query into two columns, value and label.
There's lots of ways to do that.

The best option, performance wise, would be to use aliases and date formatting in your query.
If modifying the query isn't an option, you can use a script transform; the exact syntax will vary depending on how you're returning the query results (i.e. JSON or Dataset format), but basically you just build up a new return list; you can use system.date.format to format a Date object into whatever date presentation format you prefer.

ret = []
for row in value:
	actualValue = row["myColumn"]
	ret.append({"value": actualValue, "label": system.date.format(actualValue, "HH:mm:ss")})

return ret
3

I apologize but this is not working. Keep getting Error_ScriptEval. I went back and created another column in my query and cast the format of datetime to my values, its like it’s not being recognized in the correct format. I tried your solution below before I added the new column and it is still not working.

4

You'll need to provide (a lot) more information about what you're specifically trying - what's the exact code you're using, what's the data you're running that against, what exact errors are being thrown in detail, etc.

5
  1. I am binding my drop down to a named query.

[//cdck-file-uploads-us1.s3.dualstack.us-west-2.amazonaws.com/inductiveautomation/original/3X/5/6/56968220e8b3d06e611f1c896932b8383e5f6e43.jpeg]

  1. The data that is being seen in the drop down shows as below. The only thing that I need to do is format the data to a format that makes sense to the operator. I will then use this data in another query for the specific record details that he is looking for. Both columns have the same data in them. The SQl table that I am pulling the data from has a column data type of smalldatetime so I cast the data as a datetime and called the column DateTime2 hoping to see a change in formatting. No change. Every script that I try still gives me the same Error, Error_ScriptEval. I’m sure that is something that I am over looking.

[//cdck-file-uploads-us1.s3.dualstack.us-west-2.amazonaws.com/inductiveautomation/original/3X/5/e/5e3e79dddfa12d0c1836a62b2c2395447d73c637.jpeg]

[SCM Logo]<http=:/www.servicecentermetals.com/>
Neil Carty
Controls Engineer

O: 804.586.5787
F: 804.518.1551

5850 Quality Way
Prince George, VA 23875

[Facebook] http://www.facebook.com/servicecentermetals [YouTube] https://www.youtube.com/user/servicecentermetals

6

I went back change the column names to value and label like you said in your first e-mail (apologies I overlooked that) butand am getting the same result. I need to assign them to value and label but I cant figure out how to do that. Once I have that I can work on formatting.

7

It would be more efficient, methinks, to adjust your query.

SELECT DISTINCT DateEntered,
       FORMAT(DateEntered, 'yyyy-MM-dd hh:mm:ss tt') as DateString
FROM foy.SCM_Checklist
WHERE DateEntered >= :DateEntered
1 Like
8

That worked. I appreciate it Jordan.

1 Like