SQL_LOOKUP question

[dfricke10]

I think I'm WAY overthinking this. I have a complex UX that I want to do a separate SQL_Lookup in it but am not sure how to go about it. So, let's start with a simple test UX.

UX
SQL Connection Name: SQL
Table Name: Customers
Fields: customer_id, FirstName, LastName

On the UX there are 3 fields.
customer_id
FirstName
LastName

The customer_id field is pre-filled with the customer_id value (say '13835'). I have a button that onClick would lookup in the Customers table and return FirstName and LastName and insert into those fields in the UX.

This is probably the most blatantly simple thing on earth, but I can't seem to figure out where to start.

I know that there's a Lookup and Fill in Fields Action Javascript, but I want to do this the long way so I can modify and expand to my complex UX.

[Davidk]

Re: SQL_LOOKUP question

You could go an interesting route and create you own Ajax Callback... calling an XBasic function which simply fires off a SQL Select and then returns the data. It's quite straight forward.

Or, you can call the Javascript Action "Lookup and Fill-in Fields" which... essentially... does the same thing. There's no error checking or anything else... just a simple lookup.

[asap rich]

Re: SQL_LOOKUP question

I want to do this the long way so I can modify and expand to my complex UX.

In what direction would you take it? I'm not highly experienced, but I've seen several different ways of doing this.

[Davidk]

Re: SQL_LOOKUP question

Here's a sample UX. The Button makes an Ajax Callback to an XBasic function which runs a SQL Select statement.

We get the data and convert the resultSet into a JSON string. Then we pass that string back to the client as a parameter in a Javascript function call.

The Javascript function takes that string and parses it into a JSON Object. Then we get the data from that object and set the values in the UX.

There's more error checking that can go in, but the basics are here.

Change the Connection String name in the XBasic function to your connection string,

Change your table name... and ensure the SQL Select fieldnames match your fields.

In the Javascript function, ensure the .setValue() fieldnames match the Controls in the UX.

[CharlesParker]

Re: SQL_LOOKUP question

Excellent example DK - thank you for that.

[dfricke10]

Re: SQL_LOOKUP question

Perfection!

[madtowng]

Re: SQL_LOOKUP question

This might be a crazy question, but how did the customer_id field get populated ?

[dfricke10]

Re: SQL_LOOKUP question

For the test, it was hard coded as the Default Value.

[madtowng]

Re: SQL_LOOKUP question

This code should work in your UX.
I like to use sql_lookup when I need to check on one or 2 columns, or just to get a count.
I used strtran() to avoid having to setup arguments.

sqlcode ="customer_id = ''"
sqlcode = strtran(sqlcode,"<ux cust_id>",e.datasubmitted.customer_id)
luData = sql_lookup("::name::connection","customers",sqlcode,"concat(FirstName,'|',LastName)")
e._set.FirstName = left(luData,at(luData,"|")-1)
e._set.LastName = right(luData,rat(luData,"|")-1)