Data mapping in fields and grid columns in RC4

[barrymavin]

The data mapping is all now in place in RC4.

In the attributes for a field or grid column you have two new attributes:

"Get data mapping" and "Set data mapping".

So how does it all work you may ask.

Let me explain.

These attributes should be expressions. Let's say you have a numeric value that you want to display formatted as currency.

In the "Get data mapping" specify:



// The second (optional) argument to the currency() function is the number of decimal places.
currency( {} , 4)


This will display the numeric field as e.g. $12,675.4523

Now to edit this and be able to "map" it back into the way it is stored in the database, we specify a "Set data mapping" expression:



// val( ) will strip commas and $ out for us automatically
val( "{}" )


Ok... so now you want to map a numeric "order id" to its description in another table using a foreign key. The KeyLookup() function can do this for us as it will save and restore the cursor state (record position) so as not to affect other parts of the App.


It will also open the table if its not already open but better to open it in the init or load delegate yourself so it does not keep opening and closing it for each keylookup it does.

In the "Get data mapping" specify:



// keylookup(table|alias, indextagname, key, valueExpr)
keylookup("orderdetails" , "orderdid", {}, orderdescription)


In the "Set data mapping" specify:



// keylookup(table|alias, indextagname, key, valueExpr)
keylookup("orderdetails" , "orderddescription", "{}", orderid)


Notice how {} are used to substitute the value to be mapped in the mapping expressions.

If you want to be clever you can call one of your own functions in a custom library and pass "{}" as a parameter to it.

To edit the "Foreign key" value you could specify a choice list using SQL SELECT to populate the combobox control. The mapping to and from the database/table column will be handled for you.

Thats basically it.

[avianmanagement]

Very nice implementation Barry. I look forward to trying this as I use this sort of thing a lot in my apps.

[cyrilbaskir]

In the "Get data mapping" specify:



// keylookup(table|alias, indextagname, key, valueExpr)
keylookup("orderdetails" , "orderdid", {}, orderdescription)


In the "Set data mapping" specify:



// keylookup(table|alias, indextagname, key, valueExpr)
keylookup("orderdetails" , "orderddescription", "{}", orderid)




I have this working in a form with my own data. However, the "Set data mapping" relies on description, which may not be unique.

Instead of doing a keylookup on description, could one get the PK from the Choices?

For example, say Choices for choosing a category is: select catdesc, pk_category from category

So when the user chooses one, the key for the appropriate record is already available in the choices query.

So "Set data mapping" becomes choices.pk_category.

If not, what would you use for "Set data mapping" when the description is not unique, and the user wants the second duplicate.

[barrymavin]

The value returned is a "value expression" so it can be a function call if you need it to be.

[avianmanagement]

Can we have more than one field in the choices but only show one to the user

select catdesc, pk_category from category

but only let user see catdesc


Maybe have an attribute to determine how many columns user sees in list

with choices set to

select catdesc, catPrice, catdiscount, pk_category from category

and attribute set to 3 use would see only catdesc, catPrice, catdiscount

[barrymavin]

No its not possible to show more than one column in a choice list.

[avianmanagement]

OK thanks I'll use a dialogue button to bring up the form to select from as that will do what I need

[cyrilbaskir]

OK thanks I'll use a dialogue button to bring up the form to select from as that will do what I need
Hi David

You and I have similar needs :)
Have you done it yet - if so, how did you implement it?

[avianmanagement]

No I have not done this yet.

What I want is similar to the choices list and if it is not in the list to bring up a form for teh user to add new one in.

I need to show the user more than one field in the list they are seeing though as one might have an item to select.

Basically I want the F2 lookup with autofill capability as we have now in VPM with PSP Libs

To do that I think we will need to wait for ERDtoLinaja so that we have the metadata in place needed to do this.

[HankFay]

If you are using SQL for the choice list, as you are, it's easy enough to macro the field you are returning, and so the user can select the field they want.

The big requirement though is in your data: the field used has to be a candidate key.

Concatenating fields into a string is another choice.

Hank

OK thanks I'll use a dialogue button to bring up the form to select from as that will do what I need