Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Changing the order of data being displayed (text field)

Changing the order of data being displayed (text field)

I have a large text field that CRXI displays in this fashion:

2015 : data... data
2016 : data... data
2017 : data... data
(I show the data as beginning with the calendar year for illustration only... the data is not actually in this state. As staff enter new data, those new lines are added to the end of the data field and therefore displayed as the last in the report.)

Is there a formula that would change the order of the displayed data as follows:

2017 : data... data
2016 : data... data
2015 : data... data

RE: Changing the order of data being displayed (text field)

No need for a formula.
In layout menu bar there will be a Sort order Icon. Can't recall what it looks like just hover over them and a tooltip will indicate what it is
Go to help and search for sort and it will probably show you what the icon looks like

RE: Changing the order of data being displayed (text field)

Thanks Ian, however it is the order of the data within the text field that I am trying to modify. I want the data from this particular text field, for each record, to display in a different order than the order stored in the database.
I want the last entry placed in the the database field to display first.

The text in this field can be several dozens of lines and I want to set the field to only display three lines... an those line are to be the last three entered in into the database. Currently the report is displaying the first three lines entered.

RE: Changing the order of data being displayed (text field)

Apparently this...
2015 : data... data
2016 : data... data
2017 : data... data all in one row of data of one field? And there may also be data in this row of this field for 2014, 2013, etc? And how many rows of data in your table?

It would seem that you would need someone to write a program to grab each chunk of data for a year, put it in a new place in a new string and then update the table for that string for that row in that field.

BTW, this would be an ongoing mess, cuz the users are gonna want to add new data for the current year in the usual way: at the end of the data in this row for this field, which will be in the wrong place!!!!


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Changing the order of data being displayed (text field)

Try the following:

stringvar x := {table.text};
stringvar c := totext(year(currentdate)-2,"####")+extractstring(x, totext(year(currentdate)-2,"####")," "+totext(year(currentdate)-1,"####"));
stringvar b := totext(year(currentdate)-1,"####")+extractstring(x, totext(year(currentdate)-1,"####")," "+totext(year(currentdate),"####"))+chr(13);
stringvar a := totext(year(currentdate),"####")+extractstring(x, totext(year(currentdate),"####"),right(x,1))+right(x,1)+chr(13);

This formula just pulls out the last three years with corresponding data from the field and then reassembles the data so that the current year shows first.

Be sure to format the formula to "can grow" as I added in returns so that the years would appear first in each line.


RE: Changing the order of data being displayed (text field)

Thank you Skip and LB.
I will try the formula when I return and am able to do so.

As the data is entered, the database time stamps (date/time) for each and every individual
entry. And therefore the text field is one continuous string with date stamps within.

If I recall correctly. “2017-11-06 11:12:30AM” is the format for each date stamp.

So this text field functions in a log file type of way... one log per record listing the data input entry activity.

RE: Changing the order of data being displayed (text field)


"2017-20-12 06:21:42 PM" it the actual time stamp.

I ran the formula and it executed with out error. However it does not display the text or in some case only some of the text. ( I ensured "Can grow" was used.)
Most records display as follows:


Perhaps I should take this is small steps. How would I use a formula to parse the text in the field to display in the report in this manner :

2017-20-12 06:21:42 PM... text for the first data entry
2017-21-12 06:21:42 PM... text for the second data entry
2017-22-12 06:21:42 PM... text for the third data entry
2017-23-12 06:21:42 PM... text for the fourth data entry

RE: Changing the order of data being displayed (text field)

You misrepresented what your data actually looked like. The solution I gave you was based on the year not on a datetime field. Even so, I think you must have implemented it incorrectly. I did test this. However, you can’t easily use a formula solution for something this complex. Skip was right that this should be addressed at the data entry level through a redesign.


RE: Changing the order of data being displayed (text field)

Sorry or the misrepresentation of the data...and thanks again for your examination and formula. I will try again with the formula and see if I can find
the issues in the implementation.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close