Wednesday, October 17, 2012

FoxPro Grid RecordSource With Tables In A Chained Relationsip

Let's assume a case like this, where the tables are linked in a chained relationship.:

SELECT product
SET ORDER TO prodidx

SELECT customer
SET ORDER TO custidx
SET RELATION TO prod_id INTO product ADDITIVE

SELECT invoice
SET ORDER TO invoiceidx
SET RELATION TO cust_id INTO customer ADDITIVE

Now I would like to show "customer" records in a Grid (e.g. grdCustomer), based on the current record in "invoice" (e.g. record no. 1):

SELECT invoice
GOTO 1

WITH THISFORM.grdCustomer
    .RECORDSOURCE              = 'customer'

    .colCustId.CONTROLSOURCE   = 'customer.cust_id'
    .colProdId.CONTROLSOURCE   = 'customer.prod_id'
    .colProdDesc.CONTROLSOURCE = 'product.prod_desc'        && Get value from "product" relationship.
ENDWITH

The expected behaviour would be "colProdDesc" will show the "prod_desc" linked to each "customer" record's "prod_id" in the Grid.
However, If you run this code, it will not work. Even worse, there would be no record shown at all in the Grid.

This happens when the Grid's RecordSource ("customer") has another relationship linked to it ("invoice").

As such, as a workaround to show the relationship values (product.prod_desc), we just need to add "ALLTRIM()" to the ControlSource expression.

So we change
.colProdDesc.CONTROLSOURCE = 'product.prod_desc'
to
.colProdDesc.CONTROLSOURCE = 'ALLTRIM(product.prod_desc)'


This should fix the issue and show the records in the Grid correctly.



If you find this post helpful, would you buy me a coffee?


No comments:

Post a Comment