Grid data from multiple tables

Hello,

I've gone through the site tutorials and I have a basic understanding on how things are set up and used. I'm now ready to try explore Lianja on my own.
What would be the best way to create a page with a grid that displays data from several tables?

Using the Southwind database, here is the output I would be looking for (where | separates the columns):
Contact Name | Company Name | Order ID | Order Date | First Name | Last Name

- Contact name & company name comes from Customers table
- Use the customerid to link to Orders table to get the order id and order date
- Use the employeeid to link to Employees table to get the first and last name.

An sql statement might look similar to:

Code:

---

SELECT Customers.contactname, Customers.companyname, Orders.orderid, Orders.orderdate, Employees.firstname, Employees.lastname
FROM Customers
INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Customers.contactname;

---

Thanks,
Cory

Hi Cory,

Have a look at the example_datamapping App included in the distribution. Double-click on the header of the 'Product id' column in the Grid and you will see that it uses Get and Set data mapping to map the orders.productid numeric field to the products.productname product description character field. It means that you can use a standard Grid Section based on the Orders table and lookup the other fields you require and map the values. There is also an example of mapping the orders.employeeid to the employees lastname and firstname fields in the example_tabletui App.

There's no tutorial as yet for Data Mapping, but lots of information here in the forum - just do a search for data mapping or post a question if you get stuck.

Regards,

Yvonne

Thanks Yvonne. That gives me a starting point to reference.

I'm not seeing any difference of the grid other than drag and drop of the order details onto a grid. The Get/Set data mapping is blank for all columns, including the product id column.

Hi Cory,

You should see these attributes set for PRODUCTID:

Get data mapping: keylookup("products", "productid", {}, productname)
Set data mapping: keylookup("products", "productname", "{}", productid)
Choices: select productname from products

Regards,

Yvonne

Attachment 383

Thank you Yvonne. I have added each of the sections and the data mapping now works.
I can still use assistance.

I created a dummy field with a size of 45 in order details.
I then created a section with a grid and dragged the order details to the grid.
After changing the sections, all I get returned is 'false'.

Data Source: m.DUMMY (I've also tried without the m.)
Choices: select orderdate from orders
Get Data Mapping: keylookup("orders","orderid",order_details.orderid ,orderdate)

What am I doing wrong?

Hi Cory,

The second parameter for KEYLOOKUP() is the name of an index tag, so make sure that you do have an index with the tagname 'orderid' on the orders table, e.g. from the Console (assuming the southwind database and orders table are already open):

Code:

---

select orders
list index

---

if it doesn't exist, then:

Code:

---

index on orderid tag orderid

---

Regards,

Yvonne

Yvonne,
This is what I received when using the script you provided.

Code:

---

Production DBX file: orders.dbx   
    Index TAG: CUSTOMERID
        Key: CUSTOMERID
        Type: Character
        Len: 13
    Index TAG: EMPLOYEEID
        Key: EMPLOYEEID
        Type: Numeric
        Len: 8
    Master Index TAG: ORDERID_SEARCH
        Key: ORDERS.ORDERID
        Type: Numeric
        Len: 8

---

Cory

Hi Cory,

Right, that makes sense. So you can use the existing 'ORDERID_SEARCH' tag as that had the ORDERID field as its key, or you can create a new index tag if you particularly want it to be called 'ORDERID'.

To build the new index tag:

Code:

---

select orders
index on orderid tag orderid

---

To use the existing index tag, assuming the current value (this is what {} gives you) of the column is order_details.orderid:

Get Data Mapping: keylookup("orders","orderid_search",{},orderdate)

Regards,

Yvonne

Hi Yvonne,

In order to avoid potential problems, I haven't changed to index tag.

Using the third parameter as {} returned blank data. I did have to have order_details.orderid included in order to get the results returned.
This data mapping is working for two tables. Now how would I expand it to include several tables.

For example, I would like the customer contact name to appear in the order details grid.
order details links to order by the orderid (orderid_search).
orders links to customers by the customerid.

What would be the best way to handle this?

Thanks,
Cory

I was able to obtain data from a third table using nested keylookup.

For future reference, here is what I used:

First test - display the customer id in the order details grid - customer id was an added blank dummy column to the table:
Choices: select customerid from orders
Get Data Mapping: keylookup("orders", "orderid_search", order_details.orderid, customerid)

Second test - display the contact name in the orders grid - contact name was an added blank dummy column to the table:
Choices: select contactname from customers
Get Data Mapping: keylookup("customers", "customerid", orders.customerid, contactname)

Finally, combine the above two get data mapping sections.
(using order_details to display then contact name - contact name was an added blank dummy column to the table)
Choices: select contact name from customers
Get Data Mapping: keylookup("customers", "customerid", keylookup("orders", "orderid_search", order_details.orderid, customerid), contactname)

Cory

Nice work.

Hank