How can I prevent an add based on values in existing sibling records?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am a newbie in Quickbase, but have been programming for a long time.  For children records that are added/edited, I want to make sure that the date range the user enters, does not conflict with any of it's sibling records.  How do I do that?

Photo of Sue B. H.

Sue B. H.

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
You have to use script and the API using an algorithm like this: Let's say every child record has a [start date] and an [end date] and that the siblings of the current child record you are creating or editing have already be entered without any "overlaps". So if these siblings records were laid out in sorted order they would form a strictly increasing sequence:

S1 <E1 <S2 < E2 < ... < Sn  < En

Note that every [start date] (S1, S2, ...Sn) occurs in an odd position and every [end date] (E1, E2, ... En) occurs in an even position. Now let's say the record you are creating or editing has a [start date] of Snew and an [end date] of Enew. Now sort the combined list of [start date]s and [end date]s {S1, S2, ... Sn, Snew, E1, E2, ...En, Enew}

If you find Snew in an even position or Enew is an odd position you have an overlap and you can even isolate which of the sibling records is causing the overlap.

It is actually pretty simple to do if you think about it and are familiar with the image onload technique.
Photo of Sue B. H.

Sue B. H.

  • 0 Points
Thanks for responding to my question so quickly.  I am OK with the logic of the query, I just don't know WHERE to put any of this code.  In the form? Where?  I looked up  image onload technique, and found one question about it, but I really don't know what and where I insert any code.  I know this is super trivial, but can you direct me to some documentation that explains how to put business logic code in Quickbase?  Thanks again.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
>I just don't know WHERE to put any of this code

"There is a good chance that this code uses the image onlaod techique which you can read about here: "

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=q&qid=1
Photo of Sue B. H.

Sue B. H.

  • 0 Points
Thanks so much!  I will check it out.