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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s