Ordering An Append Operation

[BOB SULLIVAN]

I am appending from a transcation table "results" to another table "north port enhancemants. I need to order the transaction table before the append so that the final table is correctly ordered for the client.

I have copied the xbasic code from an append that does not order coreectly. I am trying to insert a line that would also cause the transaction table to be ordered.
The following is the start of the append code.

DIM Append as P
'Prompt for any ASK variables in filter/order expressions
ask_vars = <<%list%
Askn_Enter_Submission_Number

%list%
prompt_result = a5_prompt_for_ask_vars(ask_vars,session_variables(session_get()))
if prompt_result <> "OK" then
end
end if

a_tbl = table.open("north port enhancement")
append.t_db = "results"
ON ERROR GOTO ERROR19052008102523906

append.m_key = ""
append.t_key = "recno()"
append.m_filter = ""
' THIS IS WHERE I TRIED TO ORDER
APPEND.t_ORDER = "str(Sampnumber,19,5)+Method+Compound"
append.t_filter = "Submission=Var->askN_ENTER_SUBMISSION_NUMBER"



I am not sure if this is the best way. The append works but does not include any order.



Thanks for your time


Bob

[Stan Mathews]

Re: Ordering An Append Operation

APPEND.t_ORDER = "str(Sampnumber,19,5)+Method+Compound"
append.t_filter = "Submission=Var->askN_ENTER_SUBMISSION_NUMBER"

I'll give you points for ingenuity. Unfortunately the .append() construction does not support an order syntax.

You could copy the transaction table records to a temporary table in the order you desire and append from there. You might try ordering the transaction table via a query before the append but I'm not hopeful that would work.

[BOB SULLIVAN]

Re: Ordering An Append Operation

Thanks Stan.

I tried the query first but then the append seemed to ignore the append and placed the records by record number.

I'll keep trying different things.


Bob

[MoGrace]

Re: Ordering An Append Operation

The temp table is the best bet. I just went thru this recently and Stan is right, a query won't have any affect on an append, for some reason it wants record number order.

[BOB SULLIVAN]

Re: Ordering An Append Operation

I ended up adding a sort key to the append table. Then appended the data. Then performed a saved query on a saved browse without the sort key being visible. This allowed me to export directly into excel for the client in the correct order. I set it up as a script which involved very few interactions by the staff.

Thanks for the responses

Bob Sullivan

[MoGrace]

Re: Ordering An Append Operation

I ended up adding a sort key to the append table. Then appended the data. Then performed a saved query on a saved browse without the sort key being visible. This allowed me to export directly into excel for the client in the correct order. I set it up as a script which involved very few interactions by the staff.

Thanks for the responses

Bob Sullivan

So you are using an excel spreadsheet instead of a temp table and then importing those records back? Or was the spreadsheet the goal in the first place? I just want to be clear on what the actual resolution was.

[BOB SULLIVAN]

Re: Ordering An Append Operation

The final result was to be an excel sheet for the client.

I operate an environmental testing laboratory. Each client needs something a little different for their own use. My main table is a table called "results" so I have to create a table that had the clients own requirements for each specific field in each specific record.

So I go from "results" and append to an empty table "north port enhancement", perform a query sort and then send to an excel sheet for their use.

(You could say that the "north port enhancement" table is a temp table except each field may have to be manipulated.)

By the way, each client needs a different type of sort based on each project.


Bob Sullivan

[MoGrace]

Re: Ordering An Append Operation

How interesting, my son works for a company that gathers the field data used for the testing. It can be a bit complicated as I understand it based upon the client's needs.