Multiple date relationships

Classic Servoy
1

In Filemaker I use a Date plugin to automatically create a series of dates from a given start date to a given end date and return a list of dates in a text field separated by returns. For example a start date of 1/1/2004 (UK date formats) and end date of 5/1/2004 will return:

1/1/2004
2/1/2004
3/1/2004
4/1/2004
5/1/2004

I can then use a relationship from another file which will match a single date or even another list of dates produced in the same way against any of the dates contained in the above calculation (i.e. if any date matches any date the relationship has a match) - very useful in booking systems etc.

I cannot work out how to do the same thing in Servoy - is it possible? I have tried playing around with text fields and manually entered lists of dates but cannot get a relationship to achieve a match unless the entire contents of the field match. Do I need to use an array - if so, how?

2

drookwood:
In Filemaker I use a Date plugin to automatically create a series of dates from a given start date to a given end date and return a list of dates in a text field separated by returns. For example a start date of 1/1/2004 (UK date formats) and end date of 5/1/2004 will return:

1/1/2004
2/1/2004
3/1/2004
4/1/2004
5/1/2004

I can then use a relationship from another file which will match a single date or even another list of dates produced in the same way against any of the dates contained in the above calculation (i.e. if any date matches any date the relationship has a match) - very useful in booking systems etc.

I cannot work out how to do the same thing in Servoy - is it possible? I have tried playing around with text fields and manually entered lists of dates but cannot get a relationship to achieve a match unless the entire contents of the field match. Do I need to use an array - if so, how?

I had a similar need and I solved it using a relation and two globals.
On the left side I put the global StartDate with a “>” operator and I related with the date field I want to filter.
Then, I added another key using the other global EndDate, with operator “?” always related to the same date field.
The result is the same you have using Mikhail Eidoshin famous smartranges formula.

Hope it helps

3

Riccardino:

drookwood:
In Filemaker I use a Date plugin to automatically create a series of dates from a given start date to a given end date and return a list of dates in a text field separated by returns. For example a start date of 1/1/2004 (UK date formats) and end date of 5/1/2004 will return:

1/1/2004
2/1/2004
3/1/2004
4/1/2004
5/1/2004

I can then use a relationship from another file which will match a single date or even another list of dates produced in the same way against any of the dates contained in the above calculation (i.e. if any date matches any date the relationship has a match) - very useful in booking systems etc.

I cannot work out how to do the same thing in Servoy - is it possible? I have tried playing around with text fields and manually entered lists of dates but cannot get a relationship to achieve a match unless the entire contents of the field match. Do I need to use an array - if so, how?

I had a similar need and I solved it using a relation and two globals.
On the left side I put the global StartDate with a “>” operator and I related with the date field I want to filter.
Then, I added another key using the other global EndDate, with operator “?” always related to the same date field.
The result is the same you have using Mikhail Eidoshin famous smartranges formula.

Hope it helps

Sorry Riccardino but I don’t quite follow this - can you email me a simple servoy example?

4

Anonymous:
Sorry Riccardino but I don’t quite follow this - can you email me a simple servoy example?

At the moment, I can’t. But I can send you a screenshot of the relation I’m talking about.
The two globals contains the start and the end date, the field on the right side contains the date of the record you want to filter.
If it was an invoice date, you could show in the portal only the invoices included between the start and end date.

5

Riccardino:

Anonymous:
Sorry Riccardino but I don’t quite follow this - can you email me a simple servoy example?

At the moment, I can’t. But I can send you a screenshot of the relation I’m talking about.
The two globals contains the start and the end date, the field on the right side contains the date of the record you want to filter.
If it was an invoice date, you could show in the portal only the invoices included between the start and end date.

Thanks for that - now I see it! This certainly deals with many situations - now to see if the same principle can work for a range of dates on both sides!

6

Don’t know if this will solve your problem but in the manuals you can find a way to find a date range. The format is:

controller.find();
datefield = “01-01-2003…03-01.2003”;
controller.search();

Please bare in mind that if you want to use variable dates that you need to format them like, in this case: utils.dateFormat(date,“dd-MM-yyyy”)

You can also do searches for the above 2 dates (not the range) by doing:

controller.find();
datefield2 = ‘01-01-2003’
controller.newRecord();
datefield = '03-01-2003;
controller.search();

Hope this helps…

Cheers

7

Riccardino:
I had a similar need and I solved it using a relation and two globals.
On the left side I put the global StartDate with a “>” operator and I related with the date field I want to filter.
Then, I added another key using the other global EndDate, with operator “?” always related to the same date field.
The result is the same you have using Mikhail Eidoshin famous smartranges formula.

The portal filtering doesn’t seem to work anymore after update to RC2. I had the same problems noticed by IT2BE with the file names, after the update. OSX.2.8

8

Hi Ricardo,

I just tested on tab and portal (W2000), but the operators < and > seem to work fine with dates.

note:
There was a bugfix on the operators.(they did exactly the opposite of what was expected). Maybe you have to flip them?