Multiple date relationships

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Multiple date relationships

Postby drookwood » Tue Jan 06, 2004 11:53 pm

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?
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Re: Multiple date relationships

Postby Riccardino » Wed Jan 07, 2004 1:02 pm

drookwood wrote: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
User avatar
Riccardino
 
Posts: 911
Joined: Thu Apr 24, 2003 11:42 am
Location: Ferrara, Italy

Re: Multiple date relationships

Postby Guest » Wed Jan 07, 2004 4:12 pm

Riccardino wrote:
drookwood wrote: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?
Guest
 

Re: Multiple date relationships

Postby Riccardino » Wed Jan 07, 2004 4:34 pm

Anonymous wrote:
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.
User avatar
Riccardino
 
Posts: 911
Joined: Thu Apr 24, 2003 11:42 am
Location: Ferrara, Italy

Re: Multiple date relationships

Postby drookwood » Wed Jan 07, 2004 7:09 pm

Riccardino wrote:
Anonymous wrote:
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!
drookwood
 
Posts: 290
Joined: Tue Nov 11, 2003 7:08 pm
Location: Somerset, UK

Postby IT2Be » Wed Jan 07, 2004 7:23 pm

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
Marcel J.G. Trapman (IT2BE)
SAN partner - Freelance Java and Servoy
Servoy Components - IT2BE Plug-ins and Beans for Servoy
ServoyForge - Open Source Components for Servoy
User avatar
IT2Be
Servoy Expert
 
Posts: 4766
Joined: Tue Oct 14, 2003 7:09 pm
Location: Germany

Re: Multiple date relationships

Postby Riccardino » Sat Jan 17, 2004 4:28 pm

Riccardino wrote: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
User avatar
Riccardino
 
Posts: 911
Joined: Thu Apr 24, 2003 11:42 am
Location: Ferrara, Italy

Postby maarten » Tue Jan 20, 2004 2:34 pm

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?
Maarten Berkenbosch
User avatar
maarten
 
Posts: 797
Joined: Wed Apr 23, 2003 10:52 pm
Location: Amersfoort, Netherlands


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 8 guests