Auto numbering based on specific conditions
Hey guys, I'm really new to not only Ninox, but database management in general. I'm trying to set up auto numbering based on a specific set of conditions. I have figured out how to create prefix based on specific condition for instance in this example when i select Group Logo in Product Table Šifra (or ID number) will be LOG-1001
if Grupa.Grupa = "Logo" then //Grupa.Grupa is Category for the Table Grupa
let prefix := "LOG";
let count := cnt(select Proizvod);
'Šifra' := prefix + "-" + format(count, "1000")
else ....
The problem is that when I create a new record and select different category for example 3D my ID is 3D-1002 (I presume this is because cnt is counting number of records in Table), but I need it to be 3D-1001, because this is the first record based on this condition. So in short i need this number to count records based on set conditions, not on table records. I don’t know if I'm explaining this correctly, but any help will be much appreciated. THANK YOU!
3 replies
-
Hi Marija -
One issue I see is that you are mixing text and numbers in your ID code so it will make things a bit harder to increase number by any amount without having to do more coding.
Here is one option.
1) create a new number field called numID.
2) Then for testing you can create a button, call it whatever you want, with the following code:
let xGrp := this;
let xMax := max((select Grupa where Grupa = xGrp.Grupa).numID);
if xMax = null then
numID:= 1001
else
numID := xMax + 1
endLine 1 creates a variable and puts the data of the record you are on in it.
Line 2 finds the largest number in numID of the records with whatever data is in the group field
Line 3 checks to see if the numID field is empty, if it is then it puts 1001 in numID. otherwise it takes the value in xMax and then adds 1 to it and puts it in the numID field.
Then you would create a formula field, where you would put:
group + "-" + numID
and now you have a field that has the format you want and you can use that.
Let us know how it goes.
-
Hey Fred, thank you it works like a charm, and thank you for explaining what formula does line by line :-). I just had to replace select Grupa, with select Proizvod (that is the name of the current table I’m on), and it works great.
-
Just thinking that you can put this in the Trigger after update for the Grupa field so whenever you select the Grupa it will create an ID for the record. I'm guessing you will only touch the field once so you won't go changing the ID of a record. If I'm wrong then that wouldn't be a good place for the trigger.
Content aside
- 3 yrs agoLast active
- 3Replies
- 437Views