Finding the right record: Relations difficulties

Classic Servoy
1

Hi,

I’ve been looking at this for quite some time now and I have no idea how to do this in Servoy, so, if anyone has an idea, please let me know…

Got the following setup:

  • 1 Products table
  • 1 TranslationTable
  • The TransLationTable contains the following columns:- TransLationID (PK),
  • ProductID,
  • LanguageKey,
  • StartDate,
  • EndDate
  • Based on the StartDate and EndDate, there can always be only one “Active” Translation for a product.
  • The last Translation has an EndDate that is null
  • In can be that a Translation change is entered with a StartDate in the future

Now, with this setup, I want to create a list/tableview of all Products, displaying the “name” of the product based on the active Translation, for the Locale (LanguageKey) with which Servoy is operating

Now, the challenge lies in the fact that to find the Active translation, you need to find the record in the Translation table based on the following logic:

StartDate <= now() and (enddate is null or enddate > now())

Trying to do this directly through a Relation failed, because a Relation can’t contain an “OR” clause

Secondly, I tried creating an Calculation in the TranslationTable (both stored and unstored) that would either return 1 or 0 to indicate an active transaction, or would return the enddate or now() + 1 day if the enddate would be null, but this didn’t help, because you cannot use calculations in the right side of a Relation.

Any ideas how to get this done? Tnx in advance,

Paul

2

pbakker:
Secondly, I tried creating an Calculation in the TranslationTable (both stored and unstored) that would either return 1 or 0 to indicate an active transaction, or would return the enddate or now() + 1 day if the enddate would be null, but this didn’t help, because you cannot use calculations in the right side of a Relation.

Store the calculation and then you can use it for the relation. Pay attention that the calc is updated correctly when you need to use it.

3

On the right side of the Relation?

I do not see the calculation there in the Relation Editor’s Foreign Key’s dropdown lists where I need it (the relation is from Products to Translations)

It does show up on the left side (Primary Key) whether it’s stored or not, but that is not where I need it.

Paul

4

pbakker:
On the right side of the Relation?

I do not see the calculation there in the Relation Editor’s Foreign Key’s dropdown lists where I need it (the relation is from Products to Translations)

Don’t look in the calculation section, look in the regular fields section, you can use the field where the calculation result is stored.

5

aha, didn’t know it worked that way.

So, two questions arise now:

  • How can I display the real Translation Enddate somewhere? For example on the form Translations, where SuperUsers can edit the TransLations? Everywhere where the dataprovider is set to enddate, the value of the calculation is returned, where in some cases, I need the actual value from the DB

  • When do these Stored Calculations get updated? Because the value of the Calculation is based on now() and now() changes from second to second. I do not expect the Calcualtion to be updated every millisecond, but what will trigger the update of the calculation?

Paul

6

pbakker:
When do these Stored Calculations get updated? Because the value of the Calculation is based on now() and now() changes from second to second. I do not expect the Calcualtion to be updated every millisecond, but what will trigger the update of the calculation?

Paul

It should update everytime the data is requested anyway I would change the calculation so that it recalcs only when a translation is added or modified. You have to check it very carefully.

7

Mmm, it’s different than what I would like, but it does work, so it seems.

FYI: My first Stored Calculation looks like this now:

if (enddate == null)
{
   return new Date(4000,1,1);
} 
else
{
   return enddate
}

This calcualtion is used in the relation.

Besides that I have another (unstored) calculation, to return the real enddate, that looks like:

if (enddate >= new Date(4000,1,1))
{
   return null;
} 
else
{
   return enddate;
}

This seems to work, but I do have to alter quite some generic codes that used the enddate to determine business logic. This logic was partially based on the enddate being null and that now has to become based on the enddate being null or >= new Date(4000,1,1).

How it works with updating the calcs, I still have to check.

Tnx ngervasi for the tips.

Paul