Problem using a variable in lookup function

[Sooty57]

Hi all,

Despite having got this to work once (then my daughter turned off pc before I had saved it grrrr), I cannot repeat my earlier (lucky) success.

I have created everything in action script as I'm not yet very proficient in xbasic.

The script displays a dialog box for the user to select a record from Table A and Table B, which are then stored in shared variables. I know these variables are OK because I can display them in a message box.

The script then opens a form for creating a new record in Table C.

I then want to populate some of the fields on the form using data from Tables A & B. I know this is a clumsy way of doing things, but I am stuck with the table structure from an existing Access database (in use for the past 10 years).

If I try to update a field value on the form with
lookup("Table_A", "RecordID = var>Variable_A","Required_Field")
I get an error (though I could swear it worked before.)

lookup("Table_A", "RecordID = 'Actual_Variable_Value'","Required_Field") gives the required result, so I know the expression is OK.

Obviously the syntax is wrong when trying to use the variable as the value for filtering.

Any help would be REALLY appreciated, as I have been stuck on this for 8 hours now, and it's 2.30 in the morning.

[DaveM]

Re: Problem using a variable in lookup function

lookup("Table_A", "RecordID = var>Variable_A","Required_Field")
' should be:  
lookup("Table_A", "RecordID = var->Variable_A","Required_Field")
'I would even go to:
lookup("Table_A", "trim(RecordID) = alltrim(var->Variable_A)","Required_Field")

I see this and maybe it is not the only thing needed.???.


.

[Sooty57]

Re: Problem using a variable in lookup function

Thanks for replying Dave.

I had actually made a typo in the original post (working too late), and had used "var->" .

Below is the exact line used for the 'set value' property of the form field.....

lookup("funders_new","Funder_Id = Var->currFunder","Funder_Name")

When I use this, the error message says that the lookup function can't be used in this context.

If I replace the variable with a known value, e.g.:
lookup("funders_new","Funder_Id = '00000000026'","Funder_Name")
Everything works as expected.

[Sooty57]

Re: Problem using a variable in lookup function

Curiouser and curiouser as Alice in Wonderland would say.....

I re-entered the expression exactly as before and no more error. However, if I select Funder A from the dialog box the form shows the details for Funder A. If I then select Funder B the form is still populated with details for Funder A, even though the contents of the variable are updated to Funder B.

[Tom Cone Jr]

Re: Problem using a variable in lookup function

Data entry sequences are difficult to describe in sufficient detail. Suggest you put together an example (using non private or dummy data) and attach it to a reply here, with instructions for us to follow in order to trigger the problem.

[Sooty57]

Re: Problem using a variable in lookup function

Thanks Tom - I will do that

[Stan Mathews]

Re: Problem using a variable in lookup function

I would have used

lookup("funders_new","Funder_Id = "+[COLOR="Red"]s_quote(Var->currFunder)[/COLOR],"Funder_Name")

In most cases a reference inside of double quotes is interpreted as text, not a variable. Placing the reference outside of the double quotes and properly concatenating it generally resolves the issue.

Consider this from the interactive editor.

currfunder = "00000000026"

? "Funder_Id = Var->currFunder"
= "Funder_Id = Var->currFunder"

? "Funder_Id = 'Var->currFunder' "
= "Funder_Id = 'Var->currFunder' "


? "Funder_Id = "+s_quote(Var->currFunder)
= Funder_Id = "00000000026"

[Tom Henkel]

Re: Problem using a variable in lookup function

I just tested with an interim step and got appropriate results. I made the filter a varaible and just placed that variable into the lookup. I think you are having an issue with the variable not being translated properly because it is in quotes. I am doing a lookup into my user table. My user-ID is "SYS02". Because the field is a character field, the filter must be Fieldname = "variable".

Unless you include the quotes, it cannot resolve the filter expression.

dim var1 as C
var1 = "SYS02"

dim filt as C
filt = "user ="+ quote(var->var1)

?filt
= user ="SYS02"

?lookup("secure2.dbf",filt,"Name")
= "Thomas Henkel                 "

Hope this helps,

Tom

[Sooty57]

Re: Problem using a variable in lookup function

Thanks for all your help guys. After following your advice verything is running smoothly now.

Because I hate reading forum threads where people post "Sorted", without giving details how, I have posted solution below. This may also be of use to those more used to Action Script than xbasic (like myself)......

I placed a new action in the action script editor (Inline xbasic), and inserted the following code.....

dim filtContact as c
filtContact = "Contactid = " + quote(var->currContact)
dim contactName as c
contactName = lookup("Funder_Contacts",filtContact,"Contact")
dim contactPos as c
contactPos = lookup("Funder_Contacts",filtContact,"Position")
dim contactPhone as c
contactPhone = lookup("Funder_Contacts",filtContact,"Telephone")
dim contactAdd1 as c

Create an action to open the form for creation of a new record.

Add another action from the editor (Set Value Property) to enter the values into the relevant fields on the form - this is a breeze because the variables declared in the xbasic action will all appear in the action script editor.

Yes, I know it is not the best way to do things but, as I said in the original post, I am limited by the table structure and 10 years of data from an existing Access database.

[CALocklin]

Re: Problem using a variable in lookup function

Something like this:
lookup("funders_new","Funder_Id = Var->currFunder","Funder_Name")

only works if the variable is a GLOBAL variable.

Think about it like this: The lookup is going "outside of the script" to do its thing. Therefore, it won't recognize any variables that are local to the script and enclosed in quotes.

If, instead you did something like this where the variable is not enclosed in quotes, it works because the variable is replaced with its value before the lookup runs:
filter = "Funder_id = \"" + var->currFunder + "\""
(same result as filter = "Funder_id = " + s_quote( var->currFunder )
lookup( "funders_new", var->filter, "Funder_Name" )
(The "var->" is optional in this case but not when enclosed in quotes and referencing a global variable.)

The same thing is true of filters and orders for layouts.

The reason the s_quote works is that it is actually replacing the variable name with the actual value in the variable. The s_quote function is just (supposedly) an easier way of doing this:
Lookup( "funders_new", "Funder_Id = \"" + var->currFunder + "\", "Funder_Name" )
which should look very similar to the above where I defined a filter.

Personally, I actually find it more confusing to use the s_quote thing - but I think I'm the only one that feels that way. Maybe because I've often been known to have many "s_quote" situations in one filter string. After a certain point you have to be careful no matter how you do it.

Confused yet?

[Sooty57]

Re: Problem using a variable in lookup function

only works if the variable is a GLOBAL variable.

Actually none of the variables are global, and everything is working great. I'm not arguing, just stating my observations.

[CALocklin]

Re: Problem using a variable in lookup function

It depends on how they are used. If "s_quoted" they will work whether global or not.

[Sooty57]

Re: Problem using a variable in lookup function

None of the variables are s_quoted() , just quoted()

They are, however all used in the same script.

[Stan Mathews]

Re: Problem using a variable in lookup function

Quote() and s_quote() are equivalent for character data types. S_quote() is extremely handy when dealing with date, numeric, and logical data types and as Cal noted, mixtures..