0

Creating serial numbers for group and sub groups

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

5 replies

null
    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    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?

    • Dorich
    • 4 yrs ago
    • Reported - view

    @ 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. 

    • Roger_McIlmoyle
    • 4 yrs ago
    • Reported - view

    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.

    • Dorich
    • 4 yrs ago
    • Reported - view

    @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.

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    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) }
    ---

Login to reply

Content aside

Categories

View all topics
  • 4 yrs agoLast active
  • 5Replies
  • 722Views