Data mapping [examples]

Return a string containing the specified field’s getdatamapping column constraint

GETDATAMAPPING

open database southwind
use order_details
? getdatamapping(1,"productid")
keylookup("products", "productid",{}, productname)
? getdatamapping("order_details","productid")
keylookup("products", "productid",{}, productname)

https://www.lianja.com/doc/index.php/GETDATAMAPPING()

---

Return a string containing the specified field’s setdatamapping column constraint

SETDATAMAPPING

open database southwind
use order_details
? setdatamapping(1,"productid")
keylookup("products", "productname", "{}", productid )
? setdatamapping("order_details","productid")
keylookup("products", "productname", "{}", productid )

https://www.lianja.com/doc/index.php/SETDATAMAPPING()

---

Column constraint to specify the display data mapping for a column

open database southwind
alter table "products" modify constraint supplierid set choices 'select companyname from suppliers order by companyname'
alter table "products" modify constraint supplierid set getdatamapping 'keylookup("suppliers", "supplierid",{}, companyname)'
alter table "products" modify constraint supplierid set setdatamapping 'keylookup("suppliers", "companyname","{}", supplierid)'

https://www.lianja.com/doc/index.php/SETDATAMAPPING_Column_Constraint

---

Column constraint to specify the display data mapping for a column

 

open database southwind
alter table "products" modify constraint supplierid;
         set choices 'select companyname from suppliers order by companyname'
alter table "products" modify constraint supplierid:
        set getdatamapping 'keylookup("suppliers", "supplierid",{}, companyname)'
alter table "products" modify constraint supplierid;
        set setdatamapping 'keylookup("suppliers", "companyname","{}", supplierid)'

https://www.lianja.com/doc/index.php/GETDATAMAPPING_Column_Constraint

---

The Get data mapping determines the value that will be displayed in the cell.

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

The Set data mapping determines the data that will be written back to the table when the cell’s value is updated.

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

https://www.lianja.com/doc/index.php/Data_Mapping

---

Get data mapping and Set data mapping

Q:
I think is related with data mapping, but I’m not sure. 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.

---

---

Get data mapping and Set data mapping

Call a function from your ‘Get data mapping‘ and pass in the “{}” or {} (char/num) value as a parameter. 
In your function, check the value (pattern match comparison or character value corresponding to the number, etc.) and return what you want displayed in the cell. 
Your ‘Set data mapping‘ should then set the value you want stored back to the field.

e.g. this will call the p_mymap function and pass the current character value of the cell:
Get data mapping:

Code:

p_mymap("{}")

---

For a Form Section or the Form View of the Grid (‘Split grid’ or ‘Double click to edit’) you can use the Data Mapping, e.g.
Get data mapping:

Code:

transform({},"999,999,999.99")

Set data mapping:

Code:

val("{}")

---

Q:
Starting to get to grips with data mapping & thought the obvious place to start was to use it to tidy up data entry – specifically PROPER() for names & addresses etc in fields.
However :
Get data mapping –

Code:

PROPER("{}")

Set data mapping –

Code:

PROPER("{}")

doesn’t seem to do anything. Do we have to call a function / our function to do the job from Lostfocus() instead?
We would like to PROPER() the data and refresh each field as the user leaves it.
A:
Set data mapping only occurs if you change the data just as a change delegate is only called when you change the data. 
You can write a small admin script to tidy up your data then inside a beforeupdate delegate you can transform input in your live app.

---

---