Export producing records based on Parent table

[draxx]

Hi, I'm having a problem with a script I'm putting together and not quite sure why. The first part of the script is a query that produces one record only. Great so far. The next part of the script should export 4 fields from the result of the preceding query. When I run the query and export, the query is working fine. I have opened the table "Clients", and the appropriate record is displayed. When the export runs, it not only displays all "Clients", but it also displays a record for each corresponding result in the parent table, even though the parent table is not open. Instead of having an export showing one record with four fields in it, it will max out the excel program at just over 16,000 records. Ultimately what I want to accomplish is have the export based on the preceding query which only has one record. Below is the code. For all intents and purposes, it should be working. I'm not sure whether it has to do with the table "Clients" being a child table of "ameregl3" or not. There are other parts of the script that are currently commented out as I work with sections of a script until I have a fully functioning script. Hope this all makes sense.

dim user1 as C
dim user2 as C
dim tbl as P
dim a_tbl as P


tstr1 = ui_get_text("Client Name","Please Enter Client")
if tstr1=""then
end
end if
user1=(tstr1)

'tstr2 = ui_get_text("Reference Number","Please Enter the Reference Number")
'if tstr1=""then
' end
'end if
'user2=(tstr2)


'DEBUG(1)

tbl=table.open("clients")

query.description = ""
query.order = "ATTORNEY"
query.filter = "ATTORNEY=(user1)"
query.options = "I"
tbl.query_create()

tbl = table.current()
export.type = 4
export.names = .F.
export.file = "C:\Others_billing.xls"
export.options = ""
export.field_sep = ""
export.record_sep = ""
export.fields = 4
export.field1 = "clients->attorney"
export.field2 = "clients->name"
export.field3 = "clients->address"
export.field4 = "clients->city_state_zip"
tbl.export()

'tbl=table.open("ameregl3")

'query.description = ""
'query.order = ""
'query.filter = "REF___=(user2)"
'query.options = "I"
'tbl.query_create()

'tbl = table.current()
'export.type = 4
'export.names = .F.
'export.file = "C:\Others_billing2.xls"
'export.options = ""
'export.field_sep = ""
'export.record_sep = ""
'export.fields = 4
'export.field1 = "ameregl3->date_comp"
'export.field2 = "ameregl3->person"
'export.field3 = "ameregl3->ref___"
'export.field4 = "ameregl3->fee"
'tbl.export()

[Stan Mathews]

Re: Export producing records based on Parent table

................
tbl=table.open("clients")

 query.description = ""
 query.order = "ATTORNEY"
 query.filter = "ATTORNEY=(user1)"
 query.options = "I"
 tbl.query_create()

[COLOR="#FF0000"] tbl = table.current()[/COLOR]
 export.type = 4
 export.names = .F.
.....................

Get rid of the line in red.

[draxx]

Re: Export producing records based on Parent table

Thanks Stan, I'll try it tomorrow.......

[Stan Mathews]

Re: Export producing records based on Parent table

The usage

query.filter = "ATTORNEY=(user1)"
and
'query.filter = "REF___=(user2)"

is incorrect.

You have placed the variables inside quotes. Alpha has no way to tell that you meant for the values in user1 and user2 to be used.

They should read

query.filter = "ATTORNEY= "+quote(var->user1)
and
'query.filter = "REF___= +quote(var->user2)

[draxx]

Re: Export producing records based on Parent table

Hi Stan, I tried the code you suggested and removed the line "tbl=table.current()". When I run it that way, I get an error message stating that the first variable field in the export is a non existent field. It doesn't recognize any of the fields for the export even though I just ran the query for that table in the preceding code. If I replace the above line regarding the current table, it again goes through all the records that are contained in the parent table and lists all the clients as opposed to the client I specify in the query. Not sure if this makes any difference, but I'm running the script from a button on a form that is based on the set that both table are contained in. I also now understand what you're saying about declaring the variable "(user1)".

[Stan Mathews]

Re: Export producing records based on Parent table

You have a form based on a set. The parent is amergl3 and the child is clients.

This should print the single record for the chosen attorney. (Actually it should print all the records where user1 is the attorney.) If you want the single child record for an attorney attached to the currently visible parent record we'll have to query the set, not just the child table.

dim user1 as C
dim tbl as P
tstr1 = ui_get_text("Client Name","Please Enter Client")
if tstr1=""then
end 
end if
user1=tstr1

tbl=table.current(2)

query.description = ""
query.order = "ATTORNEY"
query.filter = "ATTORNEY="+quote(user1)
query.options = "I"
tbl.query_create()

export.type = 4
export.names = .F.
export.file = "C:\Others_billing.xls"
export.options = ""
export.field_sep = ""
export.record_sep = ""
export.fields = 4
export.field1 = "attorney"
export.field2 = "name"
export.field3 = "address"
export.field4 = "city_state_zip"
tbl.export()

[draxx]

Re: Export producing records based on Parent table

Thanks Stan I'll give it a try. And I wanted you to know after further testing.... just because....lol, my query with the filter as "attorney=(user1)", does in fact work. It does recognise the variable, "user1" and applies it. Maybe it's not supposed to work, but for some reason it does. However, with the information that you have supplied me, from now on I'm going to make sure I declare them properly.

[draxx]

Re: Export producing records based on Parent table

Hi Stan... I'm getting an error message that states "invalid relation type". Perhaps I should scrap this script and start from scratch. Maybe something is corrupt in it. I even tried creating a saved export operation for this and running it silently after the query. I specified to use the current query or filter. When I opened that dialoge box, I notice the current query filter from the query was not showing. It's as if it does not recognise the query after it's run.

[draxx]

Re: Export producing records based on Parent table

Update..... I have to leavr for a few hours but will be back at it this afternoon. Thanks for all your help Stan, I do appreciate it.

[draxx]

Re: Export producing records based on Parent table

Hi Stan, I decided after looking athe script further perhaps I needed to run this in a new session since it's using 2 tables within a set. I have the following code :

dim user1 as C
dim user2 as C
dim tbl as P
dim a_tbl as P

script_spawn("others_billing2")

tbl=set.open("ameagle")

tstr1 = ui_get_text("Client Name","Please Enter Client")
if tstr1=""then
end
end if
user1=(tstr1)

debug(1)

query.description = ""
query.order = "ATTORNEY"
query.filter = "ATTORNEY="+quote(var->user1)
query.options = "I"
tbl.query_create()

tbl = table.current()
export.type = 4
export.names = .F.
export.file = "C:\Others_billing.xls"
export.filter = "CLIENTS->ATTORNEY="+quote(var->user1)
export.options = ""
export.field_sep = ""
export.record_sep = ""
export.fields = 4
export.field1 = "clients->attorney"
export.field2 = "clients->name"
export.field3 = "clients->address"
export.field4 = "clients->city_state_zip"
tbl.export()

END

Hopefully I'm on the right track. I'm just trying to get the first half of my original code working first. Anyway, when I run this from the form where the button is, it asks for the client's name (which it should), and then the "debug" starts up. When it goes through the query, when it gets to the "tbl=table.query_create()", I get an error message stating that the "table.query_create() method cannot be found". Then it just continually cycles through asking me for a client name. I assume it cycles through over and over even if I cancel because there is no error handling that I've inserted. Unfortunately, I have to Cntrl+Alt+Delete and close the application to get out. Any ideas? Thanks for looking.
Scott

[draxx]

Re: Export producing records based on Parent table

Hi Stan, I got the script to work after looking closer at it. I realized that I hadn't actually opened the table I needed. Below is the new script:

dim user1 as C
dim user2 as C
dim tbl as P
dim a_tbl as P
dim tbl1 as P

script_spawn("others_billing2")

tbl=set.open("ameagle")

tstr1 = ui_get_text("Client Name","Please Enter Client")
if tstr1=""then
end
end if
user1=(tstr1)

debug(1)

tbl1=table.open("clients")

query.description = ""
query.order = "ATTORNEY"
query.filter = "ATTORNEY="+quote(var->user1)
query.options = "I"
tbl1.query_create()

tbl1 = table.current()
export.type = 4
export.names = .F.
export.file = "C:\Others_billing.xls"
export.filter = "CLIENTS->ATTORNEY="+quote(var->user1)
export.options = ""
export.field_sep = ""
export.record_sep = ""
export.fields = 4
export.field1 = "clients->attorney"
export.field2 = "clients->name"
export.field3 = "clients->address"
export.field4 = "clients->city_state_zip"
tbl1.export()

END

Now the only problem I have is that it keeps cycling through the script. How do I stop it from doing that? Is there a "script_spawn_close()" function or something? Thanks.....
Scott

[draxx]

Re: Export producing records based on Parent table

Got that part figured out. I needed to put the "Script_spawn()" function on the button and not in the script. However I do have one problem, it is not exporting the record for the client name I'm inserting in the dialogue box. It's exporting only one record, but not the one I'm looking for.

[Stan Mathews]

Re: Export producing records based on Parent table

Script_spawn() does nothing but run a script. If you are seeing cycling it is happening in the coding of that script.

[Stan Mathews]

Re: Export producing records based on Parent table

I don't know what you are trying to accomplish with the second assignment to tbl1 (in blue) but it is probably messing up the flow of your script. Take it out, again.

tbl1=table.open("clients")
query.description = ""
query.order = "ATTORNEY"
query.filter = "ATTORNEY="+quote(var->user1)
query.options = "I"
tbl1.query_create()

tbl1 = table.current()

[draxx]

Re: Export producing records based on Parent table

Hi Stan, just wanted to let you know I think I way over thought this whole thing... thank you OCD....lol. Since I was able to spawn the script in a new session, I rewrote the script simplifing everything including removing the lines you suggested and it's working fine. Thanks for your patience and putting up with me and my ignorance. YOU ROCK.

Scott Naples