How to create a drop down list in a form based upon if a record has been created

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
I am trying to customized a form I have for catpuring forecast information by project where only the users can "add" a forecast only one time during the month.

Currently I have a form set up to only caputure forecasts where the month is equal to or greater than the current date field. There are many different projects that are listed in a drop down whereby a project manager can find their respective projects to select. As the project manager selects their project, the respecitive month fields will then allow them to add a forecast for every month that is equal to the current month through the end of the year.

However, this is not sufficient, because they could go into the "add" form today and enter in their forecast data and then go back in tomorrow and do it again. Quickbase then creates two forecast entries in the current month for one project forecast...only one forecast entry per project per month is allowed.

What I want to happen is for the user to only be able to "Add" a forecast using the form one time during the month and after that, that project will not be able to accept another forecast entry in that month. How it happens I really do not care so much, I am ok with a message, the project not showing up in the drop down, etc.

How can I go about doing this? I have tried doing it in the form, but I have been unsuccessful.

Any advice would be greatly appreciated.


Thanks

Doug
Photo of Doug

Doug

  • 0 Points

Posted 6 years ago

  • 0
  • 1
Photo of KenFirch

KenFirch

  • 10 Points
You could create a Formula Text field to create some sort of unique key based on the Project and whatever date field you are using as the Forecast Date. Perhaps something like:

var Number m = Month([Forecast Date]);
ToText([Related Project]) & ":" &
If($m < 10, "0" & ToText($m), ToText($m)) & "-" & ToText(Year([Forecast Date]))

Then set that formula field as unique. Maybe setup a rule so that Forecast Date becomes read only (for them) once added.
Photo of Doug

Doug

  • 0 Points
Thank you.  I will try this.  I did already set the Forecast Date to read only....so that part I know works.  Will try this angle and see what happens.