[Answers] Picklist Lookup

[josipradnik]

Q:
how to implement a picklist for a FK field in a table inside a form and grid section.
A:
Use the Choices attribute and specify a SQL Select statement. For numeric fields, use the Choices + Get data mapping / Set data mapping as demonstrated in the example_datamapping App.



Taking the southwind orders table as an example, it contains the customerid field, e.g. 'ALFKI'. For that, I would just use choices - select customerid from customers - and include an 'order by' or 'where' clause if required. The orders table also has employeeid. If I wanted to display the lastname of the employee, rather than their id, I would use data mapping to display the lastname (Get data mapping) and my select statement would be based on the lastname. I would use the Set data mapping to store the employeeid for the selected lastname back to the table.

So if you just want to display a choice list, just use choices and if you want to display a name or a description associated with an id (another field in the lookup table), use the get/set data mapping as well.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/show...ll=1#post12352

[josipradnik]

Q:
Since get/set mapping is still to come for web and mobile, I was wondering how you guys are currently dealing with this?
Say I want to capture a new customer on a form and one of the fields is 'country' (where the list of countries comes from a table with CountryCode and CountryName) - what is the technique for choosing a country by name and saving the code on the customer table?
The neatest mechanism I've seen in other systems is an auto-filtering combobox which I guess is populated with key/value.
A:
what I've come up with is this:

1) create a view that has both the FK field and the FK field's lookup description in it. Note that the Lookup Description is necessarily a candidate key in the parent table, or else a lookup from a combo couldn't work. Don't display the FK: rather display the description.
2) for the table create an "after save" trigger so that a change in the FK description field causes the right FK to be selected.
3) fill the combo for selection in mobile with values from a dynamic request (making it dynamic allows you to apply a filter using values that may only be available in the live record, or in the instance of the app). I haven't experimented with this step yet, although the need will be coming up soon. So long as the .choices property of the control can be set dynamically in the web client, getting the data there one way or the other will be doable in some way.

in the case of country code, you have a meaningful PK that you can trust. Hopefully you won't be using meaningful keys throughout your application, as there are severe performance issues (locking issues) when meaningful pk's are changed.

Q2:
Some tables in our other systems have loads of foreign keys (up to 10+) so this would mean we'd need 10 guid + 10 description fields added to our table to make this work?
do you mean I need to put country code + country name fields on the customer table and drive the trigger on customer.custcountryname to update customer.custcountrycode
A2:
And yes, you will need to make the 10 extra fields. There is an big upside in performance: if this were a traditional FK lookup, you would have a view (parameterized on the PK of the parent) associated with each of the FK's, and those 10 views would have to fire in order to show the descriptions for those 10 FK fields. So, turning the equation around, using the change in description to change the FK, turns out to be a great way to speed up initial display of records, and reduce sudden loads on the SQL engine, which ever one that happens to be. When a new record is added, there will be 10 lookups to do. However if those are done by a trigger on the Lianja SQL Server, those lookups can be written directly against the table, using a SEEK, which is extremely efficient.

---

Q:
Using the southwind database and trying to do a simple invoicing program, I create a new page (page2), add a form section (section1) and drop there the orders table fields. I then add a second section, but this time is a grid(section2) and I add the order details fields (order id, product, price, qty). Then, I try that the price field automatically updates its content with the "unit price" that figures for that product in the products table, whenever you change the product. If you select product 1, in the price field of the grid should automatically appear the "unit price" of the product 1 that is in the products table. I thought that I could get that with data mapping but I'm a little bit lost, as I also want the user to be able to overwrite that value. I've tried to specify in the "get data mapping" field of the price column: keylookup("products","productid",{},unitprice), and leaving set data mapping blank, but it doesn't work.
A:
The keylookup() needs to reference the order_details.productid, not the current unitprice cell value, which is what the {} means in

keylookup("products","productid",{},unitprice)

So it becomes:

keylookup("products","productid",order_details.productid,unitprice)

The 'Recalculate' attribute on the productid column should be checked to True to trigger the recalculation of calculated and readonly columns in the Grid.

---

---

​All topics in [Answers] alphabetically:http://www.lianja.com/community/showthread.php?2717-Answers

[josipradnik]

Q:
Why does my keylookup returns false?
I want to connect my company_code [vt_employee] to the company_code[vt_company] to get the company name.
Here is my scenario:
1. I am using virtual tables from a mysql engine and successfully created a db in Lianja. The MySQL db itself had indexes.
2. Since the keylookup deals with indexes, I was thinking that perhaps my indexes were not incorporated to the virtual tables so I added the indexes in the console.
ALTER TABLE vt_employee ADD FOREIGN KEY (company_code) TAG company_code;
3. Added keylookup code :
(Get Data Map)

keylookup("vt_company", "company_code", "{}", name)

(Set Data Map)

keylookup("vt_company", "name", "{}", company_code)

Did I misunderstand the process or did I miss something? It doesnt work on the grid and on the form

A:
This usage of data mapping against virtual tables using KEYLOOKUP() is not supported.
Index lookups in Lianja are only relevant to the embedded database engine.
With external SQL databases you need to perform a SQL SELECT in the get/set data mapping. You can accomplish this using a Lianja function in a custom library.
Be sure to create native indexes on the columns you want to lookup so that the query optimizer will use them for high speed row selections.

---

There have been several requests to provide the ability to drop down pick lists and custom forms beneath a UI control when editing a desktop form section.
I have added in some new functionality to enable this.
The “Form” class now has a new method called showDropDown(cControlID, nWidth, nHeight)
cCcontrolID is in the page1.section1.field1 notation and it identifies the UI control that you want to attach the DropDown Form to.
You can create the form inside a “DialogButton” delegate or in a hotkey delegate if you want to use function keys to drop down pick lists.
Hint: add a container to the form and set its layout to “grid” is the easiest way to create a responsive UI form.

// Example
myForm = createObject(“Form”)
// add a search panel, grid and command buttons
myForm.showDropDown(“page1.section1.field21”, 250, 250)

// You should have a means the close the DropDown Form such as a CommandButton click event
myForm.close()

// closing the form will not destroy it rather it will hide it so it can be dropped down again

This mechanism opens up a lot of possibilities for pick lists with complex query forms. I will post some screenshots in due course.

---

I want to share with all the user some line of code to test this new feathure.
This is the result


when I click on the dialogbutton, I display the dropdown form
in the interacticechange of the text box I filter on real time the table, by select the row and click "OK", the ProductId are copied to the table..

proc sp_close_handler()
    p_myform.close()
endproc

proc sp_ok_handler()
    p_myform.close()
    replace ORDER_DETAILS.PRODUCTID with myprod.productid
    lianja.get("sampleproductsentry.section3.field2").text = etos(myprod.productid)
endproc

proc sp_change()
    select * from products where etos(products.productid) like "*" + p_searchbox.text + "*" or products.productname like "*" + p_searchbox.text + "*" into cursor myprod
    p_mygrid.refresh()
    p_searchbox.setfocus()
endproc

////////////////////////////////////////////////////////////////
// Event delegate for 'dialogbutton' event
proc productiddropdownsearch()
    
    public p_myform, p_searchbox, p_mygrid
    
    p_myform = createObject("Form")
    p_myform.resize(600, 500)
    p_myform.addObject("mycont", "Container")
    
    mycont.layout = "Grid"
    mycont.autosize = 1
    
    mycont.addObject("oContTop", "container", 0, 0)
    oContTop.backcolor = "gold"
    oContTop.fixedheight = 25
    oContTop.margin = 20

// TOP
    oContTop.addObject("o_combo", "Combobox")
    o_combo.move(1, 1, 120, 20)
    o_combo.additem("a")
    o_combo.additem("b")
    o_combo.additem("c")
    
    oContTop.addobject("searchbox", "textbox")
    searchbox.move(130, 1, 120, 20)
    searchbox.interactivechange = sp_change
    p_searchbox = searchbox
        
// MIDDLE
    mycont.addObject("oContMiddle", "container", 1, 0)
    oContMiddle.backcolor = "lightgreen"
    oContMiddle.autosize = 1    
    p_oContMiddle = oContMiddle
    
    
// BOTTOM
    mycont.addObject("oContBottom", "container", 2, 0)
    oContBottom.backcolor = "lightgray"
    oContBottom.fixedheight = 25    
            
    oContBottom.addObject("sp_close", "commandbutton")
    sp_close.move(1, 1, 60, 20)
    sp_close.text = "Close"
    sp_close.click = sp_close_handler

    oContBottom.addObject("sp_ok", "commandbutton")
    sp_ok.move(80, 1, 60, 20)
    sp_ok.text = "Ok"
    sp_ok.click = sp_ok_handler

    select * from products into cursor myprod // This could be a parameterized multi-table join
    p_mygrid = oContMiddle.browse("browse noedit noactionbar", "myprod") // last 2 args are optional
    // note that the grid object is returned from browse() so you can set properties and call methods on it
    p_myform.showDropDown("sampleproductsentry.section3.field2", 400, 360)

endproc

I do not see the scoll bar.. :-)

A:
Did you try form's attribute Scrollbars >0 ?

Scrollbars

RW

Numeric

Scrollbars: 0 = none, 1 = horizontal, 2 = vertical, 3 = both

A2:
a few suggestions.
1. You can press escape to close the dropDownForm.
2. You can double click a grid row to call the dblclick() proc that you can specify on the browse() call. That could be set to your ok button clicked handler.
3. If the third parameter to showDropDown() is 0 then the form width will auto adjust to the width of the textbox.
scrollbars on the grid will appear if more rows than can be seen in the viewport and pagination is false.

Q2:
I've try to set the right layout for the grid, but how can I set the pagination property for tge grid?


The rows displayed on the grid is greater than the row visible..
How can I set the height and width of the grid inside the container?

A:
Because you are laying out the form using a grid layout you should remove the setting of autosize from oContMiddle. A grid layout will autosize into the grid cells automatically.
​

---

---

All topics in [Answers] alphabetically: https://www.lianja.com/community/sho...ll=1#post13748

These answers are also systematized on the site "Lianja developer": https://lianjadeveloper.wordpress.co...cklist-lookup/