My application requires Project Numbers to be numbered using a specific format:
Such as: 20-1001 20-1002 20-1003 etc
Thing is that the numbering starts over again when the year changes so I can't use an autoincrementing field:
21-1000 21-1001 21-1002 etc
I have a Projects Table with 2 fields: ProjectYear and ProjectNumber
Then a TextEquation field to concatenate them together for Display purposes
I have a second table named NextProjectNumber to track the next available project number by year. (See attachment).
This may be really simple (I'm new to Knack!) but I can't figure out how to filter my connection to NextProjectNumber table so that I can know what the next available project number is based on the Year that the project is being created.
I've added the NextProjectNumber connection field to my input form, it shows as a drop-down box.
But how do I filter it to the year of the Project (date entry field on the form)?
My logic all works correctly as long as I'm pointed/connected/filtered to the correct record in the NextProjectNumber table.
Seems like this should be easier than it is. Maybe I'm missing something obvious?
ANY input is welcome, any ideas, any other methods to accomplish what I'm trying to do.
Thanks in advance for any replies!