Pseudo Calculation Field in tableview form

Classic Servoy
1

I have 3 tables as follows:
(1) INDIVIDUAL table comprising of fields:

  • PK
  • INDIVIDUAL_NAME

(2) ORGANIZATION table comprising of fields:

  • PK
  • ORGANIZATION_NAME

(3) CUSTOMER table comprising of fields:

  • CUSTOMER_TYPE (with values I for Individual or O for Organization)
  • CODE (which contains PK value from either Individual or Organization table depending of CUSTOMER_TYPE defined above)

A customer can be either an individual or an organization. The customer name is not stored in the CUSTOMER table so as to automatically cater for name changes in INDIVIDUAL & ORGANIZATION tables.

In a record view form, there is no problem looking up the customer name from either the INDIVIDUAL or ORGANAZATION table and displaying it dynamically (using the onRecordSelection trigger).

In a tableview form, how is it done?

2

You can use an unstored calculation for that: check the customer_type and return the name from the correct relation. Personally I’d choose a different approach about the tables design though.

3

Would suggest you switch to a table like this to handle both. Dealing with that data-model would be a pain.

Entity:
entity_id
entity_type
entity_name

4

Nicola,

Servoy does not support database-related methods in calculation fields. It would have been perfect if it did. I wonder whether such a case would be receivable.

5

Scott,

I find my data model most logical and normalized. I oversimplified it for the sake of presenting the problem. Please note that my INDIVIDUAL and ORGANIZATION tables contains many more associated columns. I have designed my model so because a SUPPLIER, for example, could also be either an INDIVIDUAL or an ORGANIZATION. Furthermore, an ORGANIZATION or INDIVIDUAL could also be both a CUSTOMER, a SUPPLIER, a STAFF_MEMBER, etc…

I need to present my CUSTOMERs in tableview form in a customized valuelist.

Any other suggestion most welcome.

6

As I told you in my post I agree with Scott: dealing with that data-model will be a real PITA.
Having said that if you want to continue that path you can easily solve your current issue, problems will arise later on.

  • create 2 relationships: customers_to_individuals and customers_to_organizations
  • create an unstored calc in the customers table:
function display_name
{
  if(customer_type == 'I')
  {
    if(utils.hasRecords(customers_to_individuals)) return customers_to_individuals.individual_name;
  }

  if(customer_type == 'O')
  {
    if(utils.hasRecords(customers_to_organizations)) return customers_to_organizations.organization_name;
  }
}
7

Nicola,

Thanks for the example and the advice.

8

NP.
Keep in mind that this calculation will fire a query per every row of the foundset: for a normal foundset that means 200 queries just to display it in a table view. This could be a little slow especially if smart clients are a bit far from the application server (latency rules).
It would be better to make that calculation stored (create a column with the same name as the calc in the customers table), it will save all those queries and it will be update only when customer_type changes.

9

Nicola,

Thanks again for yet another good advice.