Creating serial numbers for group and sub groups

D

I’m attempting to use Ninox to create a filing system that is broken into the following sections

- Box

- Group

- Subject

 


A group is contained inside a box, and a subject is contained inside a group.

 


I want to allocate a “Serial Number” for each group and a “Serial Number  for each subject.

 


However, the only method I’ve seen for creating serial numbers uses the fact that a new record is created in a table and allocates the number on the creation of a new record. Using this approach it would appear that I would be forced to use a table for each group and a table for each subject, which seems unwieldly. I would prefer not to have a table for each group and a table for each subject.

 


Thus for a subject the logic to create a new serial number would appear to be something like:

If box 1 and group 2 and subject 3 then make the serial number the next one in the series for this subject 3.

 


Similarly for a group:

If box 10 and group 5 then make the serial number the next one in the series for this group

 


I could allocate the serial number manually but I wondered if there is a method to automate the process within Ninox.

 


I tried using If/Then/Else in the “On creation” field but as might be expected it appears that you can’t use fields from other tables in the logic.

 


Perhaps this is too ambitious for a naive user but I’d appreciate any suggestions or thoughts on solving my dilemma.

 


Thanks

A

You don't say much about the structure of your database. Is it:

- one table for boxes

- one table for groups

- one table for subjects

- with n:1 links from subjects to groups and from groups to boxes to represent the hierarchy?

D

@ Alain Fontaine

Good question and one I've struggled with.

Overall the "logical" structure would seem to be as follows:

One Table for Files

One Table for Boxes

    A Child Table for Groups

        A Child Table for Subject

 

In addition when a file is assigned a "Subject" it needs to have a sequence number so that it can be easily found in the subject group, since files in each group are filed by sequence number. I can't fathom any way to automatically generate this number and so I'm assuming it is just entered manually.

 

" with n:1 links from subjects to..." Yes that's the intent.

 

I'm not attached to this database structure, however, the "logical" structure of identifying the location of a file seems to be optimum.

 

Finally, thanks for responding. 

Ninox Profile

Assuming you don't want to re-use numbers, simply use number(Id) from each table. Given your layout about:

sequence := text(Boxes.Id) + "-" + text(Groups.Id) + "-" + text(Subject.Id)  would result in something along the lines of 10-10-10. The numbers are never re-used and are literally guaranteed to be static. You have to consider context. I would store the serial# in the Subjet table in which case it would be;

let xId := number(Id);
let xBoxId := number(Groups.Boxes.Id);
let xGroupId := number(Groups.Id);

let serial := text(xBoxId) + "-" + text(xGroupId) + "-" + xId

I would put this in the On Create trigger of the Subject table.

D

@Roger McIlmoyle

Thanks for your response.

Good suggestion. I'll give this some more thought. Originally I dismissed trying to use the Id because I wanted the sequencing of the group to restart its sequence in each box. However, I realize that I didn't explain that in my OP so my apologies.

 

A

Using number(Id) is indeed a good way to get unique serial numbers. If, in your business case, you need serial numbers that start at "1" for each group inside a box, and for each subject inside a group, that is possible too. Let's suppose that each level has a number field named "Serial", and that the subject level also has a text field named "Subject Name".

For the boxes, there is no parent. So we will need to put a formula in the "Trigger on create" of the "Boxes" table itself:

Serial := max((select Boxes).Serial) + 1

The groups have a parent, and in the "Groups" table, there is a "Boxes" field, so named by default, which is the "Reference from Groups to Boxes (N:1)". In the "Trigger after update" option of this field definition, put:

Serial := max(Boxes.Groups.Serial) + 1

Similarly, in the "Trigger after update" option of the "Groups" fields of the "Subject" table, put:

Serial := max(Groups.Subject.Serial) + 1;
'Subject Name' := ---
{ text(Groups.Boxes.Serial) }-{ text(Groups.Serial) }-{ text(Serial) }
---

Reply