Welcome!

This community is for professionals and enthusiasts of the Genio platform.
Share your questions and challenges, and help your partners!

Hide Intro Register

Limiting new table record: How to make a write condition that checks the interval between 2 dates?

Formula

Joana Damas

on 11/21/19, 3:16 PM • 1,597 views

I have the tables PERSON (field: name) and LOCPERSON (fields: foreignkeyPERSON, local, begindate, enddate), which I want to use to know where the person was at any given date.

How do I make sure that a person is not at 2 locals at the same time? i.e. when I create a new record in LOCPERSON, how do I make sure I'm not inserting the same person at another local in the same time interval of an already existing record with that person?

Flag

Reply
Comment
Share

Tiago Xavier

On 11/21/19, 4:28 PM

Here is one example where I have one period with fields "DtStartA" and "DtEndA", and other period with fields "DtStartB" and "DtEndB".

Another field will be check formula with name "check" like you can see were:

With that example you can make a arithmetic formula like this:

iif(ComparaDatas(iif([DTEX->DTSTARTA] > [DTEX->DTSTARTB], [DTEX->DTSTARTA], [DTEX->DTSTARTB]), iif([DTEX->DTENDA] < [DTEX->DTENDB], [DTEX->DTENDA], [DTEX->DTENDB])) <= 0, 1, 0)

The return on this example will be logical and is true when you have conflit between two period of dates.

Comment
Flag

Ask a Question

Keep Informed

About the Community

This platform is for beginners and experts willing to share their Genio knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers. Read Guidelines

Question tools

14 follower(s)

Stats

Asked: 11/21/19, 3:16 PM
Seen: 1597 times
Last updated: 11/26/19, 10:09 AM