Conditional self relationships

Classic Servoy
1

I need a self relationship based on whether a field has content or not.

In FMP this would be a snap. Create a calculation populated with a “1” if the target field is not empty. The relationship is then between the constant and this new calc.

But in Servoy it’s not possible to use a calc as the foreign key. What’s recommended in this situation?

2

Can you make the calculation stored?

3

HJK:
Can you make the calculation stored?

Is that the ONLY way to do solve this problem with SQL?

I could indeed set up an integer field, posting a “1” into it each time the target field is given a value, giving it a “0” each time the target is emptied. That solution feels vulnerable to error. There surely must be a robust way of addressing this issue in SQL.

My solutions in FMP frequently employed calculations on the “right” or “foreign” side of the relationship. I can’t help but believe this is a common need in SQL.

4

I guess the best solution is to use a SQL query…

5

Hi Morley,

Harjo probably meant that you could set up a calculated field whose value was stored and then it could be referenced in a self join relationship.

This then acts in a similar fashion to FM where constant = calculated_fk

So you have a choice of whether to use an event driven method or a stored calculation to set the value of the ‘calculated_fk’

To make the calculated field stored you need to ensure that the calculation has the same name as the target field !

Cheers
Harry

6

Thanks all.

Harry, what do you mean by “event driven” in this context?

7

Thinking about an onAction, onDataChange etc where you will progamatically change the data in the field via a method.

You are more in control here as nothing is hard coded and it is more flexible whereby the content of the field could ultimately be driven by a variety of other conditions prevailing within the record !

Cheers
Harry