Append transaction records based on external table lookup

[confused]

Mental block coming from SQL world. How would I copy records from one table to an exact duplicate table but filtered by the value in a third table? I believe I use the filter in the append operation but the lookup syntax is messing with me. Header_table, child_table_current, child_table_archive...Append records from child_table_current to child_table_archive only if a logical field in the header_table is marked .T. The header and child tables are 1-Many linked with a field "GUID".

The syntax for the filter is what I'm looking for.

Thanks

[Stan Mathews]

Re: Append transaction records based on external table lookup

Mental block coming from SQL world. How would I copy records from one table to an exact duplicate table but filtered by the value in a third table? I believe I use the filter in the append operation but the lookup syntax is messing with me. Header_table, child_table_current, child_table_archive...Append records from child_table_current to child_table_archive only if a logical field in the header_table is marked .T. The header and child tables are 1-Many linked with a field "GUID".

The syntax for the filter is what I'm looking for.

Thanks

I don't believe you can use a set as a transaction entity for an append operation. Consider copying records from the set, based on the parent logical field, and then appending to the archive table from that result.

[confused]

Re: Append transaction records based on external table lookup

Stan,

Thanks for replying. I re-read my post and its seems jumbled. I want to do what you are suggesting: Copy a record or group of records from the child_current_table to the child_archive_table, but ONLY the child_current records whose parent records have a field "Inactive" marked true. I'm not copying any header records or sets.

I add the linking field/set info because I think it's important to the filter syntax?

[Stan Mathews]

Re: Append transaction records based on external table lookup

Stan,

Thanks for replying. I re-read my post and its seems jumbled. I want to do what you are suggesting: Copy a record or group of records from the child_current_table to the child_archive_table, but ONLY the child_current records whose parent records have a field "Inactive" marked true. I'm not copying any header records or sets.

I add the linking field/set info because I think it's important to the filter syntax?

Copy from the set if you have one, make one if you don't. Just because you copy from the set doesn't mean you have to include any fields from the parent in the output. It may be possible to write a filter expression which would extract only the pertinent child records, but why bother?

[confused]

Re: Append transaction records based on external table lookup

Copy from the set if you have one, make one if you don't. Just because you copy from the set doesn't mean you have to include any fields from the parent in the output. It may be possible to write a filter expression which would extract only the pertinent child records, but why bother?

I just looked at that but the copy operation creates a new table. I would like to append records to an already existing table. Example, Workorder table has subtasks which are entered into the subtasks_current table, from a form, based on a set. Once a month I would like to append the subtask records to the subtasks_archive table but only for subtasks that have a parent record in the workorder table marked as "Inactive". The filter expression for pertinent child records is important. I know it needs to be in the filter for transaction table but I can't get the lookup() function to do what I want.

Here's the xbasic from the append operation screen:

a_tbl = table.current()
append.t_db = "c:\leasdoc\wo_tasks_current.dbf"
append.m_key = "guid"
append.t_key = "guid"
append.m_filter = ""
append.t_filter = "THIS IS WHAT I DON'T KNOW" It should say "ONLY RECORDS WHOSE PARENT RECORD FROM THE WORKORDERS TABLE ARE 'INACTIVE'"
append.type = "Unique, replace existing"
append.m_count = 3
append.m_case1 = "EITHER"
append.m_field1 = "WO_ID"
append.m_exp1 = "@WO_TASKS_CURRENT->WO_ID"
append.m_case2 = "EITHER"
append.m_field2 = "WO_JOB"
append.m_exp2 = "@WO_TASKS_CURRENT->WO_JOB"
append.m_case3 = "EITHER"
append.m_field3 = "WO_TASK"
append.m_exp3 = "@WO_TASKS_CURRENT->WO_TASK"
append.t_count = 0
a_tbl.append()

I attached the tables in MS Access and made the append operation, here's the SQL:

INSERT INTO wo_tasks_archive ( wo_id, wo_job, wo_task )
SELECT wo_tasks_current.wo_id, wo_tasks_current.wo_job, wo_tasks_current.wo_task
FROM wo_tasks_current
...The following filters the records...
INNER JOIN workorders_current ON wo_tasks_current.wo_guid = workorders_current.guid
WHERE (((workorders_current.inactive)=True));

I did this in 30 seconds and works like a charm. I've spent 4 or 5 hours trying to decifer the documentation and searching this forum before I gave up and asked the question. I'm sure it's something simple but I'm not seeing it.

Thanks

Below is the sample Alpha ADB if anyone's interested, help is greatly appreciated.

[Tim Kiebert]

Re: Append transaction records based on external table lookup

Create an index on the GUID field in the workorders_current table called guid_.
Guid_ - GUID - no filter - Ascending - All

This will be used in the LookupL function. Then use the following as the filter in the append genie.

Lookupl("F",Wo_Guid,"Inactive","workorders_current","Guid_")

which becomes this in the xbasic

append.t_filter = "lookupl(\"F\",Wo_Guid,\"Inactive\",\"workorders_current\",\"Guid_\")"

Note: the filter does not need to compare the returned value of the function to 'True' because the returned value is already a true or false value and that is what the filter needs to return in order to include or exclude records.

PS that took 90 seconds but that included checking out your table structure and adding an index.;)

[jkletrovets]

Re: Append transaction records based on external table lookup

Attached is a tweak to your example that should work. BTW, you had 2 indices in the work order table with the exact same name. I changed one. You should probably do the same with your real tables.

Regards,

Jeff

Edit: I just noticed that Tim beat me to the reply. Anyway, my modification of your example just does what he explained.

[Tim Kiebert]

Re: Append transaction records based on external table lookup

Attached is a tweak to your example that should work. BTW, you had 2 indices in the work order table with the exact same name. I changed one. You should probably do the same with your real tables.

Regards,

Jeff

Edit: I just noticed that Tim beat me to the reply. Anyway, my modification of your example just does what he explained.

Great minds think alike ........ I forget how the rest of that goes:)

[confused]

Re: Append transaction records based on external table lookup

Bingo! Thanks to all for your help. I'm desperately trying to kick Access to the curb but as you can see the syntax to do the same thing in Alpha is quite different. I can use this example to answer a whole lot of other questions I have. Now this should only take 15 seconds in the future, productivity increased 100% :D

Thanks again

[jkletrovets]

Re: Append transaction records based on external table lookup

Really,

I too come from an environment that utilized SQL statements. It sure is easy to extract data once you get used to writing the SQL statements and I miss that a little in Alpha. But, all of the other the great features in Alpha more than make up for it.

Also, just to throw it out for your consideration. If you are going to do very much development in Alpha you might want to consider a naming convention to follow. It really does make a difference. Cal Locklin's is very well thought out and I have found it very valuable to follow his recommendations. More info is in his white paper here:

http://www.aimsdc.net/Tips_Funcs/A5_...onventions.htm

It might also be nice if you give us at least your first name in your message board name as it makes communication a little easier.

Regards,

Jeff

[confused]

Re: Append transaction records based on external table lookup

Jeff, Thanks for the link. I'm no longer really confused but inadequately competent.

John

[jkletrovets]

Re: Append transaction records based on external table lookup

Hi John

Sure thing.

>>I'm no longer really confused but inadequately competent.

I have days where I feel that way myself. :)

See you around here...

Jeff