Using Button and Script Block to Issue New ID Number

Hello everyone!

Hopefully I put this post in the right place, but let me know if I should move it to another area. I have been trying to use the community as well as Google to try and find a solution to my problem at work, but don’t know coding well enough (at all really) to put anything together that works. I had an idea that would work out well, but I don’t know if this idea is realistic for the use of a button and scripting. I haven’t been able to find anything similar to it yet. I apologize if I am not including all of the necessary information needed or if parts of this don’t make sense. I will clarify anything that needs to be addressed. If anyone is willing to help out, I would greatly appreciate it!!

We have a log that records the materials being used on each specific job. There are multiple material types that we log. Some of these materials we can purchase samples for and the others we need to create in-house samples. For our in-house system we currently are using another base, but I want to combine these 2 bases to reduce double entry of the same information. The largest problem I am having with combining them is that our in-house system has a specific numbering system that we don’t want to extend to all of the materials that are in the material log (our purchased items in particular). I was hoping that there was a way to use the button and have an associated script that would assign the specific record a “sample number”, that goes in sequence, but only for the records that we choose (use the button on). It would be nice if it could auto-fill into the sample column, but if the script block displays it, then we could transfer to the column. I am open to adding additional tables and/or columns to the base in order to make this a reality. See below picture of the setup on the sample log table. Right now we manually transfer in the sample number associated with the items that are made in house (from the in-house sample base) and they do not go in order on this log (grouped by job).

Below is the setup on our in-house sample table. The view I will create in the other base will closely resemble this with some alterations (due to the functionality of other tables in that base, I cannot keep it exact).

One more note, our number system resets each year (that’s how we can differentiate when the sample was created). So this year started with 20000, next year will be 21000 and so on. At the moment the in-house sample base has a table for each year which I could control the starting number with a formula column and an autonumber column. When it’s moved into the material table all of the years will have to be in the same tab and we’ll have to use additional views or filters to separate them. I don’t know if the fix to this issue would be to change the scripting at the beginning of each year to offset the difference in numbers or changing the formula from another column/table that the script works off of.

Thank you for taking the time to read this and any attempts to help!

1 Like