My application requires Project Numbers to be numbered using a specific format:
2-DigitYearOfProject-RunningNumber.
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!
My overall objective is to be able to have a project number that is a running number that resets at the beginning of the year.
And it's prefixed by the 2-digit year.
20-1234 is an example for a project in the year 2020.
21-1002 is an example for a project in the year 2021.
There will be multiple people entering projects into the system.
I can't use a field type of auto-incrementing because of the need to re-set at the beginning of the year.
Maybe a filter is the wrong way to go about making sure the form is looking at the correct NextProjectNumber record.
Maybe there's some basic functionality that I'm missing.
All I know is that Projects-->NextProjectNumber
I need to be linked/joined to the correct NextProjectNumber record based on the year of the project. ProjectDate is an entry field on my form.
I'm asking for guidance and suggestions. Maybe I'm doing it the wrong way for Knack.
“My problem is that I don't know how to filter my NextProjectNumber connection to the year of the Project.”
Can you be a little clearer.
The filter is going to limit the options you give the user. So I can’t easily see how that will help you.
What’s your overall objective? You could be missing something a lot simpler.
You want a record to be given a certain ID format.
The first two digits is the year.
The next four digits is the record number starting from 1000 resetting every year.
I can’t easily see how filter will help here.
Thank you for your response Ramsey.
That doesn't work for me, as I tried to explain, maybe not very well.
I can't use an incrementing field as my counter because I need to start over at 1000 when we hit a new year.
That's why I created the NextProjectNumber table, so that I can keep track of what the next available project number is.
My problem is that I don't know how to filter my NextProjectNumber connection to the year of the Project.
This is important towards the end of the year as one person may be entering a project for the year 2020 but a person on a different computer entering a project for year 2021. So person #1's project would be something like 20-5321 but person #2's project number would be 21-1001.
I can put a "hard-coded" filter on the form for NextProjectNumber, I can say "ProjectYear Is 2020" (Screenshot below)
But I need that filter to be based on the year of the Project which is a date field on the form (a field in the Projects) table.
I know how to extract the year from the date, all that good stuff.
I just can't figure out how to filter my Projects-->NextProjectNumber connection so that I can get the next available project number for the year that I'm working in.
I even have the incrementing of the fields in NextProjectNumber table working with From Rules/Record Rules.
I just can't figure how to filter/point-to the proper record in my NextProjectNumber table based on the Project Date that's entered on the form.
Thanks again for your response Ramsey.
Your fix is to make a new text formula field that concatenates the other fields such as “Two Digit Year”- {incrementalNumber+(startingNumber)}
You can check the docs for how to extract a two digit year with time formulas.
The incremental fields adds to what your starting number is, which is the biggest number you currently have, let’s say it’s 1000, so that will be the fixed number in the formula then incremental field number adds to that, and you should be good.
All the best,
Ramsey
Ramsey@garmar.co.uk