Restrict date update if 14 days after currently selected date

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
Hello,

We have a date field and would like to forbid users selecting new date that is after 14 days of the currently selected date.

For example: date is set to 01/01/2018, user shouldn't be allowed to change it to 01/15/2018. If user selects 01/14/2018 that should be allowed.

I've been playing with form rules but nothing I set there works. I keep saving any dates I please.

Here is the attached rule, can you please tell me where I'm wrong?


Thank you.

Photo of Aleksandar Salapura

Aleksandar Salapura

  • 482 Points 250 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
The issue is that you are trying to compare the value of a field with itself.  So it will always be equal to itself.

So you need to capture the value of the field in a separate field.

I have not tested this, but try a setting up a field called [Old Actual Est Date (concept)].

Have a form rule that says
When the record is saved, change the value of [Old Actual Est Date (concept)]. to the value in the field [Actual Est Date (concept)].  That way you will have the old value to compare to.

You should initialize the value  of [Old Actual Est Date (concept)] to the on that field to the current value on the field [Actual Est Date (concept)].  

You can either do that with copy and paste in grid edit if there are not too many records or else make that field a formula field equal to [Actual Est Date (concept)] and then change he field type back to a date field.  It will retain its calculated value.
Photo of Aleksandar Salapura

Aleksandar Salapura

  • 482 Points 250 badge 2x thumb
Hello,
Thank you for fast response. I honestly didn't get the second part of your idea. Tried this way using your logic to the point I understood, but still doesn't work. Could you please check it?

Thank you.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
The point is that the value in old is initially blank. You need to get all of 5he respective old field to match the current field.

To test is this is going to work, manually edit a record to get 5he old to match the current.

Then see if the form rule works in that one record.

Then post back here to continue.
Photo of Aleksandar Salapura

Aleksandar Salapura

  • 482 Points 250 badge 2x thumb
It works. Once I manually enter date in [Actual Exit Date (Ideation)] value is automatically copied to [Old Actual Exit Date (Ideation)]. So the field and dynamic form rule works for the first part.

Can you tell me the next step, please?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
OK, so say you have 1000 existing records. The form rule will work on a going forward basis for just 1 of those 1000 records because that is the only one has old value populated.

So, the question is how to populate old value in the other 999 records.

If you have less than say 500 records, then you could run a report and get those two columns side by side and Grid Edit and highlight the source cells and then right click to copy and then paste.

But if you have a lot of records, then another method is do this just once.

Change the field type of old value to be a formula numeric.

Make the formula just be

[Actual Est Date (concept)]

Then change the field type for old back to to be a numeric field type. Quick Base will drop the calculated value that was there by calculation into each row of data. So even if you had 100,000 Records, that would happen basically instantly.

Now your form rule will work on all your records and not just that one where you hand edited.
Photo of Aleksandar Salapura

Aleksandar Salapura

  • 482 Points 250 badge 2x thumb
I do appreciate your help very much, but I really have no clue what you're telling me to do. I didn't understand single sentence. I don't blame you, it's probably me, because I'm newbie in all of this.

I thought your explanation will be much easier to follow.

Now I don't know what I did nor what I should do.

Is there a way for you to tell me in more basic language. Like: 1. create this field with this type; 2. create this form rule etc.

I'm confused right now... 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
I feel you are very new to Quick Base you may want to make a copy of your app to first do this on a test copy of the app.

1. Edit the field properties of the. Field called [Old Actual Est Date (concept)] and change it to be type of formula numeric

2. Make the formula box entry be [Actual Est Date (concept)]

3. Edit the field properties of the field [Old Actual Est Date (concept)] to be of type numeric.

Now your existing form rule should work for all your records.