Picklist Lookup

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

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

So it becomes:

Code:
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.



Advertisements

Picklist Lookup

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.