Default Table Sort

[Clunes]

I want to have a default sort for a table based on a date field. I have tried everything in indexes but nothing works.

[Tim Kiebert]

Re: Default Table Sort

At which point do you want this default sort to take affect? In forms or browses you can set that in the properties. I don't think it can be set in the default browse. In xbasic, say using table.open(), you would need to apply the index.

[Clunes]

Re: Default Table Sort

Hi Tim - my problem has turned out bigger than that. What I am trying to do is create crosstab that sorts the columns based on a date field. I want to produce a monthly stat by user. See attached picture of the current result. No matter what I do with teh date field it does not prduce a logical sort result. It keeps sorting as a character. Appreciate any hep.

demo.jpg

[Ted Giles]

Re: Default Table Sort

Have you tried the little button with 3 dots on it Mark?
groupby.JPG

[Clunes]

Re: Default Table Sort

Yep - sure have. It does not seem work for dates. As I want it to sort by month, all it does is encase the date in month(), which then gives a number which is sorted, but if the year for months 10 11 and 12 is 2011, and the months 1for 1 2 and 3 is 2012, it still sorts as 1 2 3 10 11 12. This problem is componded by the fact it does it only shows a number for the sort

[Ted Giles]

Re: Default Table Sort

Got a bit further like this Mark;
groupby01.JPG

[Tom Cone Jr]

Re: Default Table Sort

Mark,

I'm not understanding you. 1,2,3,10,11,12 IS sorted by month.

If you want 10, 11, 12, 1, 2, 3 then you have to sort by year and by month.

Perhaps DTOS(your_date_field) will help. If you group by the left 6 characters wouldn't that gete you there?

[Clunes]

Re: Default Table Sort

Thanks All- the penny drops. I was so used to another programs crosstab system (lotus approach) that I simply thought that it would still base the sort on the date. Guess I will get used to it. Thanks. I might add that Alpha should take alook at the Lotus approach one. Well over 13 years old in tech terms, but still much better than Alpha's. I guess I can't have everything

[Clunes]

Re: Default Table Sort

OK - FYI - I got the sort correct based on your help. In order to achieve my final goal of puting the result table in an excel spreadsheed, I had to manipualte the field names to be more in line with something presenatable. The scrip below does:

1. Creates a silent crostab
2. Uses the result table
3. Opens excel and puts in the data

The great thing about this whole project was that I had to actually write very little of the code. The cross tab was initialy created with the wizard and converted to xbasic. The code for excel was recorded as a macro in excel and then copied/paste and modified into the code.

--------------------
'Creating the crosstab

xtab_db_name = filename_decode("IBYBM.DBF")
tbl = table.open("e15a")
ON ERROR GOTO ERROR09062012221756893
crosstab.db = xtab_db_name
crosstab.db_dd_delete = .F.
crosstab.filter = ""
crosstab.row = "FIELD35"
crosstab.column = "STR(YEAR(SMD))+STR(MONTH(SMD))"
crosstab.options = "I"
crosstab.tabulate = "ISSTOTAL"
crosstab.code = 1
tbl.crosstab()
GOTO CONTINUE09062012221756893
ERROR09062012221756893:
ON ERROR GOTO 0
ui_msg_box("Error","Error running Crosstab Operation"+crlf()+error_text_get())
END
CONTINUE09062012221756893:
tbl.close()
'Add the table just created by the Crosstab operation to the Database..
file_add_to_db(xtab_db_name)

'Extracting the crosstab data and putting it in excel

Dim excel as p
dim t1 as p
dim q1 as p
dim pth1 as c
dim filepointer as p
dim mylist as c
dim nrecs as n
dim i as n
dim i1 as n
dim lcount as n
dim pname as c
dim getarow as c
dim myfd as p
dim myfd1[20] as c
dim mytotal as c
dim myform1 as c
query.filter = ""
query.sort = ""

mylist = A5_GET_FIELDNAMES("ibybm","n") 'Extract result table field names

lcount = *count(mylist)
dim mylist2 as c
mylist = STRITRAN(mylist,"_"," ",1)
dim mylist2 as c
i = 0
mylist = *line_replace(mylist,"Users", 1)

for each x in mylist 'Changing field names to be more readable

x.value = "01 " + right(x.value,len(x.value) - at2(" ", x.value,1)) + " " + left(x.value,4)

if isdate(x.value) = .t. then
if mylist2 = "" then
mylist2 = date_format(ctod(x.value),"Mon yyyy")
else
mylist2 = mylist2 + crlf() + date_format(ctod(x.value),"Mon yyyy")
end if

end if
i = i + 1

next
mylist = "User" + Crlf() + mylist2

mylist = STRITRAN(mylist,crlf(),",",1) 'Convert list to comma sepearted to become first row in spreadsheet

pth1 = a5.Get_Path()
pth1 = pth1 + chr(92) + "work files"
if A5_IS_PATH_VALID(pth1) = .f. then
DIR_CREATE(pth1)
end if

pth1 = pth1 + chr(92) + "LB_issues_by_user_by_month.csv" 'Default spreadsheet name

if file.exists(pth1) = .t. then
file.remove(pth1)
end if

filepointer = file.create(pth1,FILE_RW_Shared)
mylist = mylist + crlf()
filepointer.write(mylist)

t1 = table.open("ibybm",FILE_RO_SHARED)

nrecs = t1.records_get()

mylist = ""
t1.fetch_first()

for i = 1 to nrecs
getarow = ""
for i1 = 1 to lcount
myfd = t1.field_get(i1)
if getarow = "" then

getarow = myfd.value_get()
getarow = alltrim(getarow)
else

getarow = getarow + "," + myfd.value_get()
end if


next

t1.fetch_next()
File.Append_Line(pth1,getarow)

next

filepointer.flush()

filepointer.close()
t1.close()
dim sv as c
dim ev as c

sv = "A"

for i = 1 to lcount-1
sv = increment_value(sv)

next


ev = alltrim(sv + alltrim(str(1)))
ev = "B1:" + ev

excel = ole.create("excel.Application")

excel.Workbooks.Open(pth1)
excel.visible = .t.


excel.Range(ev).select()

excel.selection.HorizontalAlignment = 3
excel.selection.VerticalAlignment = 3
excel.selection.Orientation = 90
excel.selection.font.bold = .t.
excel.Range("A1").select()

sv = "A" + alltrim(str(nrecs+2))
excel.Range(SV).select()
excel.selection.formula = "Totals"
excel.selection.font.bold = .t.

sv = "A"
dim mrow as n
dim mrow1 as n
dim myform as c
dim sr as c
dim er as c
dim sr1 as c
mrow = nrecs+2
sr = "-1"
sr1 = "-2"
mrow1 = (nrecs+1)*-1
er = alltrim(str(mrow1+1))

for i = 2 to lcount
sv = increment_value(sv)
ev = alltrim(sv)
ev = ev + alltrim(str(mrow))

excel.Range(EV).select()
myform = "=Sum(R["+er+"]C:R["+sr+"]C)"
excel.ActiveCell.FormulaR1C1 = myform
excel.selection.font.bold = .t.
next
sv = "A"
for i = 1 to lcount
sv = increment_value(sv)

next
dim sv1 as c

sv1 = increment_value(sv)
excel.Range(SV + alltrim(str(1))).select()
excel.selection.formula = "Totals"
excel.selection.font.bold = .t.

excel.Range(SV1 + alltrim(str(1))).select()
excel.selection.formula = "Average"
excel.selection.font.bold = .t.

for i = 2 to nrecs + 2

excel.Range(sv + alltrim(str(i))).select()
mrow = lcount * -1
er = alltrim(str(mrow))
myform = "=Sum(RC["+er+"]:RC["+sr+"])"
excel.ActiveCell.FormulaR1C1 = myform
excel.selection.font.bold = .t.
excel.Range(sv1 + alltrim(str(i))).select()

myform1 = "=average(RC["+er+"]:RC["+sr1+"])"
excel.ActiveCell.FormulaR1C1 = myform1
excel.Selection.NumberFormat = "0"
excel.selection.font.bold = .t.

next

excel.activesheet.Cells.Select()

excel.Cells.EntireColumn.AutoFit()
excel.Range("A1").select()
'excel.activesheet.Cells.Select
'excel.Cells.EntireColumn.AutoFit

[Ted Giles]

Re: Default Table Sort

Well the code certainly looks impressive Mark!
Just for info, why did you choose to manipulate the data in Excel and not in Alpha and then just do a simple export to Excel?

[Clunes]

Re: Default Table Sort

The key reason I use excel is that I cannot build a report to manage variable number columns which a cross tab builds. Excel allows me to do this and Excel puts the report on more common ground for others to review. I could view a browse result and export to execl, but I would still have to manipulate the coulumn names to make sense.

If you want to test the above, copy and paste into a new code, build a crosstab (or use one you have built), view te crosstab code in xbasic, copy and paste over the crosstab script. Then change the results table name in the A5_getfieldnames() line. Run and it should work to some degree. The only issue you will need to change in greate detail is the part of the script that changes the column names. Initially it will not effect the execution, just the look of the columns.

[Ted Giles]

Re: Default Table Sort

Always more than one way to skin a cat Mark and if the solution works for you that's all that matters.
However,
In the Crosstab Genie, the facility to select which records are actually evaluated is available, so in date terms that would limit the range of columns.
If I had to do this on a regular basis, I'd create a Query to identify the records I was interested in, say Year = 2012. This could be a user controlled date range
Create a Table with the Field Names the way people expect - As in Person, Sales, Date etc.
Run the Crosstab and use Replace Existing Add Unique Append function mapping the raw cross tab names to the report table names, and then squirt it into Excel.
The users could view in Excel and I'd still have the data files to use for other purposes.

[Clunes]

Re: Default Table Sort

Yep Ted, more than one way for everything - if there wasn't sex would be boring (did I just say that?). In relation to your approach, how do you get the column names in the crosstab to be the way you want, particularly based on a date and based on a variable numebtr of columns

[Ted Giles]

Re: Default Table Sort

I'm obviously missing something here. I had assumed that you were looking at a fixed period to report upon. Say a 12 month window.
The query would give you the period, say 01/01/2012 to 31/12/2012.
If the sort order is correct then the first col will be Jan, 2nd Feb etc.
The append would insert data into the column with a text descriptor - Jan/Feb/Mar etc

So Xtab 2012_2_8 would map to Feb

On the other hand, if you are looking at unbounded periods like for all time, then the approach I suggested would be more cumbersome.

I'll try and put a sample of what I mean together (and I may fall flat on my face) but I'm sure that I have done this previously.