Update table

[den1s]

Hello.

I have a dialog component with two dropdown lists (Approval_No which is multiselect and Collection_No).

In the AfterValidate Event i am attempting to take the values selected from the 'Approval_No' and 'Collection_No' dropdowns and place it in the 'assets' table.

The way this works is, you select one or many approval numbers from the multiselect dropdown and then select a collection no. The collection no will be inserted in the selected records matching the approval no(s) selected.

Dim tbl as P
tbl = table.open("assets")
[B]query.filter = "Approval_No =" crlf_to_comma(approvalNumber.dump())""[/B]
query.order = ""
query.options = ""
indx = tbl.query_create()
update.fields = 1
update.field1 = "Collection_No"
[B]update.expr1 = Collection_No[/B]
tbl.update()

I think my problem is how i am refering to the dropdown list box in both (BOLD) sections of the code.

Can anyone please advise me how to fix this.

Denis

[den1s]

Re: Update table

BUMP - Anyone?

[Al Buchholz]

Re: Update table

Denis

I think you have pinpointed the line of code that is causing the problem.

The best way that I have found to debug these issues is to use the a5_show_variable() command. This will show what is in the variables and you can see exactly what is happening to a variable as you move through the code.

Don't forget to remove them when you have it debugged.... ;)

ps - since you have a multi select, you'll need to deal with the translation to the filter in another manner. Use of a *for_each() to build the expression for query.filter is one technique to use...

[den1s]

Re: Update table

Thanks for the tip Al.

I am a real beginner with xbasic and am learning as i go along. Do you have an example how i may refer to my dropdown control field (Collection_No) in this line of code?

update.expr1 =

[Pat Bremkamp]

Re: Update table

Your problem is a common one. For a multiselect, your filter will be built as

approval_No=num1 .or. num2 .or. num3.....

but you want it to read
Approval_No=num1 .or. Approval_No=num2 .or. ....

So as Art says, you need the for each. The following is based on an example in one of the example pages

vFilter = approvalNumber.dump()
if vFilter=""
end
end if
vFilter = *for_each(x,"Approval_No = " + quote(x),vFilter)
vFilter = strtran(alltrim(vFilter), crlf()," .or. ")

Pat

[den1s]

Re: Update table

Thanks Pat and Al.

I have amended my code, but it appears that all records in the table are being updated. I cant see the obvious mistake in the filter code.

What do you make of it?

tbl = table.open("assets")
'Define a query so that only selected records are updated.
vFilter = approvalNumber.dump()
vFilter = *for_each(x,"Approval_No = " + quote(x),vFilter)
vFilter = strtran(alltrim(vFilter), crlf()," .or. ")
indx = tbl.query_create()
update.fields = 3
update.field1 = "Collection_No"
update.expr1 = time("ddmmyy0h0m0s")
update.field2 = "Company"
update.expr2 = "'Company'"
update.field3 = "Collect_PO_Number"
update.expr3 = "'PO_Number'"
tbl.update()

The reason i have put a text entry in the update.exp2 and expr3 is because i dont know how to refer to the dropdown controls (Company/PO_Number) in my dialog component. So frustrating!

update.field2 = "Company"
update.expr2 = "'Company'"
update.field3 = "Collect_PO_Number"
update.expr3 = "'PO_Number'"

[Al Buchholz]

Re: Update table

I don't see an a5_show_variable()....

[den1s]

Re: Update table

Where would i put the show variable command? Sorry Al, i have never used this before. What variable show i be monitoring?

[Al Buchholz]

Re: Update table

Thanks Pat and Al.

I have amended my code, but it appears that all records in the table are being updated. I cant see the obvious mistake in the filter code.

What do you make of it?

tbl = table.open("assets")
'Define a query so that only selected records are updated.
vFilter = approvalNumber.dump()
vFilter = *for_each(x,"Approval_No = " + quote(x),vFilter)
vFilter = strtran(alltrim(vFilter), crlf()," .or. ")
[COLOR=Red]a5_show_variable(vFilter)
query.filter=vFilter
[/COLOR] indx = tbl.query_create()
update.fields = 3
update.field1 = "Collection_No"
update.expr1 = time("ddmmyy0h0m0s")
update.field2 = "Company"
update.expr2 = "'Company'"
update.field3 = "Collect_PO_Number"
update.expr3 = "'PO_Number'"
tbl.update()

The reason i have put a text entry in the update.exp2 and expr3 is because i dont know how to refer to the dropdown controls (Company/PO_Number) in my dialog component. So frustrating!

update.field2 = "Company"
update.expr2 = "'Company'"
update.field3 = "Collect_PO_Number"
update.expr3 = "'PO_Number'"

Note the 2 lines in red.

What are the variable names for the dropdown controls (Company/PO_Number) in your dialog component?

[den1s]

Re: Update table

Thanks Al, Yes. They are Company and PO_Number.

I cut and pasted your amended code and the strangest thing happened.

I selected one approval_no, when i pressed submit, only that record is now in the table, all other entries are erased, and what make this even more wierd, the values in the dropdown still show the approval numbers for all records.

[Lenny Forziati]

Re: Update table

I don't see an a5_show_variable()....

Al, you can't use a5_show_variable() in a web app. It is a desktop UI command that has no meaning in the context of a web page or component.

[Al Buchholz]

Re: Update table

Denis

The records are not erased, they are filtered out.

With the form open, do a control-I and change the index that is being used...

I'd suggest using vCompany and vPO_number as variables names in the dialog and then use those variables in the expressions.. (they should be dimmed SHARED)

update.expr2 = vCompany
.
.
update.expr3 = vPO_Number

[Al Buchholz]

Re: Update table

Al, you can't use a5_show_variable() in a web app. It is a desktop UI command that has no meaning in the context of a web page or component.

Oops missed that we're in the web app forum....

So he'd have to work this out as a script or function in the desktop model and then apply that code to the web page...

[den1s]

Re: Update table

Al,

I have just looked up the dimmed SHARED and it's also a desktop function.

I appreciate your help in trying to get this resolved.

Denis

[Pat Bremkamp]

Re: Update table

Denis,

Actually, on the web, you don't dim variables as shared.

If you have a dropdown box set as single and you want to refer to the value in the script, then you refer to it using the name you gave the control when you created it. The name defaults to something like "dropdown1", but you probably changed that. Look in Control Settings -> Name

You can leave the scope as local.

As you have seen, if the scope is multiselect, then the control becomes an array.

Pat