Can we limit the nmber of records in a table

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • In Progress
Photo of Kristy Williford

Kristy Williford

  • 564 Points 500 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of Charlie

Charlie

  • 542 Points 500 badge 2x thumb
If you don't delete records in a table / your RIDs are sequential you could set up a custom data rule (advanced table settings) on this table where record ID cannot be greater than X...

Othwerwise you need some way to know how many records are in your table at the time of saving a new record. That can easily be achieved by creating a variables table with a parent relationship to the table you are trying to limit.

1. Create new table "Variables"
2. Create a relationship to the table you want to limit (lets call it projects) with variables as the parent, i.e. "1 variable has many projects"
3. Create 1 record in your variables table
4. Import the value of 1 (record ID of the only record in your variables table) to the "Related Variable" field in every record in projects
*Also set the default value of related variable to 1 so this will be auto-populated for you moving forward
5. Create a summary "# of projects" from projects to variables
*since your 1 variable record is related to ALL projects, this will get you the total you need
6. Lookup "# of projects" from Variables back down to Projects
7. Create a formula field for "# of projects lookup +1"


Now on each new project record, you will know what the impact of adding a new project would be to the total and using custom data rules (as mentioned above) you can write a rule to say that your formula cannot exceed a certain number. You could do this with a form rule if all your data is added via a form..but custom data rules will apply in grid edit, imports, automation, etc..

I hope that was helpful!

Charlie