DATETIME to string & auto incremental number

[Deric_Voon]

Hi all,
Can anyone help me the following ?

1. When I call “Load Table Object” function, how do I get date time string (instead of number) from database DATETIME column type.
2. how do i create a DOUBLE / INT column auto filled with increment number.

Thanks.
Deric

[mark-piller]

Hi Deric,

To create a new column in a data table:

1. Switch to BACKEND
2. Click the Data icon
3. Select the table where you need to create a column
4. Switch to the SCHEMA tab
5. If the Schema is shown in Visual Modeler, click the “+” icon to create a new column in the selected table:
   
   
6. Type in a name for your column and select the AUTO NUMBER in the Type dropdown:
   
   

Regards,
Mark

[olhadanylova]

Hello @Deric_Voon,

1. When I call “Load Table Object” function, how do I get date time string (instead of number) from database DATETIME column type.

The DATETIME values are stored as timestamps in the database.
To get the date string from timestamp please use the Date section blocks:

1. create date from timestamp
2. create string from date

Regards,
Olha

[Deric_Voon]

Thank you very much

[Deric_Voon]

Opp , sorry that i overlooked this type earlier.
Thanks.

[Deric_Voon]

Hi Olha,
The “Load Table Object” function returns more than 1 data objects from database.
Any idea how do loop through the returned list of objects and convert every DATETIME field in the objects to string ?
I want to feed the returned list to Dynamic list.
Thanks.
Deric

[vladimir-upirov]

Hello @Deric_Voon

You can iterate through the list and transform each DateTime column’s value into a string

You can do it in the BL or in the UIBuilder logic as well

Regards, Vlad

[Deric_Voon]

Got it.
1 problem is, when i save the object to database, how do i convert back to DATETIME format?

When I convert DATETIME to dateString, I got error when i save to database. (picture below)

When I convert DATETIME to localeString, the date time will be changed (example , from original 01/25/2021 11:46:57 to 01/25/2021 08:00:00) .

Any idea to fix this.
Thanks.
Deric

[vladimir-upirov]

before sending the object back to the server you need to convert it back to timestamp (number), there is a corresponding block for that

[Deric_Voon]

I tried but encountered error.
I convert DATETIME to localString.

Then i convert back to time using “date from string”.

But i got the error.

[vladimir-upirov]

Seems like the transformation makes your logic more complicated, I would recommend you to convert the timestamp into the local string in a place where it’s needed.
For instance, you display it in the DynamicList and the value is bonded to some Text Component, so you can use the “Content Logic”.

[Deric_Voon]

This is a much better way. Didn’t know this earlier.

I created a 1:1 relationship between “task” and “Users” table.

How do I write use “Load Table Objects” to get data like the following SQL?
"SELECT u.name AS ‘Name’ , t.username FROM task t
LEFT JOIN Users u ON t.username = u.username "

Can I use alias in “Load Table Objects” property ?

[vladimir-upirov]

@Deric_Voon please create a separate topic for each issue.

Regards, Vlad