Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Grid data from multiple tables

  1. #1
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658

    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

  2. #2
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,831
    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

  3. #3
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    Thanks Yvonne. That gives me a starting point to reference.

  4. #4
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    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.

  5. #5
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,831
    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

    Name:  data_mapping.jpg
Views: 251
Size:  102.2 KB

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    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?

  7. #7
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,831
    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

  8. #8
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    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

  9. #9
    Lianja Team yvonne.milne's Avatar
    Join Date
    Feb 2012
    Location
    Berkshire, UK
    Posts
    1,831
    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

  10. #10
    Senior Member
    Join Date
    Jul 2013
    Location
    Ontario, Canada
    Posts
    658
    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

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us