Date Formatting with data mapping

[CGibson]

Hello,

When dealing with data mapping, how do you format a date field if the column is using a character type?
Currently the value is displayed as 20050113 and I would like to see it as 1/13/2005.

Thanks,
Cory

[HankFay]

Hello,

When dealing with data mapping, how do you format a date field if the column is using a character type?
Currently the value is displayed as 20050113 and I would like to see it as 1/13/2005.

Thanks,
Cory

DTOC(STOD("{}"))

ought to do it.

hth,

Hank

PS: in your SET mapping, if the field is editable, you would use DTOC(CTOD("{}"),1).

[CGibson]

Hi Hank,

Where would I use this ... input mask or somewhere else?

Cory

[cyrilbaskir]

Hi Hank,

Where would I use this ... input mask or somewhere else?

Cory

You use Get data mapping and Set data mapping.
Some examples here should help.

[CGibson]

Thanks for all of the help.
The following worked for me:

DTOC(keylookup("orders","orderid_search",order_det ails.orderid,orderdate))




Also, I'm not sure who to report this to, although the lianjapedia example for STOD() uses dtos(). This gets extremely confusing when you are trying to learn and apply certain functions.

[barrymavin]

Hi Cory,

You can test anything interactively in the "Console" workspace.

Just type:

? dtos( date() )

as an example.

[yvonne.milne]

Hi Cory,

Submit a Ticket (you will need to be logged into the website) and set the Category to 'Documentation' if you find any Lianjapedia errors and we'll get them fixed up.

Thanks,

Yvonne

[CGibson]

Thank you Barry.
I'm still getting used to and learning the Lianja environment.

As per your request Yvonne, a ticket has been submitted.

[CGibson]

All seems good except for saving.
Changing a value, saving the grid then re-loading the grid places the value back to what it was.

I'm assuming the issue exists with set mapping data.

Get mapping data: DTOC(keylookup("orders","orderid_search",order_det ails.orderid,orderdate))

Set mapping data: DTOC(CTOD(keylookup("orders", "orderdate",order_details.orderid,orderid_search)) ,1)

Could someone help me as to the proper syntax required.

Thanks,
Cory

[yvonne.milne]

Hi Cory,

Sorry for the delay in responding.

Firstly, I would put the DTOC() in your Get data mapping around the return value, rather than the whole expression - KEYLOOKUP() will return .F. (false) if the value is not found in the index which would lead to an attempt to DTOC() a logical value. So:

keylookup("orders","orderid_search",order_details.orderid,DTOC(orderdate))

You can also add a 'not found expression' to KEYLOOKUP() - an alternative if the value is not found in the index, e.g. " / / ".

For the Set data mapping, first make sure you have an index on the orders table so that you can search for the orderdate:

select orders
index on dtos(orderdate) tag orderdate

The value your Grid column after a change will be of character type - a date formatted using DTOC(). To look this up in the orderdate index, you will need to convert it to a DTOS() format ("YYYYMMDD"). We can use {} to get the current column value. Put it in quotes as it is a character string: "{}", then convert it to the format needed to search the index: dtos(ctod("{}")).

keylookup("orders","orderdate",dtos(ctod("{}")),orderid)

The first parameter is the table that we want to look up into, the second parameter is the name of the index tag for that table that is going to be searched (created as above), the third parameter is what we are looking for in that index (the current column value correctly formatted) and the fourth parameter is the value we want back, the orders.orderid.

Hope that helps,

Yvonne